filtering and formatting

This topic contains 13 replies, has 6 voices, and was last updated by Profile photo of amit aman amit aman 1 month, 1 week ago.

  • Author
    Posts
  • #49690
    Profile photo of amit aman
    amit aman
    Participant

    Team, need help again..

    I am trying to run following script:

    Invoke-Sqlcmd -Query "SELECT [CERTIFIED & UNASSESSED SKILL] FROM master where [CERTIFIED & UNASSESSED SKILL] LIKE '%Citrix%'" -ServerInstance dc2012\sqlexpress -Database final

    Getting output something like:

    Citrix Admin(L3),Citrix Virtualization – Implementation and Maintenance(L1),Communication(L2),English Language(L1),ITIL Documentation(L3),Networking and Infrastructure(L2),Projec...

    Citrix Admin(L3),Communication(L2),English Language(L1),ITIL IMPLEMENTATION(L2),Six Sigma(L1),Windows Admin(L3)

    Windows Admin(L3),Windows and Citrix Implementation(L3)

    Blackberry(L2),Capability Maturity Model Integration – CMMI(L1),Citrix Admin(L3),Communication(L2),English Language(L1),ITIL IMPLEMENTATION(L2),Lean(L1),MS Exchange Admin(L1),MS ...

    Citrix Admin(L3),Communication(L2),Datacenter – Wintel Stack(L1),English Language(L1),ITIL IMPLEMENTATION(L2),Project Management(L1),Six Sigma(L1),Vmware Support(L2),Windows Admi...

    Citrix Admin(L3),Vmware Support(L2),Windows Admin(L3),Wintel-Virtualization and Publishing Infra Consulting(L3)

    Citrix Admin(L3),Communication(L2),Datacenter – Wintel Stack(L1),English Language(L1),ITIL IMPLEMENTATION(L2),Six Sigma(L1),Vmware Admin(L3),Windows Admin(L3),Windows and AD Admi...

    Citrix Admin(L2),Communication(L2),English Language(L1),Vmware Admin(L1),Vmware Admin(L2),Windows Admin(L1),Windows Admin(L2),Windows and AD Admin(L1),Windows platform & Virtuali...

    Citrix Admin(L3),Citrix Virtualization – Implementation and Maintenance(L3),Datacenter – Wintel Stack(L1),English Language(L1),Vmware Admin(L3),Vmware Support(L2),Windows Admin(L3)

    Citrix Admin(L3),Communication(L2),English Language(L1),ITIL Ops(L2),Windows Admin(L2),Windows Admin(L3)

    Now I have to print only those skills in the output which contains *citrix* instead of printing all the skills under [CERTIFIED & UNASSESSED SKILL]

    Kindly Help

  • #49697
    Profile photo of Dan Potter
    Dan Potter
    Participant
  • #49702
    Profile photo of Don Jones
    Don Jones
    Keymaster

    Can you not just modify your SQL query to filter for that?

  • #49761
    Profile photo of amit aman
    amit aman
    Participant

    Dan, I went through all possible WHERE clause, none of them worked for me. Every time I am getting the same output.

    Don, need more help on modifying SQL query. I tried few, but ended up in frustration.

  • #49789
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    My guess is that "master" is not a table but a view and the values in the column [CERTIFIED & UNASSESSED SKILL] are being generated by a function from data coming from another table in the same or another database.

    Amit, please seek help from whoever has provided you access to this databasee because how to correctly query this internal database is not a PowerShell question but something for a data analyst, database developer or database administrator. Without access to the database or at least its schema it is very difficult for us to help you anyway.

    • #49791
      Profile photo of amit aman
      amit aman
      Participant

      Thanks Daniel for your reply. But this database has been created by me only.
      I have cross verified it, under 'final' database, I have 2 tables, out of which master is one.
      and [CERTIFIED & UNASSESSED SKILL] is one of the column name.
      I have checked all the connections as well. My database is accessible from every other application. I am able to fetch data from powershell as well using this database.
      My concern is I have to print only those skills in the output which contains *citrix* instead of printing all the skills.

      e.g I want to see the output as follows;

      Citrix Admin(L3),Citrix Virtualization – Implementation and Maintenance(L1)

      Citrix Admin(L3)

      Windows and Citrix Implementation(L3)

      instead of

      Citrix Admin(L3),Citrix Virtualization – Implementation and Maintenance(L1),Communication(L2),English Language(L1),ITIL Documentation(L3),Networking and Infrastructure(L2),Projec...

      Citrix Admin(L3),Communication(L2),English Language(L1),ITIL IMPLEMENTATION(L2),Six Sigma(L1),Windows Admin(L3)

      Windows Admin(L3),Windows and Citrix Implementation(L3)

  • #49795
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    Thanks Amit. It looks like you have a data problem. Your data is not normalized and all those values "Citrix Admin(L3),Communication(L2),English Language(L1),ITIL IMPLEMENTATION(L2),Six Sigma(L1),Windows Admin(L3)" are in the same column.

    You can try below which I haven't tested because I don't have access to your database but you should rather invest the time to normalize your data properly. May be get it again from the source.

    PowerShell is not really the right tool to filter data out of a database. Database engines are designed to filter and they can do it in memory become you see the result which is much faster than pulling everything across the wire and massaging the result with PowerShell or another tool.

    I hope that helps.

    Best,
    Daniel

    • #49799
      Profile photo of amit aman
      amit aman
      Participant

      No Luck Daniel.
      I broke down the script and tested it;

      After ForEach-Object { $_ -split ',' }, I am getting the output as
      System.Data.DataRow
      System.Data.DataRow
      System.Data.DataRow
      System.Data.DataRow
      System.Data.DataRow
      System.Data.DataRow

      So that means no proper data is passing across 2nd pipeline for where clause.
      I tried using ToString() method as well, still no luck.

      Any suggestion on normalizing SQL query?

  • #49802
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    Hi Amit,

    I've replicated your setup as best as possible on my local machine (installed SQL Express, created database and table, inserted above data).

    Please check out the updated code below that works on my machine with the data you've provided.

    I think the rest to get the expected results out of your data you'll need to figure out on your own because we don't have the bandwidth or resources to write complete solutions for you. I hope you understand and come back here to ask questions.

    Cheers
    Daniel

    • #49808
      Profile photo of amit aman
      amit aman
      Participant

      Hey Daniel, You rock man. 🙂

      Thank you so much for this. It worked.

      Citrix Admin(L3)
      Citrix Virtualization – Implementation and Maintenance(L1)
      Citrix Admin(L3)
      Windows and Citrix Implementation(L3)
      Citrix Admin(L3)
      Citrix Admin(L3)
      Citrix Admin(L3)

      Now getting output like this.
      Thank you so much once again.

  • #55964
    Profile photo of amit aman
    amit aman
    Participant

    Really sorry Daniel for getting back on this.

    This time, I need to get multiple columns instead of single column.
    I am trying with:

    $Params = @{
    Query = "SELECT * FROM master where [CERTIFIED & UNASSESSED SKILL] LIKE '%Citrix%'"
    ServerInstance = 'dc2012\sqlexpress'
    Database = 'final'
    }
    Invoke-Sqlcmd @Params | ForEach-Object { $_['CERTIFIED & UNASSESSED SKILL'] -split ',' } | Where-Object { $_ -like '*Citrix*' }

    But this code is also giving me the same data as:

    Citrix Admin(L3)
    Citrix Virtualization – Implementation and Maintenance(L1)
    Citrix Admin(L3)
    Windows and Citrix Implementation(L3)
    Citrix Admin(L3)
    Citrix Admin(L3)
    Citrix Admin(L3)

    How to get multiple columns?

    Output I want is:

    Name Id CERTIFIED & UNASSESSED SKILL

    abc 123 Citrix Admin(L3)
    xyz 456 Citrix Virtualization – Implementation and Maintenance(L1)
    hkj 789 Citrix Admin(L3)

  • #55972
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    I concur with Daniel that you really need to consider fixing the database. If it's your database, you should really look at normalizing your database. You're writing complex code to get values when you could just correct the database and easily handle it in SQL.

    tblSkills

    ID      SkillName
    1       Citrix Admin(L3)
    2       Citrix Virtualization – Implementation and Maintenance(L1)
    3       Communication(L2)
    4       English Language(L1)
    

    tblSkillsToJob

    ID, SkillID, JobID
    1   2        2
    2   3        1
    3   3        2
    

    tblJobs

    ID  Job
    1   Infrastructure Engineer II
    2   HelpDesk Support I
    3   Network Analyst III
    

    In your current database, you are associating data to a comma-delimited column, which is duplicating data over and over for each association. Additionally, as you've found, the column has to be parsed to get what you need. Get your data to 3NF Normalization and you can do some simple JOINs and do your queries in SQL.

  • #56023
    Profile photo of amit aman
    amit aman
    Participant

    Thanks a ton Rob for the suggestion. I'll try to fix it asap.

    Another thing is I have the data in excel sheet also, I tried to get the output using importexcel module, using following code:

    $a = Import-Excel 'C:\Master.xlsx' | sort 'Emp Code' -Unique
    $a | where {$_.'PROJECT_ACQUIRED_SKILL' -like ('*LOGICAL DOMAINS – LDOM*','*SOLARIS CONTAINERS*')} | select 'emp code','emp name', @{l="Project Acquired Skill";e={ForEach-Object {$_.'PROJECT_ACQUIRED_SKILL' -split ','} | where {$_ -like ('*LOGICAL DOMAINS – LDOM*','*SOLARIS CONTAINERS*')}}}

    I am not getting anything in Project Acquired Skill columns.

    Emp Code Emp Name Project Acquired Skill
    ——– ——– ———————-
    275 abc
    278 bcd
    319 def

    Although, It is working fine for one skill:

    $a = Import-Excel 'C:\Master.xlsx' | sort 'Emp Code' -Unique
    $a | where {$_.'PROJECT_ACQUIRED_SKILL' -like ('*LOGICAL DOMAINS – LDOM*')} | select 'emp code','emp name', @{l="Project Acquired Skill";e={ForEach-Object {$_.'PROJECT_ACQUIRED_SKILL' -split ','} | where {$_ -like ('*LOGICAL DOMAINS – LDOM*')}}}

    This code is giving me the desired output as :

    Emp Code Emp Name Project Acquired Skill
    ——– ——– ———————-
    275 abc LOGICAL DOMAINS – LDOM (85)
    278 bcd LOGICAL DOMAINS – LDOM (21)
    319 def LOGICAL DOMAINS – LDOM (10)

    Kindly help.

  • #56080
    Profile photo of amit aman
    amit aman
    Participant

    I have got a workaround for this. But it seems to be a very long code with lots of hard coding. Following is the code I am using:

    $a = Import-Excel 'C:\Users\am288711\Desktop\Master Oct 14 2016.xlsx' | sort 'Emp Code' -Unique
    $a | where {($_.'PROJECT_ACQUIRED_SKILL' -like '*LOGICAL DOMAINS – LDOM*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*SOLARIS CONTAINERS*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*SUN CLUSTER*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*ZFS*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*HP-UX VIRTUAL PARTITIONS – VPARS*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*HP NPARTITIONS – NPARS*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*IBM POWER VM, IBM VIO*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*IBM POWER HA*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*LINUX KVM*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*LINUX CLUSTERING*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*VERITAS CLUSTER SERVER*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*VERITAS VOLUME MANAGER – VXVM*') -xor ($_.'PROJECT_ACQUIRED_SKILL' -like '*HP INTEGRITY SERVERS*')} | select 'emp code','emp name', @{l="Project Acquired Skill";e={ForEach-Object {$_.'PROJECT_ACQUIRED_SKILL' -split ','} | where {($_ -like '*LOGICAL DOMAINS – LDOM*') -xor ($_ -like '*SOLARIS CONTAINERS*') -xor ($_ -like '*SUN CLUSTER*') -xor ($_ -like '*ZFS*') -xor ($_ -like '*HP-UX VIRTUAL PARTITIONS – VPARS*') -xor ($_ -like '*HP NPARTITIONS – NPARS*') -xor ($_ -like '*IBM POWER VM, IBM VIO*') -xor ($_ -like '*IBM POWER HA*') -xor ($_ -like '*LINUX KVM*') -xor ($_ -like '*LINUX CLUSTERING*') -xor ($_ -like '*VERITAS CLUSTER SERVER*') -xor ($_ -like '*VERITAS VOLUME MANAGER – VXVM*') -xor ($_ -like '*HP INTEGRITY SERVERS*')}}}

    Can we make this ugly looking code bit beautiful? I have all the skills in an excel sheet.

You must be logged in to reply to this topic.