By Garth Jones
In my last blog post, I talked about how there are three places to find user name details and how they are inventoried. In this blog post I will talk about how best to leverage each of them.
Here’s a quick refresher of where to find the user name data by using the following three queries:
- v_R_System / v_R_System_Valid
Again, I will use the same SQL query from Part I to talk about what SQL views to use and why. In the next screenshot are the results from my test lab.
left join v_GS_COMPUTER_SYSTEM CS on RV.ResourceID = CS.ResourceID
left join dbo.v_GS_SYSTEM_CONSOLE_USAGE_MAXGROUP SCUM on RV.ResourceID = SCUM.ResourceID
Why use one SQL view over the other? The answer depends on what you are trying to do.
Here are three short scenarios:
1. You need to locate the owner of a PC.
2. You need to locate the last logon user for a PC.
3. You need to determine the last logon user’s history for a PC.
You Need to Locate the Owner of a PC
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 PC and therefore is likely to be the owner of the PC. However, this view does have some drawbacks. Look at rows 11 and 15. Who is the Top Console User for these devices? This is why I look to the other user name columns when a result is null.
You Need to Locate the Last Logon User for a PC
In this scenario I don’t care about who the owner of the PC is and therefore the Top Console User is not useful. In this case I will choose either v_GS_COMPUTER_SYSTEM or v_R_System and I will generally pick v_R_System because the data is indexed, so it is faster to search.
You Need to Determine the Last Logon User’s History for a PC
This is a bit of a grey zone situation. You should never totally rely on Configuration Manager to know if a user ever logged onto a PC, but if you want a best guess, then there are two different places to gather this data. One of the places is the history SQL 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 Top Console User.
As I said in Part I, I use all three SQL views. Now that you know how each is used you can leverage the one that makes the most sense within your Configuration Manager reports.