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 !! ;)

PowerShell in Daily life

 Hello All,

This post will cover the small scripts that are used by the System Administrators/SCCM Admins/Desktop Administrator in their daily life. I am not a PowerShell expert and have just started learning it. This post is also a kind of repository/reference point for my work. 

Folder Existence and File copy script

Below script achieves the following:

• Check for the existence of a folder at the provided directory path.
• If the folder is found - required files are copied to it from the source location (in this example source location is the local folder where the script is running from). We can also add the -Path parameter and provide the actual source location of the file to be copied from.
• If the folder is not found, the script will create a folder and then copy the file to that folder.

Script: 

$Folder= 'C:\Users\Default\AppData\Local\Microsoft\Windows\WSUS'#Path of  'WSUS' folder - the folder that we are looking for.#

$Con= 'C:\Users\Default\AppData\Local\Microsoft\Windows' #Directory, in which the 'WSUS' folder will be created#

If (Test-Path -path $Folder) {

 copy-item "ABC.ini" -Destination $folder #ABC.ini is the example file - can be replaced by any file. -Path parameter can also be used if file not present in the local directory.#

} else {

New-Item -Path $Con -Name "WSUS" -ItemType "Directory"

copy-item "ABC.ini" -Destination $folder

}

    

         

Before script execution

After script execution