MAC Address report

For full details please see:

http://social.technet.microsoft.com/Forums/en-ca/configmgrreporting/thread/19e0771c-eddc-43f7-956c-a5c9f0110e59

 

SELECT
    A.Name0,
    B.SerialNumber0 ,
    A.Manufacturer0,
    A.Model0,
    C.Name0 ,
    D.TotalPhysicalMemory0 ,
    sum(E.Size0) ,
    F.MACAddress0 ,
    F.IPAddress0 ,
    G.AD_Site_Name0 ,
    A.UserName0 ,
    H.Caption0 ,
    H.CSDVersion0,
    G.Creation_Date0 ,
    I.LastHWScan–,
—    BL.DriveLetter0,
—    BL.ProtectionStatus0,
—    BL.PersistentVolumeID0,
—    CASE
—        WHEN BL.ProtectionStatus0 = 1 THEN ‘Encrypted’
—        WHEN ((BL.ProtectionStatus0 = 0) AND (BL.PersistentVolumeID0 is not NULL)) THEN ‘Suspended’
—        ELSE ‘*** UNENCRYPTED ***’
—    END AS ‘Encryption_Status’
FROM
    dbo.v_R_System G
    join dbo.v_GS_COMPUTER_SYSTEM A on G.ResourceID = A.ResourceID
    join dbo.v_GS_PC_BIOS B  on G.ResourceID = B.ResourceID
    join dbo.v_GS_PROCESSOR C  on G.ResourceID = C.ResourceID
    join dbo.v_GS_X86_PC_MEMORY D  on G.ResourceID = D.ResourceID
    join dbo.v_GS_DISK E  on G.ResourceID = E.ResourceID
    join dbo.v_GS_NETWORK_ADAPTER_CONFIGUR F  on G.ResourceID = F.ResourceID
    join dbo.v_GS_OPERATING_SYSTEM H  on G.ResourceID = H.ResourceID
    join dbo.v_GS_WORKSTATION_STATUS I  on G.ResourceID = I.ResourceID
—    join dbo.v_GS_BITLOCKER_VOLUME_ENC BL on G.ResourceID = BL.ResourceID

WHERE
    G.Netbios_Name0 like ‘%’
    and F.MACAddress0 !=
    — AND BL.DriveLetter0 like ‘C:’
GROUP BY
    A.Name0,
    A.Manufacturer0,
    A.Model0,
    C.Name0,
    D.TotalPhysicalMemory0,
    G.AD_Site_Name0,
    A.UserName0,
    H.Caption0,
    H.CSDVersion0,
    G.Creation_Date0,
    I.LastHWScan,
    B.SerialNumber0,
    F.MACAddress0,
    F.IPAddress0
—    BL.DriveLetter0,
—    BL.ProtectionStatus0,
—    BL.PersistentVolumeID0

Leave a Reply

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