Free ConfigMgr Client Health Report

Today I am pleased to make available the first of my free reports for System Center Configuration Manager 2012 and onward – a client health report.  These reports have been created in Microsoft Excel and use data connections to the ConfigMgr database, which allows us to pull a large amount of data into a single report, and display it both summarily, graphically and in data tables, without the need to drill down into further reports or navigate to different locations in the ConfigMgr console to find the data you need.

The client health report focuses on key health data for your ConfigMgr clients, including:

  • Inactive / active clients
  • Clients that have passed or failed the client evaluation check, including any error details
  • Clients that are active or inactive for DDR (heartbeat discovery), hardware inventory, software inventory, policy requests and status messages
  • ConfigMgr client versions in your environment
  • Last reboot times
  • Discovered systems with no ConfigMgr client installed
  • Systems that failed to install the ConfigMgr client, including error details

Download and read the full post for the client health report here.

Screenshots

ch1

 

ch11

ch14

ch16

ch18

Retrieving Software Inventory for a ConfigMgr Site with PowerShell

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

capture1

Get the list of machines that have “Microsoft Silverlight 5” installed and output to Gridview


Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5%" | Out-GridView

Capture2

Get the list of machines that have “Microsoft Silverlight 5” installed and output to CSV

 Get-CMInstalledSoftware -SoftwareName "Microsoft Silverlight 5" -CSV 

Capture3

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()

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()