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
Category: Uncategorized
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
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
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 […]
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 […]