filtering and formatting

This topic contains 9 replies, has 4 voices, and was last updated by Profile photo of amit aman amit aman 1 month, 3 weeks ago.

Viewing 10 posts - 1 through 10 (of 10 total)
  • 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

    • This reply was modified 1 month, 3 weeks ago by Profile photo of Daniel Krebs Daniel Krebs.
    • This reply was modified 1 month, 3 weeks ago by Profile photo of Daniel Krebs Daniel Krebs.
    #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.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic.