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.
Ken ThompsonIRM - SCCM Systems Analyst VAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shabarinath RamadasanInfrastructure ArchitectCommented:
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 TechnologyCommented:
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 EngineerCommented:
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
Ken ThompsonIRM - SCCM Systems Analyst VAuthor 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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yo_beeDirector of Information TechnologyCommented:
You can post the Powershell script  and select the hyperlink in the upper right corner that Found my own solution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.