Querying for Advertisement Status in SCCM

2014-07-21 MMS
Share on:

The following query can be run in SQL against your CAS or Primary Site to collect the status of one or more advertisements. I find it useful to run this instead of the built-in reports because I can more easily link out to other sources, in this case my Active Directory Users table (v_r_user) and add/drop data as needed.

 1select computer.Netbios_name0 as 'Host Name'
 2	 , ws.LastHWScan as 'Last HW Scan'
 3     , ad.AdvertisementID as 'Advertisement ID'
 4     , ad.AdvertisementName as 'Advertisement Name'
 5     , pkg.Name AS 'Package Name'
 6     , ad.ProgramName as 'Program Name'
 7     , adState.LastAcceptanceStatusTime as 'Adv. Acceptance Status Time'
 8     , adState.LastAcceptanceStateName as 'Adv. Acceptance State'
 9     , adState.LastAcceptanceMessageIDname as 'Adv. Acceptance Status'
10     , adState.LastStatusmessageIDName as 'Adv. Status Message'
11     , adState.LastStatusTime as 'Adv. State Time'
12     , adState.LaststateName as 'Adv. State'
13     , adState.LastExecutionResult  as 'Adv. Last Result Code'
14     , adState.LastExecutionContext as 'Adv. Last User Context'
15	 , users.Full_User_Name0 as 'Top User Full Name'
16	 , users.Mail0 as 'Top User Email'
17  from v_Advertisement ad
18  join v_Package pkg 
19    on ad.PackageID = pkg.PackageID
20  join v_ClientAdvertisementStatus adState 
21    on ad.AdvertisementID = adState.AdvertisementID
22  join v_r_system computer 
23    on computer.Resourceid = adState.resourceid
24  left join v_GS_WORKSTATION_STATUS ws
25    on computer.resourceid = ws.resourceid
26  left join v_GS_SYSTEM_CONSOLE_USAGE usage
27    on computer.ResourceID = usage.ResourceID
28  left join v_R_User users
29    on usage.TopConsoleUser0 = users.Unique_User_Name0
30 where ad.AdvertisementID in (
31		/* Use subquery to make it easier to find multiple advertisements, 
32		   or to swap out to find pacakges, or any other criteria */
33		select AdvertisementID
34		  from v_advertisement 
35		 where advertisementname like '%Lync%'
36      );

No comments