PowerShell SCCM display Status Message Queries

This topic contains 3 replies, has 2 voices, and was last updated by Profile photo of Rob Simmers Rob Simmers 2 years, 2 months ago.

  • Author
    Posts
  • #19230
    Profile photo of Fabian Küng
    Fabian Küng
    Participant

    Hey there

    I'm trying to program a rightclick tool for sccm in PowerShell which displays the Status Message Queries of the right-clicked device.

    I'm stuck in displaying the description of the message. So far I have this WQL Query:

    select SMS_StatusMessage.*, SMS_StatMsgInsStrings.*, SMS_StatMsgAttributes.* 
    from  SMS_StatusMessage left join SMS_StatMsgInsStrings on SMS_StatMsgInsStrings.RecordID = SMS_StatusMessage.RecordID
    left join SMS_StatMsgAttributes on SMS_StatMsgAttributes.RecordID = SMS_StatusMessage.RecordID 
    where SMS_StatusMessage.MachineName = "MyMachineName"
    

    but this query doesn't give me the description of the Status Message as you can see in the SCCM -> Monitoring -> Status Message Queries -> All Status Messages from a Specific System

    Does anyone know how to display the description of a Status Message Query using Powershell?

    With best regards
    Fabian

  • #19241
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    Firstly, this has zero to do with Powershell. This is a SCCM Database query. Before you even start working on Powershell, you should test out and vet your SQL queries.

    Next, most tools written for SCCM use the SCCM WMI classes and don't query the database directly. If the information you are looking for isn't available in WMI, which is unlikely, there are SQL Views that have tables of information already joined so you don't have to work as much to get the data.

    SQL Server Views in System Center 2012 Configuration Manager

    The tables you are querying above are Production tables, so there is the probability that without knowing SQL and application security that your application could be susceptible to SQL injection attacks. Start with WMI Configuration Manager WMI Namespaces and Classes and only use SQL if there is something not in WMI.

    Lastly, your query is looking for a MachineName = "", so you're looking for MachineName equal nothing. Take a look at the SMS_StatusMessage WMI class with no computer defined and the refine your search to pull only what you need from WMI to increase performance.

  • #19242
    Profile photo of Fabian Küng
    Fabian Küng
    Participant

    Hey Rob Simmers

    Thanks for your answer.

    The code above isn't a SQL query, it is WMI Query.
    I'm executing this query with the PowerShell Cmdlet Get-WmiObject.

    I know that the MachineName is empty this is because I've used between the quotes and it doesn't display it.

    Best regards

  • #19256
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    That is not a valid WQL query. WMI (WQL) queries do not support JOIN. If the classes are associated, you can try using Associator:

    Otherwise, you would do something like this:

    #Assume there is more than one status message for a machine, so loop...
    $results = Get-WMIObject SMS_StatusMessage -Filter "MachineName='Machine123'" | ForEach {
        $StatMsgInsStrings = Get-WMIObject SMS_StatMsgInsStrings -Filter ("RecordID={0}" -f $_.RecordID)
        $StatMsgAttributes = Get-WMIObject SMS_StatMsgAttributes -Filter ("RecordID={0}" -f $_.RecordID)
        # $_ indicates current object of SMS_StatusMessage and then calculated properties allow you to return
        # a single Powershell object named $results 
        $_ | Select MessageID, MessageType, @{Label="InsStrValue";Expression={$StatMsgInsString.InsStrValue}}, @{Label="AttributeValue";Expression={$StatMsgAttributes.AttributeValue}}
    }
    
    $results

    I cannot test this command as I do not have SCCM setup here and cannot test, but this is much closer to what you need to do to get data from the 3 WMI classes via Powershell.

You must be logged in to reply to this topic.