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

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.

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 ‘ ‘ […]

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 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 […]

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_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 […]

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.ResourceID) ou group by ou.ou Order by ou.ou

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.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 […]

Finding all PCs with MS Access using ARP data

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 […]

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

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’