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.

Examples

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 

Get-CMInstalledSoftware

[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$False,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$SoftwareName = "%",
        [Parameter(Mandatory=$False)]
            [switch]$Count,
        [Parameter(Mandatory=$False)]
            [switch]$CSV,
        [Parameter(Mandatory=$False)]
            [string]$SQLServer = “mysqlserver\INST_SCCM”, # eg, <sqlserver>, or <sqlserver>\<instance>
        [Parameter(Mandatory=$False)]
            [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
$connection.Open()
 
# Set queries
if ($count)
    {
        $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
        "
    }
else
    {
        $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”
$table.Load($result)

# Output results
if ($CSV)
    {
        $Path = "$env:TEMP\SoftwareQuery-$(Get-date -format hh-mm).csv"
        $table | Export-Csv -Path $Path -Force -NoTypeInformation
        Invoke-Item $Path
    }
Else {$table}

# Close the connection
$connection.Close()