Retrieving ConfigMgr Status Messages with PowerShell

Did you know you can retrieve status messages from ConfigMgr, including the message descriptions, using PowerShell? I pieced together some code I found on the internet with some of my own code, and came up with the following script.  Essentially, it’s the equivalent of running the “All Status Messages from a Specific System” from the status message queries in ConfigMgr.  You can return results either into the console, CSV file, or to the PowerShell GridView for a more authentic “Status Message Viewer” feel.

The script works by running a SQL query to the ConfigMgr database to retrieve the status messages (you need “db_datareader” access to the database with your logged-on account). But the message descriptions themselves are not stored in the database, so to retrieve them we will talk to the ConfigMgr message DLLs.  These DLLs can be found on your site server, and contain the message strings to go with the message IDs.

Configure the Script

First, you will need to define the defaults for a couple of parameters in the script:

$SQLServer – the name of the SQL server hosting the ConfigMgr database.  Include the instance name if applicable, eg MYSQLBOX-01, or MYSQLBOX-01\INST_SCCM etc

$Database – the name of the ConfigMgr database, eg CM_ABC

$SMSMSGSLocation – the location of the “smsmsgs” directory containing the “srvmsgs.dll”,”provmsgs.dll” and “climsgs.dll”.  The default location will be “C:\Program Files\Microsoft Configuration Manager\bin\X64\system32\smsmsgs”

Running the Script

To retrieve all status messages from computer “PC001” in the last 7 days:

Get-CMStatusMessages -ComputerName PC001

To retrieve all status messages from server “SCCMSRV-01” in the last 24 hours and output to CSV:

Get-CMStatusMessages -ComputerName SCCMSRV-01 -TimeInHours 24 -CSV

To retrieve all status messages from computer “PC001” in the last 7 days and output to GridView:

Get-CMStatusMessages -ComputerName PC001 -GridView

Output

Default output is to console:

CSV output:

Output to GridView for “Status Message Viewer” like experience, where you can also filter for specific components or MessageIDs etc:

Taking it Further

You could add additional SQL queries in the script to return the kind of results you want to see, and call them using a parameter, to extend the capability of the script.

The Script

Download here: Get-CMStatusMessages

<#

.SYNOPSIS
    Returns the Status Messages from a specified computer in a specified time period

.DESCRIPTION
    Queries the ConfigMgr database to find a list of status messages for a client or server.  The status message descriptions are then retrieved from the ConfigMgr message DLLs.  Equivalent to
    the "All Status Messages from a Specific System" Status Message query. The results will be returned to the console by default, unless the CSV or GridView switches are used.

.PARAMETER ComputerName
    The name of the computer to retrieve status messages for

.PARAMETER TimeInHours
    The number of hours past for which to retrieve status messages. Default is 7 days.

.PARAMETER CSV
    Use this switch to return results into a CSV file

.PARAMETER GridView
    Use this switch to return results into PowerShell's Out-GridView

.PARAMETER SQLServer
    The name of your SQL server, including the instance where relevant, eg SQLSRV-01, or SQLSRV-01\INST_SCCM etc

.PARAMETER Database
    The name of your ConfigMgr database

.PARAMETER SMSMSGSLocation
    The location of the "smsmsgs" directory containing the "srvmsgs.dll","provmsgs.dll" and "climsgs.dll".  The standard location is used as the default.

.EXAMPLE
    Get-CMStatusMessages -ComputerName PC001
    Returns to the console all status messages for the system "PC001" in the last 7 days.

.EXAMPLE
    Get-CMStatusMessages -ComputerName PC001 -CSV
    Returns to a CSV file all status messages for the system "PC001" in the last 7 days.

.EXAMPLE
    Get-CMStatusMessages -ComputerName PC001 -GridView -TimeInHours 24
    Returns to PowerShell's GridView all status messages for the system "PC001" in the last 24 hours.

.NOTES
    Cmdlet name:      Get-CMStatusMessages
    Author:           Trevor Jones
    Acknowledgments:  Some of this code is adapted from code available on the internet
    Contact:          @trevor_smsagent
    DateCreated:      2015-07-22
    Link:             https://smsagent.wordpress.com

#>

