Instant Client Software Inventory with ConfigMgr and PowerShell

Here’s a simple but handy PowerShell script I wrote that uses the ConfigMgr database to retrieve software inventory information for any client.  You can return the entire inventory for the client, or search for specific software.  You can also pass the computer name and/or software name along the pipeline to the script, so you can search multiple computers or multiple software titles.

You need ‘db_datareader’ access to your ConfigMgr database with your logged-on account, and you also need to add the ‘Installed Software‘ class to your hardware inventory classes, in your ConfigMgr Client Settings.

Examples

Search for software with “Apple” in the title for a specific client:


Get-CMClientInstalledSoftware -ComputerName mypc-tj8 -SoftwareName %Apple%

capture3

Retrieve the entire software inventory for a client, output to GridView


Get-CMClientInstalledSoftware -ComputerName mypc-tj8 | Out-GridView

capture2Search for “Cisco Webex Meeting Center for FireFox” on an array of clients

Uses PowerShell 4.0’s ForEach method


($computers = @("mypc-tj","mypc-tj8").ForEach({Get-CMClientInstalledSoftware -ComputerName $psitem -SoftwareName "%Cisco%FireFox%"}))

captureGet-CMClientInstalledSoftware

Update the $SQLServer and $Database parameters with your ConfigMgr SQL server (and instance if applicable) and database name.


[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$True,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$ComputerName,
        [Parameter(Mandatory=$False,ValueFromPipeline=$true,ValueFromPipelineByPropertyName=$true)]
            [string]$SoftwareName = "%",
        [Parameter(Mandatory=$False)]
            [string]$SQLServer = “mysqlserver\INST_SCCM”, # eg <mysqlserver>, or <mysqlserver\instance>
        [Parameter(Mandatory=$False)]
            [string]$Database = “CM_ABC”
        )

# Open a SQL connection
$connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# Set query
$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 sys.Name0 = '$ComputerName'
and sof.NormalizedName like '$SoftwareName'
order by 'Software Name'"

# Execute query
$command = $connection.CreateCommand()
$command.CommandText = $query
$result = $command.ExecuteReader()

# Load results
$table = new-object “System.Data.DataTable”
$table.Load($result)

# Output results
$table

# Close the connection
$connection.Close()

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s