Author: Garth Jones

Count of PCs by OU v2

  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' ...

Count of PCs by OU v2

  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' ...

Count of PCs by OU

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

Count of PCs by OU

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

PCs with Either of two applications installed.

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', ...

Limiting a report to a Collection

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 FC...

Report to show major Internet Explorer version

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_FullColl...

Count of Local Printers

For more details, please see https://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 Local Printers

For more details, please see https://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

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.Resource...

Count of PC within each OU

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.ResourceI...

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

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.Resour...