September, 2011

Quick post today.

Here’s a t-sql query to update a collection based on a specific Client Status Message. I’m sure you can think of many reasons why this would be handy.

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>"

Change the <Enter Status Message ID Here>, and the <Enter Status Message Description Here> to the messages you are after, and save it as a Collection Membership Rule

Matt Shadbolt