Category: Uncategorized

Hostname History

This query will display computers who host names changes. SelectCS.Name0,HCS.Name0,HCS.TimeStampfromdbo.v_GS_COMPUTER_SYSTEM CSjoin dbo.v_HS_COMPUTER_SYSTEM HCS on Cs.ResourceID = HCS.ResourceIDWhereCS.Name0 <> HCS.Name0

Find computers with no active users.

This query will find all computers in SCCM that have had not user logon within the last 30 days. declare @Now as datetimeset @Now = getdate() ;with MINLCU as(SelectSCU.ResourceID as ‘ResourceID’,min(SCU.LastConsoleUse0) as ‘LastConsoleUse0’Fromdbo.v_GS_SYSTEM_CONSOLE_USER SCUGroup bySCU.ResourceID) selectRV.Netbios_Name0,SCU.SystemConsoleUser0,SCU.LastConsoleUse0,SCU.NumberOfConsoleLogons0fromdbo.v_R_System_Valid RVJoin dbo.v_GS_SYSTEM_CONSOLE_USER SCU on RV.ResourceID = SCU.ResourceIDjoin MINLCU on RV.ResourceID = MINLCU.ResourceIDWheredatediff(dd,MINLCU.LastConsoleUse0, @Now) >30order byRV.Netbios_Name0,SCU.LastConsoleUse0 DESC

Count of Models

Here is a query query for count of computer Models. ;with m as (SELECTCS.Manufacturer0 as ‘Manufacturer’,case CS.Manufacturer0When ‘LENOVO’ then CSP.Version0else CS.Model0end as ‘Model’FROMdbo.v_R_System_Valid RVjoin dbo.v_GS_COMPUTER_SYSTEM CS on RV.ResourceID = CS.ResourceIDjoin dbo.v_GS_COMPUTER_SYSTEM_PRODUCT CSP on RV.ResourceID = CSP.ResourceID)SelectM.Manufacturer,M.Model,Count(*) as ‘total’from MGroup byM.Manufacturer,M.ModelOrder byM.Manufacturer,M.Model

Limiting a report to a Collection

For more details please see     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 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 […]