SCCM 2012: Query to find clients a user last logged in to

Summary:

Using SCCM to query the ConfigMgr database to find which clients a particular user had logged in to.

Issue:

A customer wanted to know a history of which clients on their estate a particular user had logged into in the last couple of days and cross reference their results from Active Directory against the information held within the SCCM database.

Resolution:

There are many ways to skin this particular cat but this seemed like a viable scenario to demo a how to query the ConfigMgr database to scan for instances of where this particular user had logged onto a client.

Note: The date range you can query to depends on your data retention policy (how long you keep records in your CM database)

Implementation:

  1. Open the SCCM (ConfigMgr) console
  2. Click the Monitoring tab
  3. Create new Query wizard
    > Right click Queries
    > Create Query
    SCCM_QueryUserLastLoggedOn_CreateQuery_29042015
  4. General Query Settings
    > Name: All Systems – Where user last logged on
    > Comments: Brief description of what the query is for
    > Click Edit Query Statement
    SCCM_QueryUserLastLoggedOn_General_29042015
  5. Query Statement
    > Click Show Query Language
    SCCM_QueryUserLastLoggedOn_ShowQueryLanguage_29042015
    > Paste the following query into the Query Statement

    SELECT SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonTimestamp
    FROM  SMS_R_System
    WHERE SMS_R_System.LastLogonUserName = ##PRM:SMS_R_System.LastLogonUserName##
    

    > Click Ok
    SCCM_QueryUserLastLoggedOn_QueryStatement_29042015 

  6. Set your limiting collection
    In a development environment it is not a major issue not setting a limiting collection as there are not many objects in the SCCM database.
    However in production environments this is generally a bad idea as the wider the scope of your limiting collection the more tables will be queries in your SCCM database.
    > Check limited to collection
    > Click Browse
    SCCM_QueryUserLastLoggedOn_LimitingCollection_29042015
    > Click Device Collections from the drop down menu on the top left
    > Click All Systems (for dev environments, a smaller target collection would be preferable for testing)
    > Click Ok
    SCCM_QueryUserLastLoggedOn_AllSystems_29042015

    > Click Next
  7. Summary
    > Click Close
  8. Test your new query
    > Right click new query
    > Click Run
    SCCM_QueryUserLastLoggedOn_Run_29042015
  9. Username prompt
    > Enter the username for the user you would like to query
    > Click Ok
    SCCM_QueryUserLastLoggedOn_UsernamePrompt_29042015
  10. You will be presented with the results of where a particular user logged in
    SCCM_QueryUserLastLoggedOn_Results_29042015

Query Statement Explained

SELECT SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonTimestamp

The SELECT statement is used to decide what information you would like retrieved by the Query in this case the following:

SMS_R_System.LastLogonUserName Username for user you will search for
SMS_R_System.Name Name of client
SMS_R_System.LastLogonUserDomain Domain user logged into
SMS_R_System.LastLogonTimestamp Timestamp

You can use the query builder to add/remove fields to your preference.

FROM  SMS_R_System

FROM statement indicates which table the information is stored in, in this case SMS_R_SYSTEM

WHERE SMS_R_System.LastLogonUserName = ##PRM:SMS_R_System.LastLogonUserName##

The WHERE statement is the condition by which results are filtered.

##PRM: ##  will prompt the user to enter information, the message prompt and data type will match that of the field you have targeted.

In this case System.Resource.LastLogonUserName and data type text

SCCM_QueryUserLastLoggedOn_UsernamePrompt_29042015

You can also remove the WHERE statement all together and all records mentioned in the SELECT statement will be displayed.

SELECT SMS_R_System.LastLogonUserName, SMS_R_System.Name, SMS_R_System.LastLogonUserDomain, SMS_R_System.LastLogonTimestamp
FROM  SMS_R_System

This will display all records for all users in the CM database and you can then filter on the results view using the search functionality in the SCCM console.
SCCM_QueryUserLastLoggedOn_Results2_29042015
If your database is particularly large or if the search is going to be run more than a few times creating a query to perform the same filters would be preferable and require less resource to process.

Advertisements

7 responses to “SCCM 2012: Query to find clients a user last logged in to

  1. Nice!
    Is it possible to show every instance a user logged on to machines during a time period rather than just the last logon? I imagine that logging that sort of data would be massive though 😦

    Like

    • Unfortunately I don’t think Sccm holds that kind of data for very long. You would be better off checking on your domain controllers for historical logging; dependant on your retention policy defining how far back those logs go. Another option could be to add a log on/log off script that captures the session or even delve the local event log.

      If you have budget for a more enterprise solution there are many vendors such as uberAgent, controlUP, lakeside etc.. That specialise in this sort of data gathering. Another option may be to investigate on boarding your endpoints to Microsoft SCOM but this my end up being rather expensive depending on your license terms.

      Like

  2. Would it be possible to build a quick powershell script that queries SCCM with list of know computer objects (ie, a script with ‘foreach $computer in $list’ …) that would output each said computer name and the last logged on user for each?

    I’ve seen a couple examples so far, but no clear defined way of extracting just the information I need.

    Like

    • I would suggest you create a new collection of the devices you want to query then run the query against it rather than relying on interim text files or lists.
      This has a couple of advantages:
      1) it utilises native sccm and is dynamic, every time the query is run it will use the latest data set. No need to update text input files/variables.
      2) you can automate collection membership with queries so you do not need to even maintain the membership to keep it relevant.

      I will try and write something up later today to point you in the right direction.

      Hope that helps

      Liked by 1 person

  3. Pingback: [SCCM 2012/2016]: Query SCCM to find clients a user last logged in to using PowerShell | ConfigMonkey.co.uk·

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s