Using a SQL Server Query to Create a PowerShell Script

by | Jun 5, 2019 | How-To

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

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

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

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

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.