Garth Jones

Video card with Sound card.

Garth — Thu, 18 Apr 2013 18:50:48 GMT

For full details please see

http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740

select distinct 
    R.Netbios_Name0 as 'Name',
    R.User_Name0 as 'User',
    CS.Manufacturer0 as 'Manufacturer', 
    CS.Model0 as 'Model',
    BIOS.SerialNumber0 as 'Serial',
    SD.Name0 as 'Processor', 
    RAM.TotalPhysicalMemory0 as 'Memory',
    VC.Name0 as 'Video Card', 
    VC.VCRam as 'Video Card RAM',
    Sound.Description0 as 'Sound card'
from  
    dbo.v_R_System R 
    inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId 
    inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID 
    inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId 
    inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId 
    left outer join dbo.v_GS_SOUND_DEVICE Sound on Sound.ResourceID = R.ResourceId 
    left outer join (
        select     
            VC.ResourceID,
            VC.Name0,
            Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'VCRAM'
        from 
            dbo.v_GS_Video_Controller VC 
        Where
            VC.Name0 != 'ConfigMgr Remote Control Driver'
            and VC.AdapterRam0 is not null) VC on  R.ResourceID = VC.ResourceId 
where 
    SD.CompatibleIDs0 = 'ACPI\Processor'

Video Card Details v2

Garth — Thu, 18 Apr 2013 13:28:44 GMT

For full details please see

http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740

select distinct 
    R.Netbios_Name0 as 'Name',
    R.User_Name0 as 'User',
    CS.Manufacturer0 as 'Manufacturer', 
    CS.Model0 as 'Model',
    BIOS.SerialNumber0 as 'Serial',
    SD.Name0 as 'Processor', 
    RAM.TotalPhysicalMemory0 as 'Memory',
    VC.Name0 as 'Video Card', 
    VC.VCRam as 'Video Card RAM'
from  
    dbo.v_R_System R 
    inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId 
    inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID 
    inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId 
    inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId 
    left outer join (
        select     
            VC.ResourceID,
            VC.Name0,
            Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'VCRAM'
        from 
            dbo.v_GS_Video_Controller VC 
        Where
            VC.Name0 != 'ConfigMgr Remote Control Driver'
            and VC.AdapterRam0 is not null) VC on  R.ResourceID = VC.ResourceId 
where 
    SD.CompatibleIDs0 = 'ACPI\Processor'

Video Card details

Garth — Mon, 15 Apr 2013 11:36:56 GMT

For full details please see

http://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/c42ba8cf-02a5-4e2e-8d20-39b05a4a5740

select distinct 
    R.Netbios_Name0 as 'Name',
    R.User_Name0 as 'User',
    CS.Manufacturer0 as 'Manufacturer', 
    CS.Model0 as 'Model',
    BIOS.SerialNumber0 as 'Serial',
    SD.Name0 as 'Processor', 
    RAM.TotalPhysicalMemory0 as 'Memory',
    VC.Name0 as 'Video Card', 
    Convert(VarChar, VC.AdapterRam0 / 1024) + ' MB' as 'Video Card RAM'
from  
    dbo.v_R_System R 
    inner join dbo.v_GS_X86_PC_MEMORY RAM on RAM.ResourceID = R.ResourceId 
    inner join dbo.v_GS_COMPUTER_SYSTEM CS on CS.ResourceID = R.ResourceID 
    inner join dbo.v_GS_PC_BIOS BIOS on BIOS.ResourceID =R.ResourceId 
    inner join dbo.v_GS_SYSTEM_DEVICES SD on SD.ResourceID = R.ResourceId 
    inner join dbo.v_GS_Video_Controller VC on R.ResourceID = VC.ResourceID
where 
    SD.CompatibleIDs0 = 'ACPI\Processor' 
    and VC.Name0 != 'ConfigMgr Remote Control Driver'

Find all webcam - SQL

Garth — Tue, 02 Apr 2013 12:26:07 GMT

Use this query to find all webcam – SQL reports

select 
    CS.Name0, 
    SD.Name0,
    SD.DeviceID0
from 
    dbo.v_GS_COMPUTER_SYSTEM CS
    join dbo.v_GS_SYSTEM_DEVICES SD on CS.ResourceID = SD.ResourceID
Where 
    SD.Name0 like '%Webcam%'

Find all webcam – WQL

Garth — Tue, 02 Apr 2013 12:24:07 GMT

Use this WQL query to find all webcam

http://social.technet.microsoft.com/Forums/en-US/configmgrai/thread/fa5fcfe2-69ef-4482-adfc-de25d6d19669/?prof=required

select 
    SMS_R_System.Name, 
    SMS_G_System_SYSTEM_DEVICES.Name, 
    SMS_G_System_SYSTEM_DEVICES.DeviceID 
from  
    SMS_R_System 
    inner join SMS_G_System_SYSTEM_DEVICES on SMS_G_System_SYSTEM_DEVICES.ResourceID = SMS_R_System.ResourceId 
where 
    SMS_G_System_SYSTEM_DEVICES.Name like "%webcam%"

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

How to find PCs within an AD container

Garth — Sat, 16 Mar 2013 13:02:12 GMT

If you need to find all PC within an AD container (not an OU). Use this WQL query to find them.

