Count of PCs by OU v2
Wednesday, March 19, 2014 4:15 PM

 

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
Wednesday, March 19, 2014 2:32 PM

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.
Friday, January 24, 2014 2:09 PM

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
Monday, January 13, 2014 1:14 PM

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
Friday, January 03, 2014 7:42 PM

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 PC within each OU
Monday, September 16, 2013 10:55 AM

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
Monday, August 26, 2013 10:11 AM

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
by Garth Jones
Filed under: , , , , , ,
Finding all PCs with MS Access using ARP data
Tuesday, July 30, 2013 8:26 AM

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
Tuesday, July 30, 2013 8:24 AM

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
Saturday, July 06, 2013 11:15 AM

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
Saturday, June 29, 2013 2:03 PM

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
by Garth Jones
Filed under: , , , , ,
Video card with Sound card.
Thursday, April 18, 2013 2:50 PM

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
Thursday, April 18, 2013 9:28 AM

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
Monday, April 15, 2013 7:36 AM

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
Tuesday, April 02, 2013 8:26 AM

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%'
More Posts Next page »

This Blog

Archives

Syndication