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.
![SQL Server Query to Create a PowerShell Script - Enhansoft Packages SQL Server Query to Create a PowerShell Script - Enhansoft Packages](https://b3034685.smushcdn.com/3034685/wp-content/uploads/2019/06/SQL-Server-Query-to-Create-a-PowerShell-Script-Enhansoft-Packages_thumb.jpg?lossy=2&strip=1&webp=1)
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’)
![SQL Server Query to Create a PowerShell Script - Query SQL Server Query to Create a PowerShell Script - Query](https://b3034685.smushcdn.com/3034685/wp-content/uploads/2019/06/SQL-Server-Query-to-Create-a-PowerShell-Script-Query_thumb.jpg?lossy=2&strip=1&webp=1)
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.
![SQL Server Query to Create a PowerShell Script - Copy Results SQL Server Query to Create a PowerShell Script - Copy Results](https://b3034685.smushcdn.com/3034685/wp-content/uploads/2019/06/SQL-Server-Query-to-Create-a-PowerShell-Script-Copy-Results_thumb.jpg?lossy=2&strip=1&webp=1)
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.
![SQL Server Query to Create a PowerShell Script - Clean Console SQL Server Query to Create a PowerShell Script - Clean Console](https://b3034685.smushcdn.com/3034685/wp-content/uploads/2019/06/SQL-Server-Query-to-Create-a-PowerShell-Script-Clean-Console_thumb.jpg?lossy=2&strip=1&webp=1)
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.