How to: Extract Status Message Information from ConfigMgr 2012 R2

During MMS 2014, Wally Mead and I presented a session about state and status messages. This post describes one of the scripts that I demonstrated, which uses PowerShell to display status messages in a human-readable-friendly manner. This is similar to using the ConfigMgr status message viewer or SSRS reports, in that it displays the full sentence and description of the status message information. The value that this script adds, is that you can generate the information outside of the ConfigMgr Console (and outside of the SSRS reports).

First, the code (viewed best with Chrome browser): download GetStatusMessages.ps1

#https://gregramsey.net/?p=882
$StatMsgs = @()
$cnstring='Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;' + `
 'Initial Catalog=CM_LAB;Data Source=mylab.lab.com;User ID=;Password='
Add-Type -Path `
 "D:\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\bin\srsresources.dll"

$cmdtext = @"
select top 1000 smsgs.RecordID,
CASE smsgs.Severity
WHEN -1073741824 THEN 'Error'
WHEN 1073741824 THEN 'Informational'
WHEN -2147483648 THEN 'Warning'
ELSE 'Unknown'
END As 'SeverityName',
smsgs.MessageID, smsgs.Severity, 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.MessageID = 10803 and smsgs.MachineName in
-- (select name from _res_coll_CEN00018)
--where smsgs.MachineName = 'mycomputername'
Order by smsgs.Time DESC
"@

$sql

$cn = New-Object System.Data.OleDb.OleDbConnection $cnstring
$cn.Open()

$cmd = New-Object System.Data.OleDb.OleDbCommand $cmdtext, $cn
$cmd.CommandTimeout = 0
$reader = $cmd.ExecuteReader()
while ($reader.read())
{
$FullMsgString = `
 [SrsResources.Localization]::GetStatusMessage([int]$reader["MessageID"],
 [int]$reader["Severity"], $reader["MsgDllName"].ToString(), "en-US",
 $reader["InsString1"].ToString(), $reader["InsString2"].ToString(),
 $reader["InsString3"].ToString(), $reader["InsString4"].ToString(),
 $reader["InsString5"].ToString(), $reader["InsString6"].ToString(),
 $reader["InsString7"].ToString(), $reader["InsString8"].ToString(),
 $reader["InsString9"].ToString(), $reader["InsString10"].ToString())
$FullMsgString = $FullMsgString -replace '([\.][\r])[\n]','.'
$FullMsgString = $FullMsgString -replace '([\.][\r])[\n]','.'
$obj = @{
SeverityName = $reader["SeverityName"].ToString()
MessageID = $reader["MessageID"].ToString()
Severity = $reader["Severity"].ToString()
MsgDLLName = $reader["MsgDLLName"].ToString()
Component = $reader["Component"].ToString()
MachineName = $reader["MachineName"].ToString()
Time = $reader["Time"].ToString()
SiteCode = $reader["SiteCode"].ToString()
InsString1 = $reader["InsString1"].ToString()
InsString2 = $reader["InsString2"].ToString()
InsString3 = $reader["InsString3"].ToString()
InsString4 = $reader["InsString4"].ToString()
InsString5 = $reader["InsString5"].ToString()
InsString6 = $reader["InsString6"].ToString()
InsString7 = $reader["InsString7"].ToString()
InsString8 = $reader["InsString8"].ToString()
InsString9 = $reader["InsString9"].ToString()
InsString10 = $reader["InsString10"].ToString()
FullMsgString = $FullMsgString
}

$statmsgs += new-object psobject -Property $obj
}
$cn.Close()

$wmidate = new-object -com Wbemscripting.swbemdatetime
$date = get-date -format g
$wmidate.SetVarDate($date,$true)
$csv = "C:\logs\Statmsgs_" + $wmidate.value.substring(0,12) + ".csv"

$statmsgs | select-object SeverityName, MessageID, Component,
 MachineName, Time, SiteCode, FullMsgString `
 | export-csv $csv -notypeinformation
$statmsgs | select-object SeverityName, MessageID, Component, MachineName,
 Time, SiteCode, FullMsgString `
 | out-gridview
$statmsgs | group-object component | sort count -descending | out-gridview

To run this code in your environment, update the following:

• Line 4 – modify CM_LAB and mylab.lab.com to your server database and database server name.
• Line 6 – modify path to your SSRS server dll.
• Create c:\logs directory, or modify line 78.

The easiest location to run this code is on an active ConfigMgr Reporting Point (otherwise, you may have to copy/register some .dlls from your reporting point).

Here’s a breakdown of some of the code, and some ideas on how to customize it for your environment.

• Lines 8-28 – this is the SQL code that’s run against your ConfigMgr database – you should be able to run it manually as well. Notice that as written, we are querying the last 1000 status messages. Modify the query to filter by machine name, systems in a collection, or specific status message IDs.
• After executing the SQL query, (lines 32-37), we iterate through each result, create a custom object, and add it to an array (line 72). By creating the custom object for this data, we can perform other cool actions (as described next).
• Lines 76-83 are used to easily create a .csv file (with a dynamic filename, based on the current date in wmi date-time format.
• Lines 84-86 are used to display the information into a gridview – I find this view very handy, in that I can quickly look at data, filter, sort, etc. without much effort.
• Line 87 also generates a gridview – but this time, I used it with the group-object cmdlet  to easily show how many times a specific message is received, grouped by component name.

Happy Scripting!

Greg

ramseyg@hotmail.com

This post first appeared on http://www.gregramsey.net