Status message based collection

Long ago as part of a different post I provided a query to make collections based on status messages.  I have since had several customers asking for this ability, making me aware that the blog post was not very discoverable.  Not surprising since the focus of that post was different.

My older blog post was done with SCCM 2007 and things have changed slightly with ConfigMgr 2012.  Collections are specifically aimed at user and system resources and thus the resource class is more strictly enforced now than it was in 2007 and older versions.  That requires a slight change to how the statmsg query is written.  Originally I was pulling against the statmsg tables, joined with the system resource table.  That needs to be changed to pull against the system resource table and joined to the other tables.  It will then be allowed in a device collection.

Lets get to an example, as I think that will make things very clear.  I ran a status message query in the UI for all machines that received a deployed program, which is a 10002 status message.  The status message query has several inputs, one of which is a specific deployment.  For my collection I didn't bother to do that filter.  For my collection I want to get all machines who got any deployment message in the last 30 days.  Here is what that query looks like:

select SYS.ResourceID,SYS.ResourceType,SYS.Name,SYS.SMSUniqueIdentifier,SYS.ResourceDomainORWorkgroup,SYS.Client from sms_r_system as SYS   join SMS_StatusMessage as stat   on stat.machinename = SYS.name   where stat.ModuleName = "SMS Client" and stat.MessageID = 10002 and DateDiff(dd,stat.Time, GetDate()) <30

By creating a collection, creating a query against a system resource, and using the query designer you should be able to cut and paste this in.  You can adjust the MessageID and time span according to your own needs.