[CmdletBinding(SupportsShouldProcess=$True)]
    param
        (
        [Parameter(Mandatory=$True, HelpMessage="The name of the computer to retrieve status message for")]
            [string]$ComputerName,
        [Parameter(Mandatory=$False, HelpMessage="The number of hours past in which to retrieve status messages")]
            [int]$TimeInHours = "168",
        [Parameter(Mandatory=$False)]
            [switch]$CSV,
        [Parameter(Mandatory=$False)]
            [switch]$GridView,
        [Parameter(Mandatory=$False, HelpMessage="The SQL server name (and instance name where appropriate)")]
            [string]$SQLServer = “SQLSRV-01\INST_SCCM”,
        [Parameter(Mandatory=$False, HelpMessage="The name of the ConfigMgr database")]
            [string]$Database = “CM_ABC”,
        [Parameter(Mandatory=$False, HelpMessage="The location of the smsmsgs directory containing the message DLLs")]
            [string]$SMSMSGSLocation = "C:\Program Files\Microsoft Configuration Manager\bin\X64\system32\smsmsgs"
        )

# Function to get the status message description
function Get-StatusMessage {
param (
    $iMessageID,
    [ValidateSet("srvmsgs.dll","provmsgs.dll","climsgs.dll")]$DLL,
    [ValidateSet("Informational","Warning","Error")]$Severity,
    $InsString1,
    $InsString2,
    $InsString3,
    $InsString4,
    $InsString5,
    $InsString6,
    $InsString7,
    $InsString8,
    $InsString9,
    $InsString10
      )

if ($DLL -eq "srvmsgs.dll")
    {$stringPathToDLL = "$SMSMSGSLocation\srvmsgs.dll"}
if ($DLL -eq "provmsgs.dll")
    {$stringPathToDLL = "$SMSMSGSLocation\provmsgs.dll"}
if ($DLL -eq "climsgs.dll")
    {$stringPathToDLL = "$SMSMSGSLocation\climsgs.dll"}

#Load Status Message Lookup DLL into memory and get pointer to memory
$ptrFoo = $Win32LoadLibrary::LoadLibrary($stringPathToDLL.ToString())
$ptrModule = $Win32GetModuleHandle::GetModuleHandle($stringPathToDLL.ToString()) 

if ($Severity -eq "Informational")
    {$code = 1073741824}
if ($Severity -eq "Warning")
    {$code = 2147483648}
if ($Severity -eq "Error")
    {$code = 3221225472}

$result = $Win32FormatMessage::FormatMessage($flags, $ptrModule, $Code -bor $iMessageID, 0, $stringOutput, $sizeOfBuffer, $stringArrayInput)
if ($result -gt 0)
    {
        # Add insert strings to message
        $objMessage = New-Object System.Object
        $objMessage | Add-Member -type NoteProperty -name MessageString -value $stringOutput.ToString().Replace("%11","").Replace("%12","").Replace("%3%4%5%6%7%8%9%10","").Replace("%1",$InsString1).Replace("%2",$InsString2).Replace("%3",$InsString3).Replace("%4",$InsString4).Replace("%5",$InsString5).Replace("%6",$InsString6).Replace("%7",$InsString7).Replace("%8",$InsString8).Replace("%9",$InsString9).Replace("%10",$InsString10)
    }
$objMessage
}

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

# Define the SQl query
$Query = "
select smsgs.RecordID,
CASE smsgs.Severity
WHEN -1073741824 THEN 'Error'
WHEN 1073741824 THEN 'Informational'
WHEN -2147483648 THEN 'Warning'
ELSE 'Unknown'
END As 'SeverityName',
case smsgs.MessageType
WHEN 256 THEN 'Milestone'
WHEN 512 THEN 'Detail'
WHEN 768 THEN 'Audit'
WHEN 1024 THEN 'NT Event'
ELSE 'Unknown'
END AS 'Type',
smsgs.MessageID, smsgs.Severity, smsgs.MessageType, smsgs.ModuleName,modNames.MsgDLLName, smsgs.Component,
smsgs.MachineName, smsgs.Time, smsgs.SiteCode, smwis.InsString1,
smwis.InsString2, smwis.InsString3, smwis.InsString4, smwis.InsString5,
smwis.InsString6, smwis.InsString7, smwis.InsString8, smwis.InsString9,
smwis.InsString10
from v_StatusMessage smsgs
join v_StatMsgWithInsStrings smwis on smsgs.RecordID = smwis.RecordID
join v_StatMsgModuleNames modNames on smsgs.ModuleName = modNames.ModuleName
where smsgs.MachineName = '$ComputerName'
and DATEDIFF(hour,smsgs.Time,GETDATE()) < '$TimeInHours'
Order by smsgs.Time DESC
"

