Tracking Advertisements That Have Been Received But Not Started

 

This SQL query will provide you with an example that uses a SubSelect method to let you monitor all of the machines that have received a specified advertisement but have not yet stated the installation. The value added by this query is that it is good for advertisement tracking purposes.

 

To use this query you must Replace xxx With The Advertisement that You Wish To Track From Advertisements > Advertisement ID in the SMS console.

 

SQL Query:

 

Declare @ProgId NVarChar(128)

Set @ProgId = 'XXX'

 

Select

MachineName 'Received, Not Started'

 

From vStatusMessages Astat

Join StatusMessageAttributes att1

on Astat.RecordID = att1.RecordID

 

Where AttributeValue = @ProgId

And MessageID = 10002 -- Received Status Code

And MessageID <> 10003 -- Failures Status Code

And MessageID <> 10004 -- Failures Status Code

And MessageID <> 10021 -- Failures Status Code

And ModuleName = 'Sms Client'

 

And MachineName not in

(Select all MachineName

From vStatusMessages Astat

Join StatusMessageAttributes As att1

On Astat.RecordID = att1.RecordID

Where AttributeValue = @ProgId

And MessageID = 10005 -- Started Status Code

And ModuleName = 'SMS Client')

 

Published Sunday, April 29, 2007 11:45 AM by dhite
Filed under:

Comments

No Comments