I was asked in a tweet to post my SQL query that I used within Getting Started with Power BI Desktop and SCCM. Since it didn’t make sense to post it to the Enhansoft Blog. I thought that I would post it on SCCMUG.ca site. It took a bit longer to get this out as […]
Category: Uncategorized
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
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
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 ‘ ‘ […]
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 […]
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 […]
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
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
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 […]
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 […]
Recent Comments