Find User Name Data Within ConfigMgr – Pt 1

A lot has changed since my discussion a few years ago with a fellow Configuration Manager (ConfigMgr) MVP about user names, where to find them, and the pros and cons of each SQL Server view. When a question came-up about user name locations, not long ago, at one of Enhansoft’s Ask Me Anything (AMA) sessions, I decided to re-write one of my old blog posts on the subject of where to find user name data within ConfigMgr. In the end, my re-write turned into two blog posts.

In this blog post, Part 1, I tell you about the four main places where you can find user name data within ConfigMgr, and how I rate each SQL Server view. In Part 2, I go deeper into each view and explain when you would want to use them in different scenarios.

Four Main Places to Find User Name Data

There are basically four places where you can find user names for any given computer and they MOSTLY come from Hardware Inventory. The four main SQL Server Views are:

· v_GS_COMPUTER_SYSTEM

· v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP

· v_R_System / v_R_System_Valid

· v_CombinedDeviceResources

v_GS_COMPUTER_SYSTEM

The user name column in this SQL Server view is called UserName0. The data from this SQL Server view comes from Hardware Inventory. This used to be my second favorite view, but it has fallen out of favor with me now. Why? I find that many of the other views are more useful, or more importantly, I already have the other ones within my SQL Server query.

v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP

The user name column in this SQL Server view is called TopConsoleUser0. The data from this SQL Server view comes from Asset Intelligence (via Hardware Inventory) to the Configuration Manager database. The user listed in this SQL Server view is the person that is logged on to a specific COMPUTER for the most amount of time. The minimum logon percentage is 66% of the total logon time for a computer.

This view is great when you want to know the owner of a computer. Unlike the other views, this one is based on time usage. This view, however, is not great for knowing who is currently logged on to a computer. Given this shortcoming, I still use the information from this column the most (kind of), but more on that in Part 2.

v_R_System / v_R_System_Valid

The user name column in this SQL Server view is called User_Name0. There is a catch with this one. In order to get the full user name details you also need to look at this column: User_Domain0. I am going to expand more on this particular view in Part 2 of this blog post set. The data from these SQL Server views comes from Heartbeat Discovery which is also known as Data Discovery Collection.

I only use the v_R_System / v_R_System_Valid view when I absolutely need to because who wants to join two columns (User_Name0 and User_Domain0) together? This view, similar to the v_GS_COMPUTER_SYSTEM view, has fallen out of favor with me.

v_CombinedDeviceResources

You can immediately tell from its name that this SQL Server view is a combined view. This view has multiple user names and they each come from different places.

· UserName

· PrimaryUser

· CurrentLogonUser

· LastLogonUser

IMO, I would ignore all of the user names in this view BUT CurrentLogonUser. The column for that view shows you who is logged on to the computer right NOW! It uses the BGB channel and is no more than 60-seconds out of date. Not too shabby, right? Even though this view is great for knowing who is currently logged on to a computer, it isn’t great for knowing who owns the computer. For example, if a Manager logs on to a computer, you know that they are logged-on, but you don’t know who is normally logged-on to that computer (aka the computer owner). This view is now my second favorite username to use within reports, but more on that in Part 2.

Say goodbye to spreadsheets for software and hardware tracking.

Endpoint Insights can instantly and effortlessly provide full software and hardware inventories for you so you can make educated decisions about your next moves.

Learn More

Testing the SQL Server Views

Using the following query, I am going to show you the different results I get for each of the SQL Server views listed above: UserName0, TopConsoleUser0, User_Name0, User_Domain0 and CurrentLogonUser. The results, for one of my test labs, appear in the next screenshot.

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

The first thing that I notice is that CurrentLogonUser in dbo.v_CombinedDeviceResources (CDR.CurrnetLogonUser) has the most number of results, but are the results really helpful? Look at Line 1. It shows that I’m currently logged-on to Ellen-PC, but that the Top Console User is Ellen. This means that Ellen uses this computer at least 60% of the time. If I have questions about that computer who should I talk to? The person currently logged-on to it (Garth), or the person who uses the computer the majority of the time (Ellen)? The answer, obviously, depends on what you are looking for. If you are planning to replace that computer, then Ellen. If there is a virus on that computer and you need to look at it right away, then Garth.

TopConsoleUser0 gives the most amount of entries next, but, look again. Lines 3, 6, 7, 9, 14, 15, 16, and 17 are null and therefore there is no data for those computers. Lines 4 and 13 are even more interesting. Who is gartekcm16ssrs? It might not be obvious, but cm16ssrs is an execution account (service account) for my SCCM reporting points (yes, I have more than one!) and it is not a real user.

Unfortunately, User_Name0 is not very helpful. I don’t have a good example in the screenshot above, but the problem I run into is when you have two domains with the same user name. Worse yet, is when the local user account also shares the same user name as the domains. How can you tell the difference between them? The answer is you can’t. As I mentioned earlier, you need to add User_Domain0 into the mix in order to see the difference. Even when it’s added, though, this view is still not super useful. The format is not the same as the other three columns in this example, and fixing this problem causes extra overhead in your SQL Server query.

Lastly, the results for UserName0 give a lot of null values, so how helpful is that?

User Name Data Conclusion

Believe it or not, each of these SQL Server views has their place and are helpful in certain situations. When you have a good understanding of how and when these views are employed, then you can decide which SQL Server view(s) you like. In the meantime, now you know where you can find user name data within ConfigMgr. Which of these views do I primarily use? The short answer is all of them. For the long answer you’ll have to wait for Part 2! Please feel free to touch base with me @GarthMJ if you have any questions.

Five products, one mission.

Everyone's endpoint management journey is unique, so we've streamlined hundreds of processes and created five unique products to make the lives of global IT professionals easier.

Learn more