SQL Queries for SCCM Admins

Creating SQL queries for pulling out data from the SCCM Database is the integral part of day to day job of a SCCM Admin. Most of the time the requirements are pretty much straight forward and you can simply join few tables using the common columns and you are done, however not every query creation task end up in day. Sometimes the ask seems to be easy but the query creation needs little extra efforts to pull the desired data in the desired format.

I will use this post to publish the simple to complex SQL queries that I created while working on different requirements from my different customers.

SQL Query for Software Usage

This query was created as a result of some unique requirement. Requirement was only to see the most recent software usage record for a metered software on a system for a unique combination of user and computer.

There are some standard queries that could fit the bill but they provide multiple entries for the same user and computer combination.

Example:

SELECT

S.name0 'Computer Name',

MEU.UserName 'Logged-in User',

MEU.MeteredUserID,

FN.full_user_name0 'User Name',

SF.FileName 'Metered File Name',

SF.FileVersion,

MAX(MUS.LastUsage) as 'Last Usage Date',

DATEDIFF(day,MAX(MUS.LastUsage),Current_Timestamp) 'Days since last used'

FROM v_MeteredUser MEU Inner Join v_MonthlyUsageSummary MUS ON MEU.MeteredUserID = MUS.MeteredUserID

INNER JOIN v_GS_SoftwareFile SF ON MUS.FileID = SF.FileID

INNER JOIN v_r_system S on meu.username=S.user_name0

INNER JOIN v_r_user FN on meu.username=FN.user_name0

where sf.filename in ('Photoshop.exe')

and sf.FileVersion like '13%'

and FN.Full_User_Name0 like '%Gabriel%'

group by MUS.LastUsage, S.name0, MEU.UserName, FN.full_user_name0, SF.FileName, SF.FileVersion,  SF.FileID, MEU.MeteredUserID

HAVING (DATEDIFF(day, MAX(MUS.LastUsage), Current_TimeStamp) <60)

ORDER BY MUS.LastUsage

Output of the above query shows the multiple records for the same user and same computer combination for a single metered file.

The query that could meet the requirements is given below:

select Distinct V_R_System.Name0 'Computer Name',v_monthly usagesummary.ResourceID, v_MeteredUser.FullName,
V_MeteredFiles.FileName, v_MonthlyUsageSummary.UsageCount, v_MonthlyUsageSummary.UsageTime, v_MonthlyUsageSummary.LastUsage,v_MonthlyUsageSummary.MeteredUserID
from v_MonthlyUsageSummary, V_R_System, V_MeteredFiles, V_MeteredUser,
(select MUS.MeteredUserID, MAX (MUS.LastUsage) 'Last Usage'
from v_MonthlyUsageSummary MUS Group by MUS.MeteredUserID) M1
where v_MonthlyUsageSummary.MeteredUserID=M1.MeteredUserID and v_MonthlyUsageSummary.LastUsage=M1.[Last Usage]
and V_R_System.ResourceID=v_MonthlyUsageSummary.ResourceID
and V_MeteredFiles.MeteredFileID=v_MonthlyUsageSummary.FileID
and v_MonthlyUsageSummary.MeteredUserID=v_MeteredUser.MeteredUserID
and v_MeteredFiles.FileName = 'WINWORD.EXE'

Order by v_MonthlyUsageSummary.MeteredUserID, v_MeteredUser.FullName

Output of the above query look like this:

Second query gives the unique results (only the latest usage instance of the metered software) for the same user on different systems.

Hope the above query can help someone who might fall into the same situation.

Happy Querying !! ;)