SMSUG.ca

...your virtual communITy
Welcome to SMSUG.ca Sign in | Join | Help
in Search

Garth Jones

February 2013 - Posts

  • 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

  • Adding total HDD and RAM size Part 2

    For full details, please see
    http://social.technet.microsoft.com/Forums/en-ca/configmgrai/thread/7951b781-8354-490b-9b59-4fe4e68d1975
    SELECT DISTINCT
        CS.Name0,
        CASE 
            WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
            WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
            Else 'Unknown'
        END as 'Chassis',
        BIOS.SerialNumber0, 
        CS.Manufacturer0, 
        CS.Model0,
        OS.Caption0, 
        OS.CSDVersion0, 
        SCUM.TopConsoleUser0,
        R.User_Name0 AS 'Last Logged', 
        BIOS.ReleaseDate0, 
        NAC.IPAddress0,
        NAC.DefaultIPGateway0,
        RAM.TotalPhysicalMemory0,
        Sum(LD.Size0)
    FROM
        dbo.v_R_System R
        INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID 
        INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID 
        INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC ON R.ResourceID = NAC.ResourceID 
        INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID 
        LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON R.ResourceID = SCUM.ResourceID 
        INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on R.ResourceID = ES.ResourceID
        INNER JOIN dbo.v_GS_SYSTEM S on R.ResourceID = S.ResourceID
        INNER JOIN dbo.v_GS_X86_PC_MEMORY RAM on R.ResourceID = RAM.ResourceID
        INNER JOIN dbo.v_GS_LOGICAL_DISK LD on R.ResourceID = LD.ResourceID
    WHERE     
        NAC.IPEnabled0 = 1
        AND (NOT (NAC.DefaultIPGateway0 IS NULL)) 
        AND S.SystemRole0 = 'Workstation'
    Group by
        CS.Name0,
        CASE 
            WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
            WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
            Else 'Unknown'
        END,
        BIOS.SerialNumber0, 
        CS.Manufacturer0, 
        CS.Model0,
        OS.Caption0, 
        OS.CSDVersion0, 
        SCUM.TopConsoleUser0,
        R.User_Name0, 
        BIOS.ReleaseDate0, 
        NAC.IPAddress0,
        NAC.DefaultIPGateway0,
        RAM.TotalPhysicalMemory0
  • Adding Ram and HD

     

    SELECT DISTINCT
        CS.Name0,
        CASE 
            WHEN ES.ChassisTypes0 in ('3','4','6','7','15') THEN 'Desktop'
            WHEN ES.ChassisTypes0 in ('8','9','10','21') THEN 'Laptop'
            Else 'Unknown'
        END as 'Chassis',
        BIOS.SerialNumber0, 
        CS.Manufacturer0, 
        CS.Model0,
        OS.Caption0, 
        OS.CSDVersion0, 
        SCUM.TopConsoleUser0,
        R.User_Name0 AS 'Last Logged', 
        BIOS.ReleaseDate0, 
        NAC.IPAddress0,
        NAC.DefaultIPGateway0,
        RAM.TotalPhysicalMemory0,
        LD.DeviceID0,
        LD.Size0
     FROM
        dbo.v_R_System R
        INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID 
        INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID = CS.ResourceID 
        INNER JOIN dbo.v_GS_NETWORK_ADAPTER_CONFIGUR NAC ON R.ResourceID = NAC.ResourceID 
        INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID 
        LEFT OUTER JOIN dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM ON R.ResourceID = SCUM.ResourceID 
        INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ES on R.ResourceID = ES.ResourceID
        INNER JOIN dbo.v_GS_SYSTEM S on R.ResourceID = S.ResourceID
        INNER JOIN dbo.v_GS_X86_PC_MEMORY RAM on R.ResourceID = RAM.ResourceID
        INNER JOIN dbo.v_GS_LOGICAL_DISK LD on R.ResourceID = LD.ResourceID
     WHERE     
        NAC.IPEnabled0 = 1
        AND (NOT (NAC.DefaultIPGateway0 IS NULL)) 
        AND S.SystemRole0 = 'Workstation'
  • Microsoft True Up Report

    For full details please see

    http://www.myitforum.com/Forums/tm.aspx?m=238674&high=

     

    SELECT distinct 
        DisplayName0, 
        Count(arp.ResourceID) AS 'Count', 
        Publisher0, 
        @CollID as CollectionID 
    FROM 
        dbo.v_Add_Remove_Programs arp 
    JOIN dbo.v_FullCollectionMembership fcm on arp.ResourceID = fcm.ResourceID 
    WHERE  
        fcm.CollectionID = @CollID 
        AND (Publisher0 LIKE 'Microsoft%') 
        AND DisplayName0 NOT LIKE '%Hotfix%' 
        AND DisplayName0 NOT LIKE '%Security Update%' 
        AND DisplayName0 NOT LIKE '%Update for%' 
        AND DisplayName0 NOT LIKE '%.NET%' 
        AND DisplayName0 NOT LIKE '%Viewer%' 
        AND DisplayName0 NOT LIKE '%Language Pack%'  
        AND DisplayName0 NOT LIKE '%Internet Explorer%' 
        AND DisplayName0 NOT LIKE '%MSXML%' 
        AND DisplayName0 NOT LIKE '%SDK%' 
        AND DisplayName0 NOT LIKE '%C++%' 
        AND DisplayName0 NOT LIKE '%Redistributable%' 
        AND DisplayName0 NOT LIKE '%Search%' 
        AND DisplayName0 NOT LIKE '%SMS%' 
        AND DisplayName0 NOT LIKE '%Silverlight%' 
        AND DisplayName0 NOT LIKE '%Live Meeting%' 
        AND DisplayName0 NOT LIKE '%(KB%' 
        AND DisplayName0 NOT LIKE '%Office Web%' 
        AND DisplayName0 NOT LIKE '%Office %Proof%' 
        AND DisplayName0 NOT LIKE '%Server %Proof%' 
        AND DisplayName0 NOT LIKE '%Office %Shared%' 
        AND DisplayName0 NOT LIKE '%Baseline Security Analyzer%' 
        AND DisplayName0 NOT LIKE '%Compatibility Pack%' 
        AND DisplayName0 NOT LIKE '%User State Migration Tools%' 
    GROUP BY 
        DisplayName0, 
        Publisher0 
    ORDER BY 
        Publisher0 

This Blog

Syndication

News

Training

Powered by Community Server (Non-Commercial Edition), by Telligent Systems