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

Ken Thompson
Ken Thompson used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shabarinath RamadasanInfrastructure Architect

Commented:
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.

https://github.com/adbertram/Random-PowerShell-Work/blob/master/ActiveDirectory/Get-UserLogonSessionHistory.ps1

Cheers !
Shaba
yo_beeDirector of Information Technology

Commented:
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.
Mike TLeading Engineer

Commented:
Hi,

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:

https://skatterbrainz.wordpress.com/2016/12/08/sccm-sql-queries-find-computers-by-user-logins-and-login-counts/
and
https://skatterbrainz.wordpress.com/2016/09/02/sccm-sql-query-devices-by-user-login/

Both sound like they do what you want.

An interesting article that covers tracking both local machine logons and domain logons is here:
https://devblogs.microsoft.com/scripting/how-can-i-count-the-number-of-times-a-user-has-logged-on-to-a-computer/

Mike
IRM - SCCM Systems Analyst V
Commented:
The resolution for this was to use Active Directory login information.  The task was handed to our Server Team and they are working up a PowerShell script to pull the data needed.  There are no better ways to pull this information from SCCM.  Not sure how to close out the question but I'll try.
yo_beeDirector of Information Technology

Commented:
You can post the Powershell script  and select the hyperlink in the upper right corner that Found my own solution

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial