SQL Query : Computer name with user & last logged on date etc.

This query links a number of tables and views to return extensive inventory information about your clients.   SELECT dbo.v_R_System.Name0, dbo.v_R_System.User_Name0, dbo.v_R_System.AD_Site_Name0, dbo.v_R_System.Client_Version0, dbo.v_GS_OPERATING_SYSTEM.Caption0, dbo.v_CH_ClientSummary.LastOnline, dbo.v_CH_ClientSummary.LastMPServerName, dbo.v_CH_ClientSummary.LastDDR, dbo.v_CH_ClientSummary.LastHW, dbo.v_CH_ClientSummary.LastSW, dbo.v_CH_ClientSummary.LastStatusMessage, dbo.v_CH_ClientSummary.LastPolicyRequest, dbo.v_CH_ClientSummary.IsActiveDDR, dbo.v_CH_ClientSummary.IsActiveSW, dbo.v_CH_ClientSummary.IsActivePolicyRequest, dbo.v_CH_ClientSummary.IsActiveStatusMessages, dbo.v_CH_ClientSummary.LastEvaluationHealthy, dbo.v_CH_ClientSummary.ClientRemediationSuccess, dbo.v_CH_ClientSummary.LastActiveTime, dbo.v_CH_ClientSummary.ClientActiveStatus, dbo.v_CH_ClientSummary.ClientStateDescription, dbo.v_CH_ClientSummary.IsActiveHW, dbo.v_FullCollectionMembership.CollectionID, dbo.v_GS_PC_BIOS.SerialNumber0 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 = dbo.v_GS_PC_BIOS.ResourceID WHERE v_FullCollectionMembership.CollectionID =’GS200038′ and DATEDIFF(day,dbo.v_CH_ClientSummary.LastPolicyRequest,GetDate())<31 ORDER BY v_R_System.Name0 Sample Output

Read More

SQL Query : All Package Statuses for a specific DP

This query will allow you to report on the installation status of all packages on a specified Distribution Point. SELECT Name,     Description,     v_DistributionPoint.LastRefreshTime,     Manufacturer,     Version,     Language,     v_Package.SourceSite,     v_Package.PackageID,     case when v_DistributionPoint.IsPeerDP=1 then ‘*’ else ” end as BranchDP FROM v_Package INNER JOIN v_DistributionPoint ON v_Package.PackageID = v_DistributionPoint.PackageID WHERE v_DistributionPoint.ServerNALPath LIKE ‘%BLRSCCM10001%’ Order by Name Sample Output

Read More

SQL Query : Distribution Status of a Specific Package

This query will return all DPs to where the package has been pushed. Select SUBSTRING(dp.ServerNALPath, CHARINDEX(‘\\’, dp.ServerNALPath) + 2, CHARINDEX(‘”]’, dp.ServerNALPath) – CHARINDEX(‘\\’, dp.ServerNALPath) – 3 ) AS ServerName, dp.SiteCode, stat.SourceVersion, pstat.UpdateTime, stat.InstallStatus, dp.PackageID, case when dp.IsPeerDP=1 then ‘*’ else ” end as BranchDP FROM v_DistributionPoint dp left join v_PackageStatusDistPointsSumm stat on dp.ServerNALPath=stat.ServerNALPath and dp.PackageID=stat.PackageID left join v_PackageStatus pstat on dp.ServerNALPath=pstat.PkgServer and dp.PackageID=pstat.PackageID WHERE dp.PackageID=’XX10001′ Sample Output  

Read More

SQL Query – All Systems with Make and Model Name (per Lenovo)

As we all  know, Lenovo have to be different.  When we run a query for Make / Model any of our Lenovo devices return a manufacturer specific model number rather than the model we would like to see like ThinkPad 470S.  This query returns the Version00 field which is where the actual model name is stored. SELECT GCS.Name0 ,GCS.ResourceID ,GCS.Manufacturer0 ,GCS.Model0 ,CSP.Version00 FROM v_GS_COMPUTER_SYSTEM GCS JOIN COMPUTER_SYSTEM_PRODUCT_DATA CSP on GCS.ResourceID = CSP.MachineID WHERE Manufacturer0 = ‘Lenovo’ Order by Name0 Sample Output

Read More

SQL Query : All Systems with Make, Model, OS

Fairly straightforward query.  You can expand this to use as many columns from the v_R_System view as you need. SELECT DISTINCT dbo.v_R_System.Name0 AS MachineName, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS Make, dbo.v_GS_COMPUTER_SYSTEM.Model0 AS Model, dbo.vSMS_R_System.Operating_System_Name_and0 AS OS 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.vSMS_R_System ON dbo.v_R_System.Name0 = dbo.vSMS_R_System.Name0 order by Name0 Sample Output

Read More

SQL Query : Find .exe in a particular file path

This query will return all machines where an exe is found in a specific file path.  The advantage of using this is so that you don’t return all machines simply containing the file.  This (should) mean the software is actually installed.  select computer_system_data.name00, computer_system_data.username00, SoftwareFile.filename, SoftwareFile.FileVersion, softwarefilepath.filepath FROM computer_system_data, SoftwareFile, softwareinventory, workstationstatus_data, softwarefilepath WHERE computer_system_data.machineId = softwareinventory.clientid and softwareinventory.fileid = softwarefile.fileid and softwareinventory.filepathid = softwarefilepath.filepathid and SoftwareFile.filename = ‘outlook.exe’ and computer_system_data.machineid = workstationstatus_data.machineId and softwarefilepath.filepath like ‘C:\PROGRAM FILES\MICROSOFT OFFICE 15\root\office15\’ Order by name00 Sample Output

Read More

SQL Query : User Device Affinity Report

The first query writes to a temporary dataset then the second query is used to create a pivot table.  The only limitation is you have to specify the number of ‘user’ columns you want to display per machine.  The following example displays 5 users per device.  The Sample Output is obscured for client confidentiality. SELECT UMS.MachineResourceName, UMS.UniqueUserName INTO #TempTable FROM v_UserMachineRelationship UMS join v_UserMachineRelation UMR on UMS.RelationshipResourceID = UMR.RelationshipResourceID join v_R_System VRS on UMR.MachineResourceID = VRS.ResourceID WHERE VRS.Operating_System_Name_and0 like ‘%Workstation%’ SELECT * FROM (SELECT ‘User’+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [MachineResourceName] ORDER…

Read More