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

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’

Joining Users to a PC

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

CM12 Collection prompt example

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