~ Radu Tomoiaga | Support Engineer
This article is a follow up to my original post titled How to determine who deleted what objects in the Configuration Manager console (http://blogs.technet.com/b/configurationmgr/archive/2013/10/01/how-to-determine-who-deleted-what-objects-in-the-configuration-manager-console.aspx). As in System Center 2012 Configuration Manager (ConfigMgr 2012), we have only SQL Reporting Services for the reporting component and I decided to write this article to fill in the blanks that were left behind from that first post.
Once in a while you may face an issue where you discover that some clients are missing in the Configuration Manager console and you’re not able to figure out what happened. You suspect that one of the ConfigMgr admins might have accidentally removed them but how can you figure out which one did what? Here’s a tip showing how you might be able to figure this out.
As in my original post, we want to look for a status message ID of 30066 or 30067. These mean that a user has either deleted a resource or all resources from a collection.
To do this we need to create a new report in Configuration Manager 2012 and we will use a custom SQL query to do it.
The SQL query is below:
SELECT TOP (100) PERCENT dbo.v_StatMsgAttributes.AttributeValue AS 'User', dbo.v_StatusMessage.MessageID AS 'has deleted', dbo.v_StatMsgInsStrings.InsStrValue AS 'this computer', dbo.v_StatusMessage.RecordID , dbo.v_StatMsgAttributes.AttributeTime as 'on' FROM dbo.v_StatusMessage INNER JOIN dbo.v_StatMsgInsStrings ON dbo.v_StatusMessage.RecordID = dbo.v_StatMsgInsStrings.RecordID INNER JOIN dbo.v_StatMsgAttributes ON dbo.v_StatMsgInsStrings.RecordID = dbo.v_StatMsgAttributes.RecordID WHERE ((dbo.v_StatusMessage.MessageID = 30066) OR (dbo.v_StatusMessage.MessageID = 30067)) AND (dbo.v_StatMsgInsStrings.InsStrValue LIKE @variable ) and (dbo.v_StatMsgInsStrings.InsStrIndex='2') ORDER BY 'this computer' DESC
We need to create a new report that will use the query above and it will look something like this:
When running the report we need to provide a search string which is the computer name of the client or clients that are missing:
This will show you the user name and the record they deleted. Below is another example with a % wildcard (which means all).
Hope this helps!
Radu Tomoiaga | Support Engineer | Microsoft
System Center All Up: http://blogs.technet.com/b/systemcenter/
System Center – Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/
System Center – Data Protection Manager Team blog: http://blogs.technet.com/dpm/
System Center – Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
System Center – Operations Manager Team blog: http://blogs.technet.com/momteam/
System Center – Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center – Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm
The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/