BRONZE PARTNER:
BRONZE PARTNER:
Industry News:
| |
| |
|
|
|
|
|
| Tracking Advertisements That Have Been Received But Not Started |
|
|
|
By: Don Hite
Posted On: 5/1/2007
This article was Previously posted on Don Hite's Blog
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')
|
|
|
|
|
|