For more details about this request, see the full forum post.
http://social.technet.microsoft.com/Forums/en-US/configmgrsetup/thread/9358cee7-75a5-4595-80a3-4016ea572f1b

select 
    SMS_R_System.Name, 
    SMS_R_System.LastLogonUserName 
from  
    SMS_R_System 
where 
    SMS_R_System.SystemContainerName = "ENHANSOFT\\COMPUTERS"

CM12 Collection prompt example

Garth — Fri, 15 Mar 2013 20:20:06 GMT

For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=238953&mpage=1#239186

SELECT distinct 
    sys1.Name0, 
    csys.Model0, 
    csys.Manufacturer0, 
    os.Caption0, 
    os.CSDVersion0, 
    BIOS.SerialNumber0, 
    sys1.description0,  
    REPLACE((SELECT IPAddress0 + ', ' FROM v_GS_NETWORK_ADAPTER_CONFIGUR WHERE resourceID = sys1.resourceID AND ipenabled0 = 1 FOR XML PATH('') )+'..',', ..','') AS [IP Addresses]
FROM 
    v_r_SYSTEM sys1 
    join v_GS_COMPUTER_SYSTEM csys on csys.resourceid=sys1.resourceid 
    join v_GS_OPERATING_SYSTEM os on os.resourceid=sys1.resourceid 
    join v_GS_PC_BIOS bios on bios.resourceid=sys1.resourceid 
    JOIN v_FullCollectionMembership fcm on fcm.resourceid=sys1.resourceid  
WHERE  
    fcm.Collectionid= 'SMS00001'
ORDER BY 
    sys1.Name0

MAC Address report

Garth — Thu, 28 Feb 2013 16:17:37 GMT

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

Garth — Mon, 11 Feb 2013 13:41:36 GMT

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

Garth — Fri, 08 Feb 2013 22:32:33 GMT

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

Garth — Thu, 07 Feb 2013 13:24:33 GMT

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

List Collection Membership

Garth — Tue, 22 Jan 2013 13:18:09 GMT

For full details, please see

http://www.windows-noob.com/forums/index.php?/topic/7308-collection-membership/#entry27677

select 
    C.Name
from 
    dbo.v_FullCollectionMembership FCM
    join dbo.v_Collection C on C.CollectionID = FCM.CollectionID
Where
    FCM.Name = 'Gartek-DC'

PC Serial Number by OU.

Garth — Thu, 17 Jan 2013 02:37:15 GMT

For full details, please see http://www.myitforum.com/forums/Active-Directory-Discovery-additional-Attributes-and-broken-reports-and-queries-m238462.aspx

select  distinct 
    R.ResourceID,
    R.Name0,
    R.Client0,
    BIOS.SerialNumber0
from 
    dbo.v_R_System AS R
    LEFT OUTER JOIN dbo.v_RA_System_SystemOUName AS OU ON R.ResourceID = OU.ResourceID
    INNER JOIN dbo.v_GS_PC_BIOS AS BIOS ON R.ResourceID = Bios.ResourceID 
where 
    OU.System_OU_Name0 = N'GARTEK.TST/DOMAIN CONTROLLERS'

WMI query for C: with more than 1GB of HD space.

Garth — Fri, 11 Jan 2013 13:39:36 GMT

For full details, please see

http://social.technet.microsoft.com/Forums/en-US/configmgrgeneral/thread/fde395bb-a4b6-4347-b1b6-8c180c3f7f46

This is a WMI WQL query and NOT and native CM07 / CM12 WQL query.

select 
    Size
from
    Win32_LogicalDisk
Where
    DeviceId="C:"
    and size > 1073741824

Finding all PCs without Symantec AV installed

Garth — Sun, 06 Jan 2013 17:04:45 GMT

For Full detail see

http://social.technet.microsoft.com/Forums/en-US/configmgradminconsole/thread/547016e0-9f15-4611-aac9-24f582aa0614

select 
    R.netbios_name0,
    R.user_name0,
    OS.Caption0 AS 'Operating System',
    ES.SerialNumber0 AS 'Serial Number' 
from 
    dbo.v_R_System R
    join dbo.v_GS_OPERATING_SYSTEM OS on OS.ResourceID = R.ResourceID
    JOIN dbo.v_GS_System_Enclosure ES on ES.ResourceID = R.ResourceID
where 
    R.ResourceID not in 
    (
        select distinct 
            ARP.ResourceId
        From 
            dbo.v_ADD_REMOVE_PROGRAMS ARP
            join dbo.v_GS_System S on ARP.ResourceID = S.ResourceId
        where 
            ARP.DisplayName0 in 
            (
                'Norton Antivirus Client',
                'Norton AntiVirus Corporate Edition', 
                'Symantec AntiVirus', 
                'Symantec AntiVirus Client',
                'Symantec AntiVirus Win64',
                'Symantec Endpoint Protection'
            )
        or S.SMSID0 in 
            (
                'GUID:6578610F-BFD0-4693-98B5-B6FB50129FB5',
                'GUID:E64FF1A8-EF5B-46DD-8859-02D7A3E8818A',
                'GUID:52EA60BD-F3DD-4615-9CCD-AFE0B7F28D41' 
            )
    )