Mark Mears at

A compilation of information about Microsoft System Center family products

Removing/Deleting Many Incidents from the SCSM Database using PowerShell

Removing/Deleting Incidents from the SCSM Database

In System Center Service Manager 2010 (SCSM), the console does not allow deletion of work items from the console views. We can however use the PowerShell SMLets to accomplish this.

A couple of examples of when this might be appropriate are listed below:

· No test system is available and testing of work items has been conducted on the production system. Now that testing is complete it is desired that the test work items be removed.

· A problem with a workflow or notification causes a flood of work items to be created. Removal of these bogus work items is desired.

NOTE: ITIL and MOF guidelines specify that the deletion or removal of work items from live systems is not permitted. This is the reason why no deletion capability exists in the SCSM console.

NOTE: Direct deletion from the database is not supported by Microsoft and could cause damages to the system that would require removal and reinstallation with the possibility of data loss. Microsoft does allow and has documented the use of the SMLets to remove objects from the database.

I recently ran into a situation where 25000 incidents were created due to an email flood into the SCSM incoming mailbox. I am going to be documenting one way to remove items from the database and perform this cleanup. A few additional notes along the way will be given to provide some additional insight into how and why.

Step #1 – Identify the work items to be removed

Work items of the Incident class are maintained in the ServiceManager database in the MT_System$Workitem$Incident table on the SQL Server used for the SCSM Management Server. Within this table are several columns we can use to identify a particular work item. Several of the columns have a text name and a concatenated GUID to provide a unique column name. This makes it a bit more difficult to select a column at a glance but is it possible to identify the column names we need to look at. Some of the important columns are:

· Title_* - The title of the Incident as seen from the console view

· BaseManagedEntityId – The actual ID of the incident in a GUID format

· ID_* - This is the assigned IncidentID from the console view

We will be using a PowerShell script to perform the deletion of the objects from the console. (In reality, they are not actually deleted but rather marked for later deletion in the database.) In the PowerShell script that we use to remove the incidents we need to have some manner of detecting or selecting which incidents we want to delete. If the number of incidents is below a few hundred and these have some criteria where we can perform a filter operation, PowerShell can remove them in a single command. If there are more that let’s say 1000 incidents to delete, this can be a bit more challenging due to timeout issues. In my case I had 25000 incidents to delete. Furthermore, I only had a single criteria that I was able to use in the filter operation which did not reduce this number. That criteria was that for each of the affected incidents, the Incident Title was garbled but began with the same characters. So I needed to find some other criteria that I could use to filter the indents that would allow me to delete them.

I was able to create a SQL query that would perform this filter operation for me and it presented me with the 25000 incident rows in the SQL return set.

The SQL Query that I used for this was:


FROM MT_System$WorkItem$Incident

WHERE Title_9691DD10_7211_C835_E3E7_6B38AF8B8104

LIKE 'somestring%'

Step #2 - Script the deletion

This returned the entire list of incidents which included all of the data for each incident. The BaseManagedEntityId field contains the actual ID of the incident which is how I went about to start the process. The PowerShell command to remove an object (specifically an incident) from the database is:

Get-SCSMObject -Class (Get-SCSMClass -Name System.WorkItem.Incident$) -Filter "ID -eq '< BaseManagedEntityId >'” | Remove-SCSMObject –Force

This is great! But it only deletes a single incident from the database at a time. I didn’t want nor did I have the time to repeat this 25000 times so I needed a better way. Luckily we can get PowerShell to read from a text file so from the SQL query I was able to generate the list of BaseManagedEntityId’s corresponding to the list of incidents that I wanted to delete. I populated a text file with these 25000 entries and modified the Powershell command to utilize and read from the text file.

The new command I used is:

Get-Content <Fully Qualified Path to the Text File> | ForEach-Object{$ToClose= Get-SCSMObject -Class (Get-SCSMClass -Name System.WorkItem.Incident$) -Filter "ID -eq '$_'";$ToClose | Remove-SCSMObject -Force}

To dissect and explain this command we are using the Get-Content command to read a line from the text file that we specify using the fully qualified path and filename. It puts the value it reads into a variable called $ToClose. We are then piping that output to the ForEach-Object command to perform an action on each list entry in the text file. We are basically using the same command as before to remove a single object replacing the ID in the command with the entry in the text file. The Remove-SCSMObject command requires an object to work with so we use the variable that we created ($ToClose) which contains the incident object that we filtered on.

The –Force parameter is required for work items since the Remove-SCSMObject only marks the item for deletion to be placed in the DeletedItems view visible from the Administration pane. This won’t work for work items since they cannot be restored.

Step #3 – Verify the results

The script ran for nearly two hours but when it completed, a refresh of the incident view showed that the 25000 bogus incidents were indeed no longer present. They will exist in the database until the next purge which occurs on a daily schedule at which time they will be removed.

Published Oct 14 2011, 02:13 PM by markmears
Filed under: ,


No Comments
Copyright -, Inc. - 2010 All Rights reserved.
Powered by Community Server (Commercial Edition), by Telligent Systems