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?
What is SCCM Primary User?
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
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.
There is a typo in the code.
VRS,User_Name0 should be VRS.User_Name0
How can I do this from a specific collection?
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.
left join v_FullCollectionMembership fcm on VRS.resourceid=fcm.resourceid
where fcm.CollectionID = ‘SMS00001’