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.

• ConfigMgr Application Package Distribution Related Default Reports | SCCM
• How to Launch ConfigMgr Reports | SCCM | Default Reports
• ConfigMgr Reports Site Infrastructure Related | SCCM | Default
• ConfigMgr Reports Power Management | Default | SCCM
• ConfigMgr Reports Task Sequence | Default | SCCM
• ConfigMgr Default Reports Software Updates | SCCM
• ConfigMgr Reports Driver Management | SCCM | Default
• ConfigMgr Client Status Reports | SCCM | Default How To Manage Devices (anoopcnair.com)
• ConfigMgr BitLocker Management Reports | SCCM How To Manage Devices (anoopcnair.com)
• ConfigMgr Asset Intelligence Reports | SCCM How To Manage Devices (anoopcnair.com)
• ConfigMgr Hardware Related Reports | SCCM | Default How To Manage Devices (anoopcnair.com)

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 -> Active Directory User Discovery
• Last Logon User -> Active Directory System Discovery

Primary User & Last Logon User?

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

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.