Last updated on September 28th, 2022 at 09:35 am
This blog post is really meant for those who are better at writing SQL Server queries than at writing PowerShell scripts. I am one of those people, so recently I asked myself, “Why not use a SQL Server query to create a PowerShell script?” I did exactly that and this blog post will show you how I did it.
Background
I was testing our latest Enhansoft Reporting SCCM reports within my lab. When I was done, I couldn’t believe that I installed Enhansoft Reporting at least 20 times, if not more! The installation process creates two packages; one is called Enhansoft Reporting-MIR and the other one is called Enhansoft Reporting-EWR. By the way, each package has its own unique PackageID.
You can see what I was dealing with in the above screenshot. There are a number of duplicate packages, but I can’t delete all of them because a few are deployed within my test lab. I started cleaning-up the packages manually by deleting them one at a time. Then it hit me. I should use PowerShell!
I loaded PowerShell ISE and tracked down the command to delete the packages: Remove-CMPackage. After running this command a few times, I thought, “There must be a better way.” PowerShell made the most sense, but it was going to take me a while to write the script. I estimated that it would take me longer to write a PowerShell script than to manually delete the packages one by one. I am, however, good at writing SQL Server queries and could probably whip one up in a few seconds. Why not use SQL Server to create the PowerShell script? That’s exactly what I did!
Remove-CMPackage
The Remove-CMPackage PowerShell command takes a number of parameters, I quickly learned. Not only do you need the PackageID, but you should use the force command because you don’t want to be prompted each time you delete a package to say, “Yes,” delete this package.
This is what the command looks like:
Remove-CMPackage -Id ‘cb100098’ –Force
SQL Server Query to Create a PowerShell Script
Writing the query to find all of the packages and PackageIDs that I needed to remove was fairly easy.
Here it is:
select p.PackageID
FROM dbo.v_Package p
Where p.Manufacturer = ‘Enhansoft’
and p.PackageID not in (‘CB100015′,’CB100084’,
‘CB10008F’,’CB100090′,’CB100099′,’CB10009A’,
‘CB10009B’,’CB10009C’)
How did I turn this SQL Server query into a PowerShell script? It turned out not to be that difficult. I simply prepended and appended the appropriate text to the selected part of the query.
select ‘Remove-CMPackage -Id ‘+p.PackageID+’ -Force’
FROM dbo.v_Package p
Where p.Manufacturer = ‘Enhansoft’
and p.PackageID not in (‘CB100015′,’CB100084’,
‘CB10008F’,’CB100090′,’CB100099′,’CB10009A’,
‘CB10009B’,’CB10009C’)
All I had to do now was run the query and the results would be my PowerShell script! I cut and pasted the results into PowerShell then ran it. If I was correct, everything would be cleaned up.
By the way, SQL Server Management Studio (SSMS) made it easy for me to copy the results. After highlighting what I needed, all I did was right-click and select, “Copy.” I ran the command in PowerShell and my console was clean again.
Any bets on whether or not my PowerShell friends Kaido and Ryan will show me how easy this is to do in PowerShell? At least it will be a good blog post topic for them!
If you have any questions about how to use a SQL Server query to create a PowerShell script, please contact me @GarthMJ.