How to Determine Advertisement Status

Applies To: System Center Configuration Manager 2007, System Center Configuration Manager 2007 R2, System Center Configuration Manager 2007 R3, System Center Configuration Manager 2007 SP1, System Center Configuration Manager 2007 SP2

To determine advertisement status in Microsoft System Center Configuration Manager 2007, you can use the queries described in this section.

Note

These queries query the status messages directly and might take some time to complete because there can be many status messages.

For more information about using these queries, see How to Perform a Synchronous Configuration Manager Query by Using Managed Code and How to Perform a Synchronous Configuration Manager Query by Using WMI.

For more queries about advertisement status and summarization, you can use SMS_ClientAdvertisementStatus Server WMI Class and SMS_ClientAdvertisementSummary Server WMI Class.

Queries

Client Program Install

The following query returns the clients that have successfully installed a program. You need to check for both message identifiers because the program can report status with an exit code (10008) or an install status MIF file (10009).

' Returns clients that have successful installed a program
SELECT msg.MachineName, msg.SiteCode, ad.ProgramName
FROM SMS_StatusMessage msg
     JOIN SMS_StatMsgAttributes attr ON msg.RecordID = attr.RecordID
     JOIN SMS_Advertisement ad ON attr.AttributeValue = ad.AdvertisementID
WHERE msg.Component = "Software Distribution"
AND   (msg.MessageID = 10008 or msg.MessageID = 10009)
AND   attr.AttributeID = 401
ORDER BY ad.ProgramName

Clients That Have Installed a Specific Advertised Program

This query returns the clients that have successfully installed a specific advertised program.

' Returns clients that have successfully installed a specific advertised program
SELECT msg.MachineName, msg.SiteCode
FROM SMS_StatusMessage msg
     JOIN SMS_StatMsgAttributes attr ON msg.RecordID = attr.RecordID
WHERE msg.Component = "Software Distribution"
and   (msg.MessageID = 10008 or msg.MessageID = 10009)
and   attr.AttributeID = 401
and   attr.AttributeValue = "<AdvertisementID>"

Clients That Have Not Installed a Specific Advertised Program

The previous queries show which clients successfully installed an advertised program. Determining which collection members have not installed the advertised program can be more involved if the advertisement specified subcollections. The following query determines which clients of the All Systems (SMS00001) collection (substitute your collection member class for SMS_CM_RES_COLL_SMS00001) have not installed the advertised program. If the advertisement specified subcollections, the query must be run for each subcollection.

' Returns which clients of a collection have not installed the advertised program
SELECT Name
FROM SMS_CM_RES_COLL_SMS00001
WHERE NOT Name IN (SELECT msg.MachineName 
FROM SMS_StatusMessage msg
     JOIN SMS_StatMsgAttributes attr ON msg.RecordID = attr.RecordID
WHERE msg.Component = "Software Distribution"
AND   (msg.MessageID = 10008 or msg.MessageID = 10009)
AND   attr.AttributeID = 401
AND   attr.AttributeValue = "<AdvertisementID>")

See Also

Concepts

How to Perform an Asynchronous Configuration Manager Query by Using Managed Code
How to Perform a Synchronous Configuration Manager Query by Using Managed Code
How to Perform an Asynchronous Configuration Manager Query by Using WMI
How to Perform a Synchronous Configuration Manager Query by Using WMI
SMS_StatusMessage Server WMI Class
SMS_StatMsgAttributes Server WMI Class
Calling Configuration Manager Code Snippets