SELECT DISTINCT
    adt.CollectionName                     AS 'Collection',
    adt.CollectionID                       AS 'Collection ID',
    ISNULL (vcol.CollectionComment, 'N/A') AS 'Collection Comment',
    adt.PackageName                        AS 'Software',
    CASE
        WHEN substring(sd.Name0,1,1) IN ('M',
                                         'D',
                                         'T')
        THEN UPPER(RTRIM('A'+ substring(sd.Name0,2,LEN(sd.Name0)-1)))
        ELSE UPPER(RTRIM(sd.Name0))
    END                                           AS 'Computer Name',
    sd.Name0                                      AS 'Computer Name Org',
    ISNULL (vos.Caption0, 'N/A')                  AS 'Operating System',
    ISNULL (sd.Resource_Domain_OR_Workgr0, 'N/A') AS 'Domain',
    ISNULL (
    (
        SELECT
            TOP (1) ou.System_OU_Name0
        FROM
            v_RA_System_SystemOUName ou
        WHERE
            ou.ResourceID = sd.ItemKey
        ORDER BY
            ou.System_OU_Name0 DESC ), 'N/A')                              AS 'Organizational Unit',
    adt.DeviceID                                                                   AS 'Resource ID',
    FORMAT (DATEADD (hh, (DATEDIFF (hh, GETUTCDATE(), GETDATE())), adt.SummarizationTime), 'G') AS
    'Last Status Check',
    (
        CASE
            WHEN adt.StatusType = 1
            THEN 'Success'
            WHEN adt.StatusType = 2
            THEN 'In Progress'
            WHEN adt.StatusType = 3
            THEN 'Requirements not met'
            WHEN adt.StatusType = 4
            THEN 'Unknown'
            WHEN adt.StatusType = 5
            THEN 'Error'
            ELSE 'N/A'
        END)              AS 'Status Type',
    adt.MessageID         AS 'Message ID',
    adt.StatusDescription AS 'Description'
FROM
    vSMS_ClassicDeploymentAssetDetails adt
INNER JOIN
    fn_DeploymentSummary(1033) DS
ON
    ds.PackageID = adt.PackageID
AND ds.CollectionID = adt.CollectionID
AND DS.FeatureType = 7
INNER JOIN
    System_DISC sd
ON
    sd.ItemKey = adt.DeviceID
AND ISNULL (sd.Decommissioned0, 0) = 0
LEFT JOIN
    v_RA_System_SystemOUName ou
ON
    ou.ResourceID = sd.ItemKey
LEFT JOIN
    v_GS_OPERATING_SYSTEM vos
ON
    vos.ResourceID = sd.ItemKey
LEFT JOIN
    vCollections vcol
ON
    vcol.SiteID = adt.CollectionID