SQL Report – Detailed Advertisement Report

SELECT dbo.v_R_System.Netbios_Name0 AS Name,

 dbo.v_ClientAdvertisementStatus.ResourceID,

 dbo.v_ClientAdvertisementStatus.LastStateName AS [Status],

 dbo.v_ClientAdvertisementStatus.LastStatusMessageIDName AS [Status Detail],

 CONVERT(datetime,

            SWITCHOFFSET(

                CONVERT(datetimeoffset,

                    dbo.v_ClientAdvertisementStatus.LastStatusTime),

  DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS StatusTime,

 dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OSName,

 dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS SP,

 dbo.v_R_System.AD_Site_Name0 AS SiteName

FROM dbo.v_ClientAdvertisementStatus LEFT OUTER JOIN

 dbo.v_GS_OPERATING_SYSTEM ON dbo.v_ClientAdvertisementStatus.ResourceID =

 dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN

 dbo.v_R_System ON dbo.v_ClientAdvertisementStatus.ResourceID = dbo.v_R_System.ResourceID

WHERE (dbo.v_ClientAdvertisementStatus.AdvertisementID = '2D020081')

ORDER BY Name