Garth Jones

Count of PCs by OU v2

Garth — Wed, 19 Mar 2014 20:15:39 GMT

For more details please see: http://social.technet.microsoft.com/Forums/systemcenter/en-US/aea60e7b-a88b-48d8-8cc3-8675097abe62/trying-to-do-a-report-of-sup-failures-by-ou?forum=configmgrinventory

SELECT
    OU.ou,
    COUNT(*) AS 'COUNT'
FROM 
    dbo.v_R_System SYS 
    join (
            select 
                SOU.ResourceID, 
                Max(SOU.System_OU_Name0) as 'ou' 
            from 
                dbo.v_RA_System_SystemOUName SOU
            Group by 
                    SOU.ResourceID) OU on SYS.ResourceID = OU.ResourceID

/*WHERE SYS.Netbios_Name0 IN ( 'PHO-PVR63186',
'WCO-PVP11579',
'PAL-PVI82336',
'LOM-PVI46044',
'LOM-PVI49102',
'LOM-PVR46148',
'PUG-PVI92643',
'RED-PVI54875',
'REN-PVR27553',
'WLA-PVR125834')*/
Group by 
    OU.ou
ORDER BY
    OU.ou

Count of PCs by OU

Garth — Wed, 19 Mar 2014 18:32:22 GMT

Please see post http://social.technet.microsoft.com/Forums/systemcenter/en-US/aea60e7b-a88b-48d8-8cc3-8675097abe62/trying-to-do-a-report-of-sup-failures-by-ou?forum=configmgrinventory for full details

SELECT
    Max(SOU.System_OU_Name0),
    COUNT(*) AS 'COUNT'
FROM 
    dbo.v_RA_System_SystemOUName SOU 
    JOIN dbo.v_R_System SYS ON SYS.ResourceID = SOU.ResourceID

/*WHERE SYS.Netbios_Name0 IN ( 'PHO-PVR63186',
'WCO-PVP11579',
'PAL-PVI82336',
'LOM-PVI46044',
'LOM-PVI49102',
'LOM-PVR46148',
'PUG-PVI92643',
'RED-PVI54875',
'REN-PVR27553',
'WLA-PVR125834')*/
Group by 
    SOU.System_OU_Name0
ORDER BY
    SOU.System_OU_Name0

PCs with Either of two applications installed.

Garth — Fri, 24 Jan 2014 19:09:34 GMT

Use this query to determine if a PC has either or both applications installed.

For more details, please see

http://social.technet.microsoft.com/Forums/systemcenter/en-US/6c4650c7-3246-4e13-a78d-05691c13c89d/duplicate-rows-when-finding-pcs-with-installed-software?forum=configmgrreporting

SELECT DISTINCT 
    R.Netbios_Name0, 
    R.User_Domain0+'\'+    R.User_Name0 as 'User Name', 
    OS.Caption0 AS 'Operating System Caption', 
    CASE isnull(WIR.ResourceID,0 ) 
        When 0 THEN ' ' 
        ELSE '*' 
    END AS 'Application1', 
    CASE isnull(MIR.ResourceID,0 ) 
        When 0 THEN ' ' 
        ELSE '*' 
    END AS 'Application2'
 FROM    
    dbo.v_R_System_Valid AS R 
    INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID 
    Left outer join (Select ARP.ResourceID as 'ResourceID' from dbo.v_Add_Remove_Programs ARP Where arp.DisplayName0 like '%Warranty Information Reporting%') as WIR ON R.ResourceID = WIR.ResourceID
    Left outer join (Select ARP.ResourceID as 'ResourceID' from dbo.v_Add_Remove_Programs ARP Where arp.DisplayName0 like '%Monitor Information Reporting%') as MIR ON R.ResourceID = MIR.ResourceID
 ORDER BY 
    R.Netbios_Name0

Limiting a report to a Collection

Garth — Mon, 13 Jan 2014 18:14:13 GMT

For more details please see

http://www.windows-noob.com/forums/index.php?/topic/9766-sccm-report-on-collection/

SELECT DISTINCT
    CS.Name0, 
    R.User_Name0, 
    MAX(SOU.System_OU_Name0) AS Expr1,                      
    CS.Description0, 
    CS.Manufacturer0, 
    CS.Model0,
    BIOS.SerialNumber0
FROM  
    dbo.v_Collection Coll
    join dbo.v_FullCollectionMembership FCM on Coll.CollectionID = FCM.CollectionID and Coll.Name = 'All systems'
    join dbo.v_GS_COMPUTER_SYSTEM CS on FCM.ResourceID = CS.ResourceID
    INNER JOIN dbo.v_R_System R ON CS.ResourceID = R.ResourceID 
    INNER JOIN dbo.v_RA_System_SystemOUName SOU ON CS.ResourceID = SOU.ResourceID 
    INNER JOIN dbo.v_GS_PC_BIOS BIOS ON CS.ResourceID = BIOS.ResourceID
GROUP BY 
    CS.Name0, 
    R.User_Name0, 
    CS.Description0,
    CS.Manufacturer0, 
    CS.Model0, 
    BIOS.SerialNumber0

Report to show major Internet Explorer version

Garth — Sat, 04 Jan 2014 00:42:05 GMT

For more details see.

http://social.technet.microsoft.com/Forums/systemcenter/en-US/14bb9caa-5f0b-418e-88b5-e359ef6d116b/report-to-show-major-internet-explorer-version?forum=configmgrreporting

select 
    SF.FileName,
    OS.Caption0,
    replace(left(SF.FileVersion,2), '.','') as 'IE Version',
    Count (Distinct SF.ResourceID) as 'Total'
From 
    dbo.v_GS_SoftwareFile SF 
    JOIN v_FullCollectionMembership fcm on SF.ResourceID=fcm.ResourceID
    JOIN dbo.v_GS_OPERATING_SYSTEM OS ON SF.ResourceID = OS.ResourceID
    join dbo.v_GS_SYSTEM S on SF.ResourceID = S.ResourceID
Where 
    SF.FileName = 'iexplore.exe' 
    and SF.FilePath like '%Internet Explorer%'
    and S.SystemRole0 = 'Workstation'
Group by 
    SF.FileName, 
    OS.Caption0,
    replace(left(SF.FileVersion,2), '.','')
Order by 
    2

Count of Local Printers

Garth — Sat, 26 Oct 2013 14:45:54 GMT

For more details, please see http://www.myitforum.com/Forums/tm.aspx?high=&m=240764&mpage=1#240768

select Distinct
    PD.Name0,
    count(*)
from
    dbo.v_GS_PRINTER_DEVICE PD
Group by
    PD.Name0
Order by
    PD.Name0

Count of PC within each OU

Garth — Mon, 16 Sep 2013 14:55:33 GMT

For more detail about this query, please see

http://www.windows-noob.com/forums/index.php?/topic/9069-report-count-of-computers-in-certain-organizational-units/

select
    ou.ou,
    count(*) as 'total'
from
    (SELECT sys.ResourceID, max(OU.System_OU_Name0) AS 'OU' FROM dbo.v_R_System AS sys INNER JOIN dbo.v_RA_System_SystemOUName AS ou ON sys.ResourceID = ou.ResourceID GROUP BY sys.ResourceID) ou
group by 
    ou.ou
Order by 
    ou.ou

Find all PC with HW and SW inventory dates greater than 180 days

Garth — Mon, 26 Aug 2013 14:11:03 GMT

This WQL query will show you all PCs with a HW and SW Scan date of greater than 180 days.

select 
    SMS_R_System.Name, 
    SMS_R_System.LastLogonUserDomain, 
    SMS_R_System.LastLogonUserName
from  
    SMS_R_System 
    inner join SMS_G_System_SYSTEM on SMS_G_System_SYSTEM.ResourceId = SMS_R_System.ResourceId 
    inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId 
    inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId 
    inner join SMS_G_System_LastSoftwareScan on SMS_G_System_LastSoftwareScan.ResourceId = SMS_R_System.ResourceId 
where 
    SMS_G_System_SYSTEM.SystemRole = "Workstation" 
    and SMS_G_System_COMPUTER_SYSTEM.Model != "%Virtual%" 
    and Datediff(DD,SMS_G_System_WORKSTATION_STATUS.LastHardwareScan, Getdate()) > 180 
    and Datediff(DD,SMS_G_System_LastSoftwareScan.LastScanDate, Getdate()) > 180

Finding all PCs with MS Access using ARP data

Garth — Tue, 30 Jul 2013 12:26:03 GMT

Use this query to find all PCs with MS Access installed using ARP data.

Select Distinct 
    sys.Netbios_Name0, 
    AD_Site_Name0,  
    sys.User_Domain0, 
    sys.User_Name0, 
    sys.Operating_System_Name_and0, 
    arp.DisplayName0, 
    arp64.DisplayName0, 
    arp.Version0,
    arp64.Version0 
FROM 
    v_R_System sys
    JOIN v_GS_ADD_REMOVE_PROGRAMS arp ON sys.ResourceID = arp.ResourceID 
    left outer JOIN v_GS_ADD_REMOVE_PROGRAMS_64 arp64 ON sys.ResourceID = arp64.ResourceID 
    JOIN v_FullCollectionMembership fcm on sys.ResourceID = fcm.ResourceID
WHERE 
    (arp.DisplayName0 like '%Microsoft Office Access MUI%' 
    or arp64.DisplayName0 like '%Microsoft Office Access MUI%')
    and fcm.CollectionID = 'SMS00001'

Finding all PCs with MSAcess.exe in the All Systems collection

Garth — Tue, 30 Jul 2013 12:24:24 GMT

Use this query to find all PCs with MS Access in the All systems collection.

select 
    SMS_R_System.ResourceID,
    SMS_R_System.ResourceType,
    SMS_R_System.Name,
    SMS_R_System.SMSUniqueIdentifier,
    SMS_R_System.ResourceDomainORWorkgroup,
    SMS_R_System.Client 
from 
    SMS_R_System 
    inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId 
    inner join SMS_FullCollectionMembership on SMS_FullCollectionMembership.ResourceID = SMS_R_System.ResourceId 
where 
    SMS_G_System_SoftwareFile.FileName = "msaccess.exe" 
    and SMS_FullCollectionMembership.CollectionID = 'SMS00001'

Joining Users to a PC

Garth — Sat, 06 Jul 2013 15:15:09 GMT

Use this query to join Users to PCs

Select 
    R.Name0,
    U.User_Name0, 
    U.Full_User_Name0, 
    U.Unique_User_Name0,
    U.Mail0
From 
    dbo.v_R_System R 
    INNER JOIN dbo.v_R_User U ON U.Unique_User_Name0 = R.User_Domain0 + '\'+ R.User_Name0

.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; }

Find PC by Last logon user – prompted

Garth — Sat, 29 Jun 2013 18:03:58 GMT

Use this query if you want to locate PCs by that last logon user but prompt for the user name each time.

select 
    SMS_R_System.Name 
from  
    SMS_R_System 
where 
    SMS_R_System.LastLogonUserName = ##PRM:SMS_R_System.LastLogonUserName## 
order by 
    SMS_R_System.Name

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'