SCCM Primary Last Logon User Report | SQL Query | ConfigMgr

Are you looking for SCCM Primary Last Logon user Report? If so, you are in the correct place. Let’s discuss in this post how to find out primary user info and last logon user info from SQL query. You may need this type of report if someone asks you in your organization.

I have seen this question popping up in the forums and other communities. This report may help most of the SCCM admin, I believe.  I would recommend looking at ConfigMgr SCCM List of Default Reports to get more details about out-of-box reports.

Pre-requisite

If you need Primary user details from SCCM, you must need to enable user discovery from the ConfigMgr console. You can get the Last logon user information from system discovery. Let’s see below the SQL query to get such information.

Primary User & Last Logon User?

What is SCCM Primary User?

The SCCM Primary User is the user assigned to a particular device and the method by which each user-device relationship was assigned.

Patch My PC

What is SCCM, Last Logon User?

As the name suggests, the last logon user is the last user that logged into the device for a significant amount of time.

What is SCCM Currently Logged on User?

The currently logged-on user to the ConfigMgr or SCCM managed device. This is the almost live data of the logged-on users.

SQL Query – SCCM Primary Last Logon user Report

Now, it’s time to find out the SQL query for SCCM Primary Last Logon user Report. You can follow the steps mentioned below.

  • Open the SQL Management Studio from the SQL box.
  • Connect your Database Engine.
  • Right Click on your database CM_XXX and click on ‘New Query’
  • Please copy the following SQL query to find the report of Primary User information as well as Last logon user information with their respective email id
  • Click on the Execute button.
Select VRS.Netbios_Name0 as 'Machine Name', VUMR.UniqueUserName as 'Primary User', VRS,User_Name0 as 'Last Logon User',
VRU.Mail0 as 'Primary User Email', VRU1.mail0 as 'Last Logon User Email'
from V_R_System VRS
left join v_UserMachineRelationship VUMR on VRS.ResourceID=VUMR.MachineResourceID
left join V_R_User VRU on VRU.Unique_User_Name0=VUMR.UniqueUserName
join v_R_User VRU1 on VRU1.User_Name0=VRS.User_Name0
SCCM Primary Last Logon User Report | SQL Query |   ConfigMgr
SCCM Primary Last Logon User Report | SQL Query | ConfigMgr

Resources

  • Last-Logon-Timestamp attribute
  • Currently Logged on User – Tool – PsLoggedOn v1.35

Author

Debabrata Pati has more than 7+ years of experience in IT. Skilled in MEMCM, Azure, and Powershell. More than five (5) years of experience in MEMCM (SCCM) administration, OSD, and Troubleshooting for the environment with more than 100K client devices.

Adaptiva

5 thoughts on “SCCM Primary Last Logon User Report | SQL Query | ConfigMgr”

  1. dont understand why the usermachinerelationship view doesnt have a the userid included so the user view can just be joined on that and not the user name.

    Reply
  2. Hello Prajwal ,
    Tried to run the above query but it gave me errors :

    Msg 207, Level 16, State 1, Line 1
    Invalid column name ‘VRS’.
    Msg 209, Level 16, State 1, Line 1
    Ambiguous column name ‘User_Name0’.

    Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.