Link to home
Start Free TrialLog in
Avatar of Ken Thompson
Ken Thompson

asked on

SCCM query - report of all systems a user has logged into in the last 30 days

I have a request to create a report in SCCM that will show how often a specific user has logged into a specific system in the past 30 days.

I could also try SQL query directly on the SCCM database if that is an option - just not an expert with SQL queries.

I've become pretty adept at hacking canned reports to do what I want but I don't have the skill to write this report from scratch.  Any assistance would be greatly appreciated.

SCCM 2012 R2 is our platform and most of our systems are Windows 10.
Avatar of Shabarinath TR
Shabarinath TR
Flag of India image

Hi Ken,

Not sure if this can be achieved through SCCM. However, this is possible if you use powershell along with audit logging.

GPOs has a specific area to enable auditing specific to logon and logoff. If these controls are enabled and applied to the client machines where user is logging in, user logon and logoff will be tracked in the event log.

Use a powershell script to query the event log from the client machines to get the logon time and log off time, which can give you the total time each session was used. Here is a ready made script available in github which can be a great resource to start.

Cheers !
I agree with Shabarinath.  SCCM will not (from my knowledge) give you the report you are looking for nor do I think going directly to backend DB lead to anything.  Looking at his link I think it might be the most viable solution.  The downside to this is that you are only going to get as far back as the security logs go.  So if you have a log file set to a low file size and you are not archiving you will be writing over the data rather quickly.  

I have a vbs logon script that i use to collect some basic info of the computer and user that is written to a MSSQL db. This gives me a much better history because I can go back pretty far.
I built a front-end app that my team uses to query the results based on one of these filters (User Name, Computer Name, or IP).  This script was built for one purpose, but it has morphed into a very useful tool to see when a user really logged on and off their machine as well as their machine history.

I would be happy to share it with you if you like.

SCCM can report on many, many things. One of those things is user logons. In versions lately it is there, in the console.
There is also a built-in report "Count users by domain".

However, someone better at SQL than me has already written a query for what you want to do:

Both sound like they do what you want.

An interesting article that covers tracking both local machine logons and domain logons is here:

Avatar of Ken Thompson
Ken Thompson

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You can post the Powershell script  and select the hyperlink in the upper right corner that Found my own solution