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 […]
Tag: CM07
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 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 […]
SELECT DISTINCT CS.Name0, CASE WHEN ES.ChassisTypes0 in (‘3′,’4′,’6′,’7′,’15’) THEN ‘Desktop’ WHEN ES.ChassisTypes0 in (‘8′,’9′,’10’,’21’) THEN ‘Laptop’ Else ‘Unknown’ END as ‘Chassis’, BIOS.SerialNumber0, CS.Manufacturer0, CS.Model0, OS.Caption0, OS.CSDVersion0, SCUM.TopConsoleUser0, R.User_Name0 AS ‘Last Logged’, BIOS.ReleaseDate0, NAC.IPAddress0, NAC.DefaultIPGateway0, RAM.TotalPhysicalMemory0, LD.DeviceID0, LD.Size0 FROM dbo.v_R_System R INNER JOIN dbo.v_GS_PC_BIOS BIOS ON BIOS.ResourceID = R.ResourceID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM CS ON R.ResourceID […]
For full details, please see http://www.windows-noob.com/forums/index.php?/topic/7308-collection-membership/#entry27677 select C.Name from dbo.v_FullCollectionMembership FCM join dbo.v_Collection C on C.CollectionID = FCM.CollectionID Where FCM.Name = ‘Gartek-DC’