Adding AD site to Query

select  distinct
    R.Netbios_Name0 AS ‘Computer Name’,
    R.Resource_Domain_OR_Workgr0 AS ‘Domain/Workgroup’,
    S.SiteName as ‘SMS Site Name’,
    R.AD_Site_name0 as ‘AD Site’,
    ‘Top Console User’ = CASE
        when (SCUM.TopConsoleUser0 is NULL or SCUM.TopConsoleUser0 = ‘-1’) then ‘Unknown’
        Else SCUM.TopConsoleUser0
    End,
    OS.Caption0 AS ‘Operating System’,
    OS.CSDVersion0 AS ‘Service Pack Level’,
    SEU.SerialNumber0 AS ‘Serial Number’,
    SEU.SMBIOSAssetTag0 AS ‘Asset Tag’,
    CS.Manufacturer0 AS ‘Manufacturer’,
    CS.Model0 AS ‘Model’,
    RAM.TotalPhysicalMemory0/1024 AS ‘Memory (MB)’,
    Pro.NormSpeed0 AS ‘Processor (GHz)’,
    LD.Size0 as ‘Disk Space (MB) on C:\’,
    LD.FreeSpace0 as ‘Free Disk Space (MB) on C:\’
from
    v_R_System_Valid R
    inner join v_GS_OPERATING_SYSTEM OS on (OS.ResourceID = R.ResourceID)
    left join v_GS_SYSTEM_ENCLOSURE_UNIQUE SEU on (SEU.ResourceID = R.ResourceID)
    inner join v_GS_COMPUTER_SYSTEM CS on (CS.ResourceID = R.ResourceID)
    inner join v_GS_X86_PC_MEMORY RAM on (RAM.ResourceID = R.ResourceID)
    inner join v_GS_PROCESSOR Pro on (Pro.ResourceID = R.ResourceID)
    inner join v_FullCollectionMembership FCM on (FCM.ResourceID = R.ResourceID)
    left  join v_Site s on (FCM.SiteCode = S.SiteCode)
    inner join v_GS_LOGICAL_DISK LD on (LD.ResourceID =R.ResourceID) and LD.DeviceID0 = SUBSTRING(OS.WindowsDirectory0,1,2)
    left outer join v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on (SCUM.ResourceID = R.ResourceID)
Where
    FCM.CollectionID = @CollectionID
Order by
    R.Netbios_Name0

Leave a Reply

Your email address will not be published. Required fields are marked *