filtering and formatting

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

https://technet.microsoft.com/en-us/library/ee177028.aspx?f=255&MSPPError=-2147217396

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

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.

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.

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)

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

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?

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

Hey Daniel, You rock man. :slight_smile:

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.

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)

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.

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.

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.