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

Additional Dell Right-Click Tools

 

Previously, I posted how to Integrate Dell iDRAC into Configmgr 2012. Here are additional Dell right-click tools that can be leveraged with ConfigMgr 2012:

  • Show System and Warranty Information – Launches a Dell Web page based on the computer service tag and displays system details as well as factory warranty information.
  • Dell and System Center – Launches the Dell and System Center home page.
  • Dell TechCenter – Launches the home page for Dell and ConfigMgr on Dell TechCenter.

DellRClick

Perform the following steps to add the additional Dell Extensions:

  1. Close any existing instances of the ConfigMgr Console.
  2. Download LaunchDelliDracV2.  Extract both .ps1 files and copy to C:\PowerShell\CM12RClickTools\
  3. Extract Dell.XML from the downloaded .zip and copy to “C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\XmlStorage\Extensions\Actions\3fd01cd1-9e01-461e-92cd-94866b8d1f39\Dell.xml,” (you may need to create the Actions and GUID folder or replace the file created previously).
  4. Copy that same Dell.xml to C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\XmlStorage\Extensions\Actions\ed9dee86-eadd-4ac8-82a1-7234a4646e62\ (you may need to create the Actions and GUID folder).
  5. Launch the ConfigMgr console.
  6. Navigate to a device, right-click and view the additional actions from the Dell menu.

PowerShell for DellLaunchSystemInfo.ps1 (Hover and select ‘View Source’ to copy code):

$ComputerName = $args[0]
$SiteServer = $args[1]
$SiteNamespace = $args[2]
"Querying {0} for {1} Service Tag" -f $SiteServer, $ComputerName
$SysInfo  = get-wmiobject -ComputerName $SiteServer -Namespace $SiteNamespace -query "select SMS_G_System_PC_BIOS.SerialNumber from  SMS_R_System inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_SYSTEM_ENCLOSURE on SMS_G_System_SYSTEM_ENCLOSURE.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.Name = '$ComputerName' and SMS_G_System_SYSTEM_ENCLOSURE.Manufacturer like 'Dell%'"
if (($SysInfo -ne $null) -and ($SysInfo.SerialNumber.ToString().length -ge 5)) {
"Service Tag is {0}" -f $SysInfo.SerialNumber
$strURL = "http://www.dell.com/support/troubleshooting/us/en/19/Servicetag/"+ $SysInfo.SerialNumber
start $strURL
Start-Sleep 5
}
else {
write-host  "Dell Service Tag not found. Ensure the following:" -foregroundcolor red
write-host "Healthy Client" -foregroundcolor red
write-host "Running on PowerEdge Server" -foregroundcolor red
write-host "OMSA is installed on server" -foregroundcolor red
write-host "ConfigMgr inventorying Remote Access Service Port" -foregroundcolor redstart-sleep 60
}

PowerShell for DellLaunchiDRAC.ps1 (Hover and select ‘View Source’ to copy code):

$ComputerName = $args[0]
$SiteServer = $args[1]
$SiteNamespace = $args[2]
"Querying {0} for {1} DRAC URl" -f $SiteServer, $ComputerName
$DRACInfo  = get-wmiobject -ComputerName $SiteServer -Namespace $SiteNamespace  -query "select SMS_G_System_DELL_RemoteAccessServicePort.AccessInfoIPV4 from  SMS_R_System inner join  SMS_G_System_DELL_RemoteAccessServicePort on SMS_G_System_DELL_RemoteAccessServicePort.ResourceId =  SMS_R_System.ResourceId where SMS_R_System.Name = '$Computername'"
if (($DRACInfo -ne $null) -and ($DRACInfo.AccessInfoIPv4.ToString().length -ge 5) ) {
"Address is {0}" -f $DRACInfo.AccessInfoIPV4
start $DRACInfo.AccessInfoIPV4
Start-Sleep 5
}
else {
write-host "DRAC IP not found. Ensure the following:" -foregroundcolor red
write-host "Healthy Client" -foregroundcolor red
write-host "Running on PowerEdge Server" -foregroundcolor red
write-host "OMSA is installed on server" -foregroundcolor red
write-host "ConfigMgr inventorying Remote Access Service Port" -foregroundcolor red
start-sleep 60
}

XML for updated Dell.xml file (Hover and select ‘View Source’ to copy code):

<ActionDescription Class="Group" DisplayName="Dell" MnemonicDisplayName="Dell" Description="Dell Actions">
<ShowOn>
	<string>DefaultHomeTab</string><string>ContextMenu</string>
</ShowOn>
<ActionGroups>
	<ActionDescription Class="Executable" DisplayName="Launch iDRAC" MnemonicDisplayName="Launch iDRAC" Description = "Launch iDRAC in Web Browser" RibbonDisplayType="TextAndSmallImage">
		<ShowOn>
			<string>ContextMenu</string>
			<string>DefaultHomeTab</string>
		</ShowOn>
		<Executable>
			<FilePath>PowerShell.exe</FilePath>
			<Parameters>-ExecutionPolicy RemoteSigned -File C:\PowerShell\CM12RClickTools\DellLaunchiDRAC.ps1 "##SUB:Name##" "##SUB:__Server##" "##SUB:__Namespace##"  </Parameters>
		</Executable>
	</ActionDescription>
	<ActionDescription Class="Executable" DisplayName="Show System and Warranty Information" MnemonicDisplayName="Show System and Warranty Information" Description = "Show System and Warranty Information in Web Browser" RibbonDisplayType="TextAndSmallImage">
	<ShowOn>
		<string>ContextMenu</string>
		<string>DefaultHomeTab</string>
	</ShowOn>
	<Executable>
		<FilePath>PowerShell.exe</FilePath>
		<Parameters>-ExecutionPolicy RemoteSigned -File C:\PowerShell\CM12RClickTools\DellLaunchSystemInfo.ps1 "##SUB:Name##" "##SUB:__Server##" "##SUB:__Namespace##"  </Parameters>
	</Executable>
	</ActionDescription>
	<ActionDescription Class="Executable" DisplayName="Dell and System Center" MnemonicDisplayName="Dell and System Center" Description = "Launch Dell-System Center Integration Home Page" RibbonDisplayType="TextAndSmallImage">
	<ShowOn>
		<string>ContextMenu</string>
		<string>DefaultHomeTab</string>
	</ShowOn>
	<Executable>
		<FilePath>http://www.dell.com/systemcenter</FilePath>
		<Parameters></Parameters>
	</Executable></ActionDescription>
	<ActionDescription Class="Executable" DisplayName="Dell TechCenter" MnemonicDisplayName="Dell TechCenter" Description = "Launch Dell TechCenter to ConfigMgr Home Page" RibbonDisplayType="TextAndSmallImage">
	<ShowOn>
		<string>ContextMenu</string>
		<string>DefaultHomeTab</string>
	</ShowOn>
	<Executable>
		<FilePath>http://www.dell.com/ConfigMgr</FilePath>
		<Parameters></Parameters>
	</Executable></ActionDescription>
</ActionGroups>
</ActionDescription>