Using SCCM to query the ConfigMgr database to find which clients a particular user had logged in to.
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.
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)|
- Open the SCCM (ConfigMgr) console
- Click the Monitoring tab
- Create new Query wizard
> Right click Queries
> Create Query
- General Query Settings
> Name: All Systems – Where user last logged on
> Comments: Brief description of what the query is for
> Click Edit Query Statement
- Query Statement
> Click Show Query Language
> 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##
- 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
> 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
> Click Next
> Click Close
- Test your new query
> Right click new query
> Click Run
- Username prompt
> Enter the username for the user you would like to query
> Click Ok
- You will be presented with the results of where a particular user logged in
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|
You can use the query builder to add/remove fields to your preference.
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
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.
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.