SCCM SQL Query - Detailed Deployment Statement

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 = 'xxxxxxxx')
ORDER BY Name

Change the advertisementID based on your environment