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://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 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 […]
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’
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 […]
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
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%’
For full details see: http://www.myitforum.com/forums/tm.aspx?high=&m=238953&mpage=1#239186 SELECT distinct sys1.Name0, csys.Model0, csys.Manufacturer0, os.Caption0, os.CSDVersion0, BIOS.SerialNumber0, sys1.description0, REPLACE((SELECT IPAddress0 + ‘, ‘ FROM v_GS_NETWORK_ADAPTER_CONFIGUR WHERE resourceID = sys1.resourceID AND ipenabled0 = 1 FOR XML PATH(”) )+’..’,’, ..’,”) AS [IP Addresses] FROM v_r_SYSTEM sys1 join v_GS_COMPUTER_SYSTEM csys on csys.resourceid=sys1.resourceid join v_GS_OPERATING_SYSTEM os on os.resourceid=sys1.resourceid join v_GS_PC_BIOS bios on bios.resourceid=sys1.resourceid […]