Auditing in MEMCM Using Status Messages SQL Queries

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.

Screenshot of MEMCM Console showing status message queries

The next step is to right-click on the column section and select Expression.

Screenshot of MEMCM Console showing the right-click column menu

You can now see what WQL query is being used for that specific status message query.

Screenshot of MEMCM Console showing status message queries along with Expression column

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 desc

SQL 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.

Screenshot of IntelliSense being used in SQL Server Management Studio

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
	InsStrValue3 'Collection Name', 
	InsStrValue2 'CollectionID',
	InsStrValue1 'Created By',
	[Time] 'Created On'
AND	MessageID ='30015'
AND insStrvalue3='Test - Status Message Demo'
Screenshot of SQL query output.

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.

By Sam

Hi, I am Samarth Joshi. Gaming got me into computers and programming, which ultimately led me to IT. Now just trying to share what I learn.

Leave a Reply

Your email address will not be published. Required fields are marked *