Tagged: Status

SCCM Status Message Query for Advertismet

Made a small combination of a couple of queries, so this will show “Clients That Ran, Received, Rejected or Started a Specific Advertised Program

SELECT stat.*, ins.*, att1.*, att1.AttributeTime
FROM SMS_StatusMessage AS stat 
LEFT JOIN SMS_StatMsgInsStrings AS ins ON stat.RecordID = ins.RecordID
LEFT JOIN SMS_StatMsgAttributes AS att1 ON stat.RecordID = att1.RecordID 
INNER JOIN SMS_StatMsgAttributes AS att2 ON stat.RecordID = att2.RecordID

WHERE
	stat.ModuleName = "SMS Client" AND
	(
		(stat.MessageID = 10005 AND att2.AttributeID = 401)  OR 
		(stat.MessageID >= 10018 AND stat.MessageID <=10019 and att2.AttributeID = 401) OR 
		(stat.MessageID = 10002 AND att2.AttributeID = 401) OR 
		(stat.MessageID >= 10008 AND stat.MessageID <= 10009)
	)
	AND att2.AttributeValue = ##PRM:SMS_StatMsgAttributes.AttributeValue## 
	AND att2.AttributeTime >= ##PRM:SMS_StatMsgAttributes.AttributeTime## 
ORDER BY att1.AttributeTime DESC