locked
How to create Collection Based on Status Message RRS feed

  • Question

  • It's possible create a collection based on Status Message?

    If yes, please post how

    tks


    Allan

    Wednesday, June 4, 2014 6:40 PM

Answers

  • The short answer is yes.  It is all based on what you want to accomplish.  Here is an example

    SELECT SMS_R_SYSTEM.ResourceID,
      SMS_R_SYSTEM.ResourceType,
      SMS_R_SYSTEM.Name,
      SMS_R_SYSTEM.SMSUniqueIdentifier,
      SMS_R_SYSTEM.ResourceDomainORWorkgroup,
      SMS_R_SYSTEM.Client
    FROM sms_R_System  
    JOIN sms_statusmessage ON sms_R_System.netbios_name0 = sms_statusmessage.machinename   
    JOIN sms_StatMsgInsStrings ON sms_statusmessage.RecordID = sms_StatMsgInsStrings.RecordID  
    WHERE sms_statusmessage.messageid = "<Enter Status Message ID Here>" 
    AND sms_StatMsgInsStrings.InsStrValue = "<Enter Status Message Description Here>"

    There is a Status Message Excel document that was on several blogs like myITForum and John Nelson that lists every ID number or message text.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    • Marked as answer by AllanOSilva Tuesday, June 17, 2014 11:35 AM
    Friday, June 6, 2014 8:53 PM
  • the query given by matthew is WQL but not SQL.you cannot run the WQL query in SQL which do not support.

    The error what you get is expected .some of the wmi entries that exist in sms provider may not appear in sccm query design.

    Do not click on show query design as sms_statusmessage attributes cannot be retrived .After you created the WQL Query ,just paste in query box and click Ok (Ignore the error message box).

    wait for sometime and see if that display any computers.



    Eswar Koneti | Configmgr blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: Eskonr

    • Marked as answer by AllanOSilva Tuesday, June 17, 2014 11:35 AM
    Friday, June 13, 2014 5:41 AM

All replies

  • The short answer is yes.  It is all based on what you want to accomplish.  Here is an example

    SELECT SMS_R_SYSTEM.ResourceID,
      SMS_R_SYSTEM.ResourceType,
      SMS_R_SYSTEM.Name,
      SMS_R_SYSTEM.SMSUniqueIdentifier,
      SMS_R_SYSTEM.ResourceDomainORWorkgroup,
      SMS_R_SYSTEM.Client
    FROM sms_R_System  
    JOIN sms_statusmessage ON sms_R_System.netbios_name0 = sms_statusmessage.machinename   
    JOIN sms_StatMsgInsStrings ON sms_statusmessage.RecordID = sms_StatMsgInsStrings.RecordID  
    WHERE sms_statusmessage.messageid = "<Enter Status Message ID Here>" 
    AND sms_StatMsgInsStrings.InsStrValue = "<Enter Status Message Description Here>"

    There is a Status Message Excel document that was on several blogs like myITForum and John Nelson that lists every ID number or message text.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    • Marked as answer by AllanOSilva Tuesday, June 17, 2014 11:35 AM
    Friday, June 6, 2014 8:53 PM
  • I tried creating a query but it appears the error below:

    I have not found the tables SMS_Status, in System Resource.


    Allan

    Monday, June 9, 2014 5:57 PM
  • Collection queries are created through WQL which user WMI in conjunction with SMS provider. Though it is similar to SQL, every syntax may not be supported. Also table/view names will be different.

    Try running the query in SQL management studio query editor to see the output. Then you can use SCCM queries node in the console to create a WQL query and use it for creating the collection

    Tuesday, June 10, 2014 7:16 AM
  • the query given by matthew is WQL but not SQL.you cannot run the WQL query in SQL which do not support.

    The error what you get is expected .some of the wmi entries that exist in sms provider may not appear in sccm query design.

    Do not click on show query design as sms_statusmessage attributes cannot be retrived .After you created the WQL Query ,just paste in query box and click Ok (Ignore the error message box).

    wait for sometime and see if that display any computers.



    Eswar Koneti | Configmgr blog: www.eskonr.com | Linkedin: Eswar Koneti | Twitter: Eskonr

    • Marked as answer by AllanOSilva Tuesday, June 17, 2014 11:35 AM
    Friday, June 13, 2014 5:41 AM
  • In addition, as the error message implies, if you use a WQL for a class that is not available in the report builder then SCCM will not render it in the GUI, giving the error that is shown.  It is a "feature of the language not supported in design view".

    If it is not a syntax error, you can ignore the error, save and run the query. You just can't edit the query in design view. If it is a syntax error running the query will return a syntax error.

    Monday, June 16, 2014 7:24 PM