Service Manager 2012 Console Task to Bulk Remove Instances - TechNet Articles - United States (English) - TechNet Wiki

Don’t give up thought of that this is one time action and use only for demo environments or with –Whatif switch.

There are already workarounds for such demands. One of them from Travis;

http://blogs.technet.com/b/servicemanager/archive/2009/12/17/deleting-objects-in-bulk-in-the-cmdb-using-powershell.aspx

We’ll combine two different solutions into one and make bulk delete task easier. We'll create a custom PowerShell script for each work item category (This post will cover only for Release Records, but you can simply apply same script for all instances) and pin it as a task on Service Manager Console. So you’ll see different tasks within each work item category and be able to delete all previously created ones with one click.

To achieve this goal, we’ll use the following script;

Import-Module C:\Windows\System32\WindowsPowerShell\v1.0\Modules\SMLets 
 $SQLServer = "SQLSERVERNAME\InstanceName" 
 $SQLDBName = "SCSMDBNAME" 
 $SqlQuery = "SELECT BaseManagedEntityId FROM `MT_System`$WorkItem`$ReleaseRecord"
 
 $SqlConnection = New-Object System.Data.SqlClient.SqlConnection 
 $SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
 
 $SqlCmd = New-Object System.Data.SqlClient.SqlCommand 
 $SqlCmd.CommandText = $SqlQuery 
 $SqlCmd.Connection = $SqlConnection
 
 $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter 
 $SqlAdapter.SelectCommand = $SqlCmd
 
 $DataSet = New-Object System.Data.DataSet 
 $SqlAdapter.Fill($DataSet) 
 
 $SqlConnection.Close()
 
 $dataset.tables[0] | foreach { $_.BaseManagedEntityId } | foreach { $_.Guid } | Out-File C:\Temp\ReleaseRecordIDs.txt
 
 Get-Content c:\Temp\ReleaseRecordIDs.txt | ForEach-Object{$RR= Get-SCSMObject -Class (Get-SCSMClass -Name System.WorkItem.releaserecord$) -Filter "ID -eq '$_'";$RR | Remove-SCSMObject -force }
 
 Remove-Item c:\Temp\ReleaseRecordIDs.txt 

Now lets dive in each step;

Import-Module C:\Windows\System32\WindowsPowerShell\v1.0\Modules\SMLets

SMLets is a codeplex project that can be found on http://smlets.codeplex.com/ and provides additional cmdlets besides native Service Manager cmdlets.

To get SCSM objects from wide variety of classes, SMLets will help you a lot. So first thing you must achieve is importing SMLets module.

Also you can simply make a query to get if SMlets module imported or not with following command and trigger to import module with an if else condition. One example from Andreas Rynes ;

$Getmodule = (get-module|%{$_.name}) -join " "
if(!$GetModule.Contains("SMLets"))
{Import-Module SMLets -Force}

Next part;

SQLServer = "SQLSERVERNAME\InstanceName"
$SQLDBName = "SCSMDBNAME"
$SqlQuery = "SELECT BaseManagedEntityId FROM `MT_System`$WorkItem`$ReleaseRecord"

To get related objects from Service Manager DB, you need to know BaseManagedEntityID for each item. So that we’ll open a connection to SQL Server within PowerShell script and execute a query.

This query gets all Release Record EntityID’s and assigns them to the SQLQuery variable. If you execute same query on SQL box, you’ll see below output;

Above three lines assign SQL Server name, SQL DB Name and SQL Query values to the variables.

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)

$SqlConnection.Close()

Above part opens a connection to the SQL Server.

$dataset.tables[0] | foreach { $_.BaseManagedEntityId } | foreach { $_.Guid } | Out-File C:\Temp\ReleaseRecordIDs.txt

I added out-file line to figure out what we have in $dataset.tables[0] variable and also will use that content to remove bulk objects with;

Get-Content c:\Temp\ReleaseRecordIDs.txt | ForEach-Object{$RR= Get-SCSMObject -Class (Get-SCSMClass -Name System.WorkItem.releaserecord$) -Filter "ID -eq '$_'";$RR | Remove-SCSMObject -force -confirm:$false }

That is the actual command that removes work items from Service Manager DB. Firstly we load text file into the memory and then get release record for each line. And the last pipeline removes all Release Records.

This bunch of code can be run within PowerShell console that has a connection to the SQL box and works like a charm. But what we want is to add a simple button (Console Task) on Service Manager Console and trigger bulk deletions within it.

Don’t forget that Console Tasks run on Management Server, if you wish things happen on background, you should create workflows using Authoring Tool.

Now lets create a console task and let it to execute our PS1 script.

• Under Library/Tasks Click “Create Task”

• Provide Task Name, Description, Target Class and MP.
• Click Next

You can choose categories to enable the task to be displayed in certain place.

Choose PowerShell.exe for Full path and –command parameter to trigger our previously saved DeleteRRs.ps1 file.

For the working directory enter DeleteRRs.ps1 location.

Click Create.

From now on, you can view newly created task on the right pane;

Now just click it and wait for the rest.

You can customize PowerShell script and provide more meaningful outputs to the SCSM Console.

All them are gone.

Well please note that again, this is a one time action, and you’ll lose all your related instances.