Written By Craig Wall
BUILDING A SQL QUERY REPORT, IN STEPS
SCCM has a lot of built-in reports, but sometimes you need some info that isn’t quite covered in any of them.
Let’s say you have users in SCCM with multiple primary devices — and you want to know which users do NOT have Zoom installed on ANY of their devices.
A Built-In report will let you know which devices are missing Zoom, but that doesn’t list their associated primary user.
What I’m Using For This Walkthrough
- SQL Server Management Studio
- Microsoft Report Builder as an alternative
I didn’t use the SCCM console because I wanted to teach myself SQL queries. The console uses WQL instead.
BUILDING A QUERY – STEP 1: Return Which Devices Have Zoom Installed
Show which devices have Zoom installed.
The solutions I Googled for this query looked a little complicated. So to teach myself what goes into these queries, I broke the solution down into much smaller chunks that I could build upon.
The Database Views We Need:
SQL Server Views in Configuration Manager (Microsoft Docs)
- v_GS_ADD_REMOVE_PROGRAMS – Under the Asset Intelligence views, this returns programs installed in Control Panel. Perfect.
In SQL Server Management Studio, right-click on the View that matches this name, right click it, and Select Top 1000 rows. This quickly shows you what columns are in the view and what info you can get from it.
This view is based on a “ResourceID” that’s a numeric value. If you run the same “Select Top 1000 rows” on the v_R_System_Valid view, we see the same list of ResourceIDs — indicating that these numeric values are IDs of computers in SCCM.
This view also has a DisplayName0 column that lists the associated software title, which we want.
We want to grab these two columns out of v_GS_ADD_REMOVE_PROGRAMS and display them.
The query ends up looking like this:
SELECT CS.ResourceID as 'ComputerID', CS.DisplayName0 as 'Software Title', FROM v_GS_ADD_REMOVE_PROGRAMS as CS WHERE (CS.DisplayName0 = 'Zoom')
I could have left that part out of the SELECT statement and just returned the ComputerID.
Step 2: Translate the Computer ID’s to Machine Names
Show the computer names of computers with Zoom installed.
For this, we’ll need to pull in another view — one that can use the ResourceID from v_GS_ADD_REMOVE_PROGRAMS and give us back the name for the computer it’s tied to.
We can use v_R_System_Valid since that view has a Netbios_Name0 column, which lists the computer name.
To pull in another view, we need to INNER JOIN the v_R_System_Valid view into our original view. Since we want our results to show the computer name and not the computer ID, we need to select the Netbios_Name0 column instead.
This results in the below query:
SELECT DISTINCT OS.Netbios_Name0 as 'ComputerID', CS.DisplayName0 as 'Software Title' FROM v_GS_ADD_REMOVE_PROGRAMS as CS INNER JOIN v_R_System_Valid as OS on CS.ResourceID = OS.ResourceID WHERE (CS.DisplayName0 = 'Zoom')
Note that each view listed in FROM are joined as separate variables — one as CS, one as OS. Since v_R_System_Valid is joined as “OS”, the SELECT statement needs to specify OS.Netbios_Name0.
The modified result will show computer names instead of computer ID numbers, of machines with Zoom installed:
(instead of leaving my test installation’s PC names in there, I copied modified output to Excel to show what you can expect it to look like)
Also, as noted above, including the Software Title here is more for testing. All we really needed was ComputerID.
Step 3: Find the Primary User For Each Of these Machines
Show the Computer Names of computers with Zoom installed, along with the user assigned.
New Views Needed:
- v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP or
I want UserIDs, and ultimately user names, associated with the computers that have Zoom installed.
There are two ways we could do this depending on whether or not we want a “good enough” solution.
A) View: v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP
There’s a column in this view, TopConsoleUser0, which appears to return the user with the most time on the machine. This is probably going to be your primary user, but it’s not necessarily the primary user. This might be a “good enough” solution.
B) View: v_UsersPrimaryMachines
This view has only 2 columns: MachineID, which holds computerIDs, and UserResourceID, which holds the numeric ID of the user associated with that machine.
This method will get the assigned primary user to that device. However, because this view doesn’t return user names, an additional view will have to be pulled in to map the UserResourceID to a user name.
For now I’ll stick with the “technically correct” way, Option B.
The Problem Mapping Computers to Users
Most SCCM database views relate to each other based on a ResourceID column.
However, ResourceID on views that show computers contain different data than the ResourceID on views that show users — these two columns can’t directly relate to each other.
In order to find a user attached to a machine, we need a third view — one that can map computers to users.
That’s where the view v_UsersPrimaryMachines comes in.
The view only has two columns: MachineID for the computer, and UserResourceID for the user listed in SCCM.
Note: If like me, your Users list was empty or included only user groups, v_UsersPrimaryMachines will be empty, even if you assigned Primary Users from the SCCM Console’s “Devices” screen. The assigned individual users need to be listed in the SCCM Console’s Users area.
Note that neither of these columns are called “ResourceID”. For newer people to this like me, this is something to watch for. It just means we have to be careful how we relate v_UsersPrimaryMachines to another view.
We’ll pull in v_UsersPrimaryDevices via a second JOIN. I joined it using a third variable, “RAM”.
I set the relationship so that ResourceID on v_GS_ADD_REMOVE_PROGRAMS is related to MachineID on v_UsersPrimaryMachines. The two columns hold the same kind of data, the machine IDs.
The SELECT statement was changed to only spit out the Computer name, and the numeric User ID that it’s associated with.
SELECT DISTINCT OS.Netbios_Name0 as 'ComputerID', RAM.UserResourceID as 'UserID' FROM v_GS_ADD_REMOVE_PROGRAMS as CS INNER JOIN v_R_System_Valid as OS on CS.ResourceID = OS.ResourceID INNER JOIN v_UsersPrimaryMachines as RAM on CS.ResourceID = RAM.MachineID WHERE (CS.DisplayName0 = 'Zoom')
Step 4: Return the User Name for Each User ID
Show the machines with Zoom installed, along with the username of the primary user assigned to it
New View to Add:
Fairly straightforward addition to the query, but will require yet another view to be JOINed in.
View: v_R_User refers to user IDs via its ResourceID column, and contains a User_Name0 column that returns the user name. Alternatively, Unique_User_Name0 will return DOMAIN\\USER.
A third INNER JOIN is executed. I imagine there’s a simpler way to do this:
SELECT DISTINCT OS.Netbios_Name0 as 'ComputerID', ROM.User_Name0 as 'User Name' FROM v_GS_ADD_REMOVE_PROGRAMS as CS INNER JOIN v_R_System_Valid as OS on CS.ResourceID = OS.ResourceID INNER JOIN v_UsersPrimaryMachines as RAM on CS.ResourceID = RAM.MachineID INNER JOIN v_R_User as ROM on RAM.UserResourceID = ROM.ResourceID WHERE (CS.DisplayName0 = 'Zoom')
Step 5: Now Show Machines Without Zoom
Return the Computers which do not have Zoom installed, with the username of the device’s primary user.
We now have a list of computers with Zoom installed, along with their assigned Primary User.
But that’s not exactly what we want, is it? We want machines WITHOUT Zoom installed.
We can make this happen in the query’s WHERE clause.
I wanted to show how to query machines with Zoom installed first because we need to use that. To get machines without Zoom, we write the WHERE clause to say:
“We want usernames that do NOT appear in (this list of usernames that have Zoom installed on their machine)”.
Again, I’m sure there’s a much easier way to do this, but getting the assigned primary user for a machine took a few extra steps.
We can use the “not in” operator as shown in the below query:
SELECT DISTINCT OS.Netbios_Name0 as 'ComputerID', ROM.User_Name0 as 'User Name' FROM v_GS_ADD_REMOVE_PROGRAMS as CS INNER JOIN v_R_System_Valid as OS on CS.ResourceID = OS.ResourceID INNER JOIN v_UsersPrimaryMachines as RAM on CS.ResourceID = RAM.MachineID INNER JOIN v_R_User as ROM on RAM.UserResourceID = ROM.ResourceID WHERE OS.Netbios_Name0 not in ( SELECT distinct CS.Netbios_Name0 as 'ComputerID' FROM v_R_System_Valid as CS INNER JOIN v_GS_ADD_REMOVE_PROGRAMS as ROM on ROM.ResourceID = CS.ResourceID WHERE ROM.DisplayName0 = 'Zoom' )
We now have a list of computers without Zoom installed, with their primary user.