Find User Name Data Within ConfigMgr – Pt 2

In my last blog post, I talked about the four places to find user name data details and how they are collected/inventoried: Hardware Inventory, Heartbeat Discovery, Asset Intelligence and BGB. In this blog post, I show you the best way to leverage each of them.

Here’s a quick refresher of which SQL Server views you can use in order to find user name data:

· v_GS_COMPUTER_SYSTEM

· v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP

· v_R_System / v_R_System_Valid

· v_CombinedDeviceResources

When to Use Each SQL Server View

Again, I am using the same SQL Server query from Part 1 because I want to discuss what SQL Server views to use – when and why. In the next screenshot, you see the results from my test lab.

Select
RV.Netbios_Name0,
CS.UserName0,
SCUM.TopConsoleUser0,
RV.User_Domain0,
RV.User_Name0,
CDR.CurrentLogonUser
from
dbo.v_R_System_Valid RV
left outer join dbo.v_GS_COMPUTER_SYSTEM CS on RV.ResourceID = CS.ResourceID
left outer join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on RV.ResourceID = SCUM.ResourceID
left outer join dbo.v_CombinedDeviceResources CDR on RV.ResourceID = CDR.MachineID

Why would you use one SQL Server view over another? The answer depends on what you are trying to do.

Here are four short scenarios:

1. You need to know who is logged-on to a computer RIGHT now!

2. You need to locate the owner of a computer.

3. You need to locate the last logon user for a computer.

4. You need to determine the last logon user’s history for a computer.

Below, I go into more detail about what view to use in each scenario.

Find User Name Data – Scenario 1

You need to know who is logged-on to the computer RIGHT now!

In this case, a computer has a virus. It is on the network and you need to find it immediately! You need to use v_CombinedDeviceResources.CurrentLogonUser as this view tells you who is logged-on now.

The other SQL Server views collect user name data by relying on Configuration Manager’s Hardware Inventory or Heartbeat Discovery. In most cases, this inventory information is only collected once-a-day. However, the BGB channel is updated within 60-seconds of someone logging-on to a computer (it’s a bit longer than 60-seconds when they log off). This means that you can find user name data right away.

Find User Name Data – Scenario 2

You need to locate the owner of a computer

In this situation, the Top Console User found within v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP is the best choice to use as this user has the most logon time for a specific computer and is therefore likely to be the owner of the computer. This view, however, does have some drawbacks. Look at Line 1 in the results from my test lab. Who is the Top Console User for this device? What about Line 8? Who is currently logged-on to that computer? Who is likely to know anything about this computer?

Find User Name Data – Scenario 3

You need to locate the last logon user for a computer

If you know who is currently logged-on to a computer, or who the top console user is, why do you need to know who was last logged-on to a computer? These details are rarely needed, but there are a few cases where you need to know this information. See Line 14. In situations like this one, you can choose from either v_GS_COMPUTER_SYSTEM or v_R_System.

Usually, I pick the v_R_System view. Why? There are two reasons. First, I don’t find the v_GS_COMPUTER_SYSTEM very helpful. In Part 1, I showed that the results for UserName0 gave a lot of null values. Second, the v_R_System view is already within my query. Since the v_R_System view is already there, by not adding another SQL Server view to my query, I also don’t increase the overhead which would happen if an additional view had to be processed. Here’s a tip: if you are looking at computer details, you should never have a query without the v_R_System view. See my post, Get the Most Accurate and Up-to-Date Data Using the v_R_System_Valid SQL Query in Configuration Manager 2012, for more details.

By the way, for many years, the v_GS_COMPUTER_SYSTEM view was my go-to view, but as Configuration Manager evolved, the need for this view became rare, so I seldom use it anymore. I find, sadly, that there are still a ton of queries on the internet that use it.

Find User Name Data – Scenario 4

You need to determine the last logon user’s history for a computer

This is a bit of a gray zone situation. You should never rely exclusively on Configuration Manager to know if a user ever logged-on to a computer. Instead, use AD. If you still want to use Configuration Manager for a “best guess,” then there are two different places to gather this data. One of the places is the history SQL Server view for v_GS_COMPUTER_SYSTEM (v_HS_COMPUTER_SYSTEM) or v_GS_SYSTEM_CONSOLE_USAGE which is used by v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP to determine the identity of the Top Console User.

Combining Tip

Why not combine three of the main user name columns into one query?

isnull(SCUM.TopConsoleUser0,isnull(CDR.CurrentLogonUser, isnull(RV.User_Domain0+”+RV.User_Name0,’n/a’))) as ‘username’

When the Top Console User result is null, this query displays the Current Logon User details. If those details are null, then it displays the Last Logon User information. If all three of the user name results are null, then ‘n/a’ is displayed.

This tip works well, but it isn’t perfect. There is a problem if you try to use it as a drill down. For more information, see my blog post, Why is My Prompt in SSRS Not Working?

As I said in Part 1, I use all four SQL Server views. Now that you know how each is best used, you too can leverage the ones that make the most sense within your Configuration Manager reports. Better yet, combine the user name data similar to the results I showed you above. Please feel free to touch base with me @GarthMJ if you have any questions.