Use this query to list all Windows 10 devices including the user, the OS version and the OS language ID. SELECT DISTINCT dbo.v_R_System.Name0 AS MachineName, dbo.v_R_System.User_Domain0 AS UserDomain, dbo.v_R_System.User_Name0 AS UserName, dbo.vSMS_R_System.operatingSystemVersion0 AS OSVersion, dbo.v_GS_OPERATING_SYSTEM.OSLanguage0 FROM dbo.v_R_System INNER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_OPERATING_SYSTEM on dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.vSMS_R_System ON dbo.v_R_System.Name0 = dbo.vSMS_R_System.Name0 WHERE vSMS_R_System.Operating_System_Name_and0 like ‘%Workstation 10%’ order by v_R_System.Name0 Sample Output
Read MoreTag: SQL Query
SQL Query : All Computers Missing a Specific KB Update
If you identify a software update which has been applied to a limited number of your devices, this query can be used to identify those where the KB is missing. select sys.name0 [Computername], osd.OSLanguage00 [OS Language], osd.Caption00 [OS Name] from v_updateinfo ui inner join v_UpdateComplianceStatus ucs on ucs.ci_id=ui.ci_id join v_CICategories_All catall2 on catall2.CI_ID=UCS.CI_ID join v_CategoryInfo catinfo2 on catall2.CategoryInstance_UniqueID = catinfo2.CategoryInstance_UniqueID and catinfo2.CategoryTypeName=’UpdateClassification’ join v_R_System sys on sys.resourceid=ucs.resourceid join Operating_System_DATA osd on sys.ResourceID = osd.MachineID and ucs.status=’2′ — required AND ui.articleid=’3012973′ order by sys.name0 Sample Output
Read MoreSQL Query : Systems With Hardware Scan > 60 Days ago
You can identify devices which have not checked in for some time, or those with potentially faulty SCCM clients using this query. SELECT a.ResourceID, a.Netbios_name0, b.LastHWScan FROM v_R_System a INNER JOIN v_GS_WORKSTATION_STATUS b ON a.ResourceID = b.ResourceID WHERE b.LastHWScan <= Dateadd(day, -60, getdate()) order by LastHWScan desc Sample Output
Read MoreSQL Query : For Summarizing Software Metering Usage
The first query brings back separate rows for each line in V_MonthlyUsageSummary view. Second query consolidates it to one row and totals some of the fields. /****** Software Metering – Multiple Rows ******/ SELECT MRT.ProductName, VRS.Name0, MUR.FullName, MFS.MeteredFileName, MUS.UsageCount, MUS.UsageTime, MUS.LastUsage, MUS.TimeKey AS FirstUsed FROM V_MonthlyUsageSummary MUS JOIN v_R_System VRS ON MUS.ResourceID = VRS.ResourceID JOIN v_MeteredUser MUR ON MUS.MeteredUserID = MUR.MeteredUserID JOIN v_MeteredFiles MFS ON MUS.FileID = MFS.MeteredFileID JOIN MeterRules MRT ON MRT.RuleID = MFS.RuleID WHERE MRT.ProductName = ‘metering rule name’ And (VRS.operatingSystem0 like ‘Windows 7%’ OR VRS.operatingSystem0 like ‘Windows…
Read MoreSQL Query : Find Computers with Torrent Software Installed
Query returns all devices with torrent software installed. Please comment to share others. Please note this query takes some time to run on a large client estate. Select SD.Name0, SF.FileName From v_Gs_SoftwareFile SF Join v_R_System SD on SD.ResourceId = SF.ResourceId Where SF.FileName Like ‘%Azureus%’ Or SF.FileName Like ‘%BitComet%’ Or SF.FileName Like ‘%BitLord%’ Or SF.FileName Like ‘%BitPump%’ Or SF.FileName Like ‘%BitTornado%’ Or SF.FileName Like ‘%BitTorrent%’ Or SF.FileName Like ‘%Shareaza%’ Or SF.FileName Like ‘%Utorrent%’ Order By SD.Name0, SF.FileName Sample Output
Read MoreSQL Query : List all Windows Store Apps
The following query returns all Windows Store Apps, but you could also configure it to return all machines complete with all Store Apps. This will return lots of data for each device though. SELECT distinct A.ApplicationName0 FROM v_GS_WINDOWS8_APPLICATION A JOIN v_GS_WINDOWS8_APPLICATION_USER_INFO AU ON A.FullName0 = AU.FullName0 JOIN v_R_System RS ON A.ResourceID = RS.ResourceID order by ApplicationName0 SELECT distinct RS.Name0, A.ApplicationName0 FROM v_GS_WINDOWS8_APPLICATION A JOIN v_GS_WINDOWS8_APPLICATION_USER_INFO AU ON A.FullName0 = AU.FullName0 JOIN v_R_System RS ON A.ResourceID = RS.ResourceID order by Name0 Sample Output
Read MoreSQL Query : All Active PCs
This query returns all active PCs based on the fact the device has received policy in the last 31 days (or configured as you wish). You can also configure the collection ID – in this case it is querying all machines in the Global Clients custom collection. SELECT dbo.v_R_System.Name0, dbo.v_R_System.User_Name0, dbo.v_GS_OPERATING_SYSTEM.Caption0, dbo.v_CH_ClientSummary.LastPolicyRequest FROM dbo.v_R_System INNER JOIN dbo.v_CH_ClientSummary ON dbo.v_R_System.ResourceID = dbo.v_CH_ClientSummary.ResourceID INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_FullCollectionMembership.ResourceID INNER JOIN dbo.v_GS_PC_BIOS ON dbo.v_FullCollectionMembership.ResourceID…
Read MoreSQL Query : All Packages & Programs & Sources (not applications)
This query is designed specifically for Packages rather than Applications. SELECT Program.PackageID, Package.Name ‘Package Name’, Program.ProgramName ‘Program Name’, Program.CommandLine, Program.Comment, Program.Description, Package.PkgSourcePath FROM [v_Program] as Program LEFT JOIN v_Package as Package on Package.PackageID = Program.PackageID Order by Program.PackageID Sample Output
Read More