Determining Advertisement Status

You can use the approach discussed in Determining Package Status to determine at a high level how many advertisements have been received and successfully run on the client. To determine more detailed information, such as which clients have or have not run the advertisement, you must use the status messages (SMS_StatusMessage) as the following queries show.

This 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 (100009).

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 = "Available Programs Manager (APM)"
AND   (msg.MessageID = 10008 or msg.MessageID = 10009)
AND   attr.AttributeID = 401
ORDER BY ad.ProgramName

This query returns the 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 = "Available Programs Manager (APM)"
and   (msg.MessageID = 10008 or msg.MessageID = 10009)
and   attr.AttributeID = 401
and   attr.AttributeValue = "<AdvertisementID>"

The previous queries showed you 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 sub-collections. 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 sub-collections, the query must be run for each sub-collection.

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 = "Available Programs Manager (APM)"
AND   (msg.MessageID = 10008 or msg.MessageID = 10009)
AND   attr.AttributeID = 401
AND   attr.AttributeValue = "<AdvertisementID>")

Currently, the only visibility as to which members of a collection have successfully installed a program is for collections of system resources (clients). There is no means to determine which members of a user collection or a group collection have installed a program.