# Run the query
$command = $connection.CreateCommand()
$command.CommandText = $query
$reader = $command.ExecuteReader()
$table = new-object “System.Data.DataTable”
$table.Load($reader)

# Close the connection
$connection.Close()

#Start PInvoke Code
$sigFormatMessage = @'
[DllImport("kernel32.dll")]
public static extern uint FormatMessage(uint flags, IntPtr source, uint messageId, uint langId, StringBuilder buffer, uint size, string[] arguments);
'@ 

$sigGetModuleHandle = @'
[DllImport("kernel32.dll")]
public static extern IntPtr GetModuleHandle(string lpModuleName);
'@ 

$sigLoadLibrary = @'
[DllImport("kernel32.dll")]
public static extern IntPtr LoadLibrary(string lpFileName);
'@ 

$Win32FormatMessage = Add-Type -MemberDefinition $sigFormatMessage -name "Win32FormatMessage" -namespace Win32Functions -PassThru -Using System.Text
$Win32GetModuleHandle = Add-Type -MemberDefinition $sigGetModuleHandle -name "Win32GetModuleHandle" -namespace Win32Functions -PassThru -Using System.Text
$Win32LoadLibrary = Add-Type -MemberDefinition $sigLoadLibrary -name "Win32LoadLibrary" -namespace Win32Functions -PassThru -Using System.Text
#End PInvoke Code 

$sizeOfBuffer = [int]16384
$stringArrayInput = {"%1","%2","%3","%4","%5", "%6", "%7", "%8", "%9"}
$flags = 0x00000800 -bor 0x00000200
$stringOutput = New-Object System.Text.StringBuilder $sizeOfBuffer 

# Put desired fields into an object for each result
$StatusMessages = @()
foreach ($Row in $Table.Rows)
    {
        $Params = @{
            iMessageID = $Row.MessageID
            DLL = $Row.MsgDLLName
            Severity = $Row.SeverityName
            InsString1 = $Row.InsString1
            InsString2 = $Row.InsString2
            InsString3 = $Row.InsString3
            InsString4 = $Row.InsString4
            InsString5 = $Row.InsString5
            InsString6 = $Row.InsString6
            InsString7 = $Row.InsString7
            InsString8 = $Row.InsString8
            InsString9 = $Row.InsString9
            InsString10 = $Row.InsString10
            }
        $Message = Get-StatusMessage @params

        $StatusMessage = New-Object psobject
        Add-Member -InputObject $StatusMessage -Name Severity -MemberType NoteProperty -Value $Row.SeverityName
        Add-Member -InputObject $StatusMessage -Name Type -MemberType NoteProperty -Value $Row.Type
        Add-Member -InputObject $StatusMessage -Name SiteCode -MemberType NoteProperty -Value $Row.SiteCode
        Add-Member -InputObject $StatusMessage -Name "Date / Time" -MemberType NoteProperty -Value $Row.Time
        Add-Member -InputObject $StatusMessage -Name System -MemberType NoteProperty -Value $Row.MachineName
        Add-Member -InputObject $StatusMessage -Name Component -MemberType NoteProperty -Value $Row.Component
        Add-Member -InputObject $StatusMessage -Name Module -MemberType NoteProperty -Value $Row.ModuleName
        Add-Member -InputObject $StatusMessage -Name MessageID -MemberType NoteProperty -Value $Row.MessageID
        Add-Member -InputObject $StatusMessage -Name Description -MemberType NoteProperty -Value $Message.MessageString
        $StatusMessages += $StatusMessage
    }

if ($StatusMessages -ne $null)
    {
        if ($CSV)
            {
                $Date = Get-Date -Format HH-mm--dd-MMM-yy
                $Path = "$env:USERPROFILE\Documents\StatusMessages-$ComputerName-$Date.csv"
                $StatusMessages | Export-Csv -Path $Path -NoTypeInformation
                Invoke-Item -Path $Path
            }
        if ($GridView)
            {
                $StatusMessages | Out-GridView -Title "Status Messages for ""$ComputerName"" in the last $TimeInHours hours"
            }
        else {$StatusMessages}
    }
else { write-host "No status messages found" }