Team blog of MCS @ Middle East and Africa

This blog is created by Microsoft MEA HQ near shoring team, and it aims to share knowledge with the IT community.With its infrastructure and development sides,It brings to you the proven best practices and real world experiences from Subject Matter Experts
Follow Us On Twitter! Subscribe To Our Blog! Contact Us

Service Manager 2012 Console Task to Bulk Remove Instances

Service Manager 2012 Console Task to Bulk Remove Instances

  • Comments 4
  • Likes

If you have a demo/preprod environment  for Service Manager 2012, probably you created lots of instances (Incidents, Change Requests, Release Records etc. ) for testing purposes. This helps you to get demo just right. But when it comes to production, you don’t want to have all those created “things” on your console.  In this blog post we’ll create a console task (a simple button) for each work item and assign it to a PowerShell script. You’ll simply click button and everything will blow! Smile It helps you to reset your demo environment.

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;

image

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”

image

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

image

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

image

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.

image

Click Create.

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

image

Now just click it and wait for the rest.

image

image

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

image

All them are gone.

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

Comments
  • I'm facing an issue on the "$SqlAdapter.Fill($DataSet)" line and the error message I am receiving is:

    Exception calling "Fill" with "1" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)"

    At line:1 char:17

    + $SqlAdapter.Fill <<<< ($DataSet)

       + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

       + FullyQualifiedErrorId : DotNetMethodException

  • You can use

    Get-SCSMObject –Class (Get-SCSMClass –Name System.WorkItem.ReleaseRecord$) | Remove-SCSMObject -Force

    instead of SQL query. Try this.

  • Hello Anil

    That command also works for SCSM2012, are you sure?

  • How can we reset the ID numbers?  They continue to increment up from where they left off.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment