In my last post, I demonstrated how we can retrieve software inventory information for a single ConfigMgr client or an array of clients, using PowerShell. In this post, we will change the scope from the client to the entire site. Using this script, you can query for all installations of a specific software/s in your ConfigMgr site, returning either the count, or the full list of machines with the software installed.
When I say “software inventory”, I’m actually referring to the “hardware inventory” process (strange but true) in Configuration Manager that collects data from WMI classes, including the installed software, and not to be confused with the “software inventory” process in ConfigMgr which is used to inventory file types.
As previously, you need ‘db_datareader’ permission to your ConfigMgr database, with your logged on account, and you need to add the sql server and database name in the script.
Find a count of machines that have “Microsoft Silverlight” installed
Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight%" -Count
Get the list of machines that have “Microsoft Silverlight 5” installed and output to Gridview
Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5%" | Out-GridView
Get the list of machines that have “Microsoft Silverlight 5” installed and output to CSV
Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5" -CSV
[string]$SoftwareName = "%",
[string]$SQLServer = “mysqlserver\INST_SCCM”, # eg, <sqlserver>, or <sqlserver>\<instance>
[string]$Database = “CM_ABC”
# Open a connection
$connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
# Set queries
$query = "
SELECT Count(sof.NormalizedName) AS 'Count',
sof.NormalizedName, sof.NormalizedVersion, sof.NormalizedPublisher, sof.FamilyName, sof.CategoryName
FROM v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
where sof.NormalizedName like '$SoftwareName'
GROUP BY sof.NormalizedName, sof.NormalizedVersion, sof.NormalizedPublisher, sof.FamilyName, sof.CategoryName
ORDER BY 'Count' DESC, sof.NormalizedName, sof.NormalizedVersion
$query = "select Name0 as 'Computer Name',
User_Name0 as 'Last Logged-On User',
NormalizedName as 'Software Name',
NormalizedPublisher as Publisher,
NormalizedVersion as Version,
FamilyName as 'Software Family',
CategoryName as 'Software Category',
InstallDate0 as 'Install Date',
RegisteredUser0 as 'Registered User',
InstalledLocation0 as 'Install Location',
InstallSource0 as 'Source Location',
UninstallString0 as 'Uninstall String',
TimeStamp as 'Inventory Time'
from v_GS_INSTALLED_SOFTWARE_CATEGORIZED sof
inner join v_R_System sys on sof.ResourceID = sys.ResourceID
where sof.NormalizedName like '$SoftwareName' order by Name0
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
# Output results
$Path = "$env:TEMP\SoftwareQuery-$(Get-date -format hh-mm).csv"
$table | Export-Csv -Path $Path -Force -NoTypeInformation
# Close the connection