{"id":688,"date":"2022-06-27T11:14:30","date_gmt":"2022-06-27T18:14:30","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/powershell-community\/?p=688"},"modified":"2022-06-27T11:14:30","modified_gmt":"2022-06-27T18:14:30","slug":"reading-configuration-manager-status-messages-with-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/powershell-community\/reading-configuration-manager-status-messages-with-powershell\/","title":{"rendered":"Reading Configuration Manager Status Messages With PowerShell"},"content":{"rendered":"

Q:<\/strong> I can read Configuration Manager status messages using the Monitoring<\/em> tab. Can I do it using PowerShell?<\/p>\n

A:<\/strong> Yes you can! We can accomplish this using SQL\/WQL queries, plus the Win32 function FormatMessage.<\/p>\n

Better understanding Status Messages<\/h2>\n

Before we get our hands dirty we need to understand how the Configuration Manager assembles these messages and why we can’t just query it from some table, view, or WMI class.<\/p>\n

To avoid storage or performance issues and to provide better standardization, the Config Manager stores only message’s key information (and the ones who change from message to message), and uses a Win32 function called FormatMessage<\/strong> together with a DLL to assembly and display the full message.<\/p>\n

At first, it seems intimidating, specially with the whole Win32 function thing, but it’s actually pretty simple. Let’s take a look on one of these messages, so we can visualize what we want to accomplish.<\/p>\n

Distribution Manager failed to connect to the distribution point [\"Display=\\\\CMGRDP1.contoso.com\\\"]MSWNET:[\"SMS_SITE=PS1\"]\\\\CMGRDP1.contoso.com\\. Check your network and firewall settings.\n<\/code><\/pre>\n
This message states a failed content distribution to a Distribution Point. If we remove the part of the message containing the DP information:<\/p>\n
[\"Display=\\\\CMGRDP1.contoso.com\\\"]MSWNET:[\"SMS_SITE=PS1\"]\\\\CMGRDP1.contoso.com\\\n<\/code><\/pre>\n
we end up with a standard message that can be used every time this problem occurs.<\/p>\n
Querying useful information<\/h2>\n
Now that we have an overview of the Status Message structure, let’s gather the information available on the Config Manager database. For the purpose of this post, we will use failed distribution messages, like the one we saw above.<\/p>\n
    \n
  • The WMI classes that store Status Message information interesting for us are SMS_StatusMessage<\/strong> and SMS_StatMsgModuleNames<\/strong>.<\/li>\n
  • For content distribution status we will use the SMS_DistributionDPStatus<\/strong> class.<\/li>\n
  • The SQL views for these classes are v_StatusMessage<\/strong>, v_StatMsgModuleNames<\/strong> and vSMS_distributionDPStatus<\/strong> respectively.<\/li>\n
  • For performance sake and the SQL language accepting more complex queries we are going to use it for our exercise. This SQL query should return all packages from our Distribution Point which the status is not Success<\/em> or InProgress<\/em><\/li>\n<\/ul>\n
    SELECT  *\nFROM    vSMS_DistributionDPStatus\nWHERE   [Name] = 'CMGRDP1.contoso.com'\n        AND MessageState NOT IN (1,2)<\/code><\/pre>\n
    On the result, we are interested on some key columns: MessageID<\/strong>, LastStatusID<\/strong>, MessageSeverity<\/strong> and the InsString(n)<\/strong>.<\/p>\n
      \n
    • The MessageID<\/strong> and MessageSeverity<\/strong> we will use with the FormatMessage<\/strong> function.<\/li>\n
    • The LastStatusID<\/strong> we will use to join with the other views, who name this column RecordID<\/strong>.<\/li>\n
    • And perhaps the more interesting ones, the InsString(n)<\/strong> columns.<\/li>\n<\/ul>\n
      These columns, InsString1<\/strong>, InsString2<\/strong>, InsString3<\/strong>, …, InsString10<\/strong> contain the custom part of the message. Let’s look at one row of the above query shall we?<\/p>\n\n\n\n\n\n
      \n        ID1<\/sup>\n      <\/th>\n\n        MessageID\n      <\/th>\n\n        LastStatusID\n      <\/th>\n\n        MessageSeverity\n      <\/th>\n\n        InsString12<\/sup>\n      <\/th>\n<\/tr>\n<\/thead>\n
      \n        47365\n      <\/td>\n\n        2391\n      <\/td>\n\n        216172782348300122\n      <\/td>\n\n        -1073741824\n      <\/td>\n\n        [“Display=\\CMGRDP1.contoso.com”]MSWNET:[“SMS_SITE=PS1”]\\CMGRDP1.contoso.com\n      <\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
        \n
      • 1<\/sup> The ID<\/strong> column is to help us to identify this specific message later.<\/li>\n
      • 2<\/sup> The other InsString<\/strong> columns are null<\/li>\n<\/ul>\n
        Won’t you look at that! The info on InsString1<\/strong> is exactly the custom part of our message! Let’s join the other views, and we will have all the information needed to proceed. We are also including information from v_Package<\/strong>, or SMS_Package<\/strong> on WMI, to make the end result more meaningful.<\/p>\n
        SELECT\n        pkg.Name\n        ,pkg.PackageID\n        ,dps.LastUpdateDate\n        ,stm.ModuleName\n        ,smn.MsgDLLName\n        ,dps.MessageID\n        ,CASE\n            WHEN dps.MessageSeverity = '1073741824' THEN '1073741824' --Informational\n            WHEN dps.MessageSeverity = '-2147483648' THEN '2147483648' --Warning\n            WHEN dps.MessageSeverity = '-1073741824' THEN '3221225472' --Error\n        END AS 'SeverityCode'\n        ,dps.InsString1\n        ,dps.InsString2\n        ,dps.InsString3\n        ,dps.InsString4\n        ,dps.InsString5\n        ,dps.InsString6\n        ,dps.InsString7\n        ,dps.InsString8\n        ,dps.InsString9\n        ,dps.InsString10\nFROM    vSMS_distributionDPStatus AS dps\nLEFT JOIN    v_StatusMessage AS stm ON stm.RecordID = dps.LastStatusID\nLEFT JOIN    v_StatMsgModuleNames AS smn ON smn.ModuleName = stm.ModuleName\nLEFT JOIN    v_Package AS pkg ON pkg.PackageID = dps.PackageID\nWHERE   dps.MessageState NOT IN (1,2)\n        AND dps.ID = '47365'<\/code><\/pre>\n
        We are using the ID<\/strong> from the previous query to stick to our result. Removing this condition should bring all package distribution failure for that site.<\/p>\n
        The Case<\/em> statement is necessary because the Message Severity is actually hexadecimal, thus:<\/p>\n
        PS C:\\> '{0:X}' -f -1073741824\nC0000000\nPS C:\\> '{0:X}' -f 3221225472\nC0000000\nPS C:\\>\nPS C:\\> '{0:X}' -f -2147483648\n80000000\nPS C:\\> '{0:X}' -f 2147483648\n80000000\nPS C:\\><\/code><\/pre>\n
        Let’s see what the result of this query looks like.<\/p>\n