SCCM Advertisement Status Reporting

One of the issues I have with SCCM Reporting on Advertisements is that they are always categorized based on current status.  Thus you have the Success and the Waiting and the Failed all in different reports.

I decided to set out to fix this.  The main reason is, now I can send my new report to end users and they can just look up their computer and see the status of the installation and I don't have to walk them through looking at all of the different status reports.

So here's the report I put together.
********************************



select distinct
v_R_System.Netbios_Name0 AS [Computer Name], 
v_GS_xxx_CustomInfo0.Div_Code0 AS [Div],
v_GS_xxx_CustomInfo0.Support_Area0 AS [Area], 
[Top or Last User] = CASE
  WHEN Coalesce(v_R_User.Name0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, v_R_User.Name0, v_GS_COMPUTER_SYSTEM.UserName0, userhist.Name0, hist.OldTCU) is not null THEN Coalesce(userconsole.Name0, v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0, usercs.Name0, v_GS_COMPUTER_SYSTEM.UserName0, userhist.Name0, hist.OldTCU)
  WHEN v_R_System.user_name0 is not null THEN v_R_System.user_domain0 + '\' + v_R_System.user_name0
  ELSE 'Unknown'
 END,
v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System], 
v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Level], 
v_GS_OPERATING_SYSTEM.Description0 AS [Computer Description],
LastStatusMessageIDName AS [Last known Advertisement Status],
DATEADD(ss,@__timezoneoffset,LastStatusTime) as LastStatusTime, 
AdvertisementID
  


from v_ClientAdvertisementStatus
join v_R_System on v_ClientAdvertisementStatus.ResourceID=v_R_System.ResourceID
left join v_RA_System_SMSInstalledSites site on v_ClientAdvertisementStatus.ResourceID=site.ResourceID
left join v_GS_xxx_CustomInfo0 on (v_R_System.ResourceID = v_GS_xxx_CustomInfo0.ResourceID)
left join v_GS_COMPUTER_SYSTEM on (v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID) 
left join v_GS_OPERATING_SYSTEM on (v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID)
left join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP on (v_R_System.ResourceID = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.ResourceID)
left join v_R_User on (v_R_User.Unique_User_Name0 = v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0)
LEFT JOIN (SELECT ResourceID, Max(TimeStamp) as [Old], Max(TopConsoleUser0) as [OldTCU] FROM v_HS_SYSTEM_CONSOLE_USAGE GROUP BY ResourceID) hist on v_R_System.ResourceID = hist.ResourceID
LEFT JOIN v_R_User userconsole on v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP.TopConsoleUser0 = userconsole.Unique_User_Name0
LEFT JOIN v_R_User usercs on v_GS_COMPUTER_SYSTEM.UserName0 = usercs.Unique_User_Name0 
LEFT JOIN v_R_User userhist on hist.OldTCU = userhist.Unique_User_Name0
LEFT JOIN v_R_User usersys on v_R_System.User_Name0 = usersys.Unique_User_Name0


where v_ClientAdvertisementStatus.AdvertisementID=@AdvertID

********************************

A couple of notes:

• You can link to the Software Distribution - Advertisement Status - Advertisement status messages for a particular client and advertisement report by referencing AdvertID as Column 10 (or whatever it ends up being for you) and Name as Column 1.
• v_GS_xxx_CustomInfo0 is a custom table we have that contains info we pull from the registry, you probably don't have this table so you'll need to edit accordingly.
• There is a prompt for the Advertisement ID - This was pulled from any of the other built-in reports for advertisement status.
• Thanks to by buddy NP for putting together the cool lookup of the Top/Last User Name info.

In the end you have a report that will give you all of the computers that Advertisement was sent to with all of the Last Known Status's listed in one big list.