If your organization uses MEMCM for configuration management, it’s more than likely that it is being managed by a team of people instead of a single person. And depending on how your organization works, you may have separate teams who creates collections, deploy the packages and applications, and the team that manages the infrastructure related stuff like creating and deleting boundaries, etc.
MEMCM is a really powerful tool and you can do some really bad stuff if you’re not careful. In case such situations occur, you may want to know who exactly made that error. Or let’s say that you are doing a collection cleanup and want to delete one that you believe is not being used actively. But the person who created it has not provided any details in description. That is where auditing comes in place.
While Configuration Manager provides a GUI way of doing the same, I find SQL queries to be faster and more customizable way to do things. In this blog post I’ll explain how to figure out what query to use for different auditing scenarios in MEMCM.
Figuring out the query
The figuring out the query part is pretty easy. All you have to do is go to Monitoring | System Status | Status Message Queries
There you can find all the built-in queries.
The next step is to right-click on the column section and select Expression.
You can now see what WQL query is being used for that specific status message query.
Example : Collections Created, Modified, or Deleted
Collections Created, Modified, or Deleted is one of the built-in status message queries. Once you have the WQL query for the same using steps described previously, it’s fairly straightforward to convert it into SQL in order to get the details.
WQL Query
select stat.*, ins.*, att1.*, stat.Time from SMS_StatusMessage as stat
left join SMS_StatMsgInsStrings as ins on stat.RecordID = ins.RecordID
left join SMS_StatMsgAttributes as att1 on stat.RecordID = att1.RecordID
where stat.MessageType = 768
and stat.MessageID >= 30015
and stat.MessageID <= 30017
and stat.Time >= ##PRM:SMS_StatusMessage.Time##
order by stat.Time descSQL Details
If you have worked on SQL with respect to MEMCM for some time, you’ll know how the WQL tables translate into SQL. What I usually do is type the name of the table after the underscore on SSMS and let the IntelliSense help me out.
In this case:
- SMS_StatusMessage = v_StatusMessage
- SMS_StatMsgInsStrings = vStatusMessageInsStrs
- SMS_StatMsgAttributes = vStatusMessageAttributes
If you do some further deep dive into these views, based on the values used in the WQL query and some searching on the Microsoft Docs, you’ll come to know that the MessageID column in the v_StatusMessage view gives details about the action being performed:
- 30015 – Collection created
- 30016 – Collection modified
- 30017 – Collection deleted
The vStatusMessageInsStrs and vStatusMessageAttributes are used to get details regarding the Collection Name, Collection ID and the user who did the modifications.
Then there is another view, called vStatusMessagesWithStrings that contains the MessageID, the Collection Name, Collection ID and user details as well. So Instead of joining different views, we can use a single view to get these details:
- MessageID – MessageID
- InsStrValue1 – User Name
- InsStrValue2 – Collection ID
- InsStrValue3 – Collection Name
SQL Query
Let’s suppose you want to get the details of who created a collection called Test – Status Message Demo, all you need to do is to write the following query:
--Status message audit for collection creation
SELECT
InsStrValue3 'Collection Name',
InsStrValue2 'CollectionID',
InsStrValue1 'Created By',
[Time] 'Created On'
FROM
vStatusMessagesWithStrings
WHERE
MessageType='768'
AND MessageID ='30015'
AND insStrvalue3='Test - Status Message Demo'
Similarly, you can find out the WQL behind the other status message queries and with some tinkering on your end, convert them into SQL to get the required details.
