SQL Query : All Win10 Machines with User Details / OS Ver / OS LangID

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 More

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 More

SQL 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 More

SQL 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 More

SQL 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 More

SQL 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 More

SQL 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 More

SQL 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