Querying for Advertisement Status in SCCM
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 );