Recently, I had a discussion with a fellow Configuration Manager MVP about user names, where to find them, and the pros and cons of each SQL view. This led me to the idea that I would write two blog posts on the subject.
In this blog post I will tell you the three main places where you can find user name data within Configuration Manager and how I rate each SQL view.
There are basically three places where you can find user names for any given PC. The three places are:
· v_R_System / v_R_System_Valid
The user name column in this SQL view is called UserName0. The data from this SQL view comes from Hardware Inventory.
The user name column in this SQL view is called TopConsoleUser0. The data from this SQL view comes from Asset Intelligence (via Hardware Inventory) to the Configuration Manager database. The user listed in this SQL view will be the person that has logged on to a specific PC for the most amount of time. The minimum logon percentage is 66% of the total logon time for a PC.
v_R_System / v_R_System_Valid
The user name column in this SQL 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 the User_Domain0. I will expand more on this in Part 2 of this blog post set. The data from these SQL views comes from Heartbeat Discovery also known as Data Discovery Collection.
Using the following query, let’s see the different results you will get for each of the SQL views. I will show you the results for one of my test labs in the next screenshot.
The first thing that I notice is that User_Name in v_R_System_Valid (RV.User_Name0) has the most number of results, but are the results really helpful? Notice in Lines 7 and 13 that SYSTEM is listed as the user name.
TopConsoleUser gives the next best results but, look again. Line 11 is null and therefore there is no data for that PC. Line 15 is even more interesting, what is windows manager\dwm-7?
Lastly, UserName0 gives a lot of null values, so how helpful is that?
Believe it or not, each of these SQL views has their place. Which of these do I use? The short answer is all of them. For the long answer you’ll have to wait for Part 2 tomorrow and then you can decide which SQL view(s) you like.