Change Incident Auto-Increment Value in SCSM 2012 SP1

You would never want to change the auto-incrementing value range in SCSM 2012…would you? Well, I came across an instance where I needed to (which I will explain in detail in future blog posts).

Essentially, I was migrating from a previous SCSM 2012 SP1 environment (that was originally migrated from 2010) to a new SCSM 2012 SP1 environment. However, I needed the Incident Request (IR) reporting to be available from the old environment. Therefore, I wanted to ensure that our IR values did not duplicate those in the past.

In the ServiceManager database, a table called AutoIncrementAvailableRange stores the next available number for the classes. This is the table that we need to alter in order to choose a defined range.

First, we need to join some tables by running the following query:

select

               MT.TypeName,

               MT.ManagedTypeID,

               MTP.ManagedTypePropertyName,

               MTP.ManagedTypePropertyID,

               AIAR.FirstAvailableValue

from ManagedType as MT, ManagedTypeProperty as MTP, AutoIncrementAvailableRange as AIAR

where MT.ManagedTypeId = AIAR.ManagedTypeId and MTP.ManagedTypePropertyId = AIAR.ManagedTypePropertyId

You will now have a list that looks like this:

                       

Now (the scary part), we are ready to change the WorkItem ID. Do NOT run this unless you are ready!

For example, if we want to change the first available value to 40000, note the ManagedTypeId and the ManagedTypePropertyID for System.WorkItem and run the following:

update AutoIncrementAvailableRange

set FirstAvailableValue = 40000

where ManagedTypeId = ‘F59821E2-0364-ED2C-19E3-752EFBB1ECE9’ and ManagedTypePropertyId = ‘28B1C58F-AEFA-A449-7496-4805186BD94F’

Now, your next IR number should be IR40001.

Advertisements

Incrementing Lines in Orchestrator Runbooks

First of all, I love Orchestrator. Like…seriously. If it was able to walk upright and wasn’t so obnoxious at dinners, I may leave my wife for it…however, our relationship will probably go no further than me using it when I need to make my life simpler.

One of these scenarios just came up, as a matter of fact. As you may/may not know from previous posts, I had to migrate data from one SCSM environment to another. Rather than do this manually, I created an ENORMOUS runbook that handled the transfer of all open incidents. That, my friends, is to come in future posts (yes, it must be spread across multiple). For this post, I just wanted to simply display a technique that I used to increment lines in the runbook as to cover an entire list of incidents.

To do this, I first created two text files. One text file contained a single line with then number “1” called Line.txt. The second text file, called Incident.txt, contained a list of IR numbers, one on each line.

Next, I used two Read Line activities from the Text File Management grouping. The first I titled “Increments” and the second “Read Line”.

For the Increments activity, I pointed to the file location, chose ASCII for the file encoding and entered “1” for the line numbers portion. For the Read Line activity, I did the same for the file location and encoding, but for the line numbers I subscribed to the line text published data from the Increments activity.

“This is all good, Jesse, but how does it know to go to the next line?” Please, please practice some patience. I’m getting there.

A couple actions down (it doesn’t matter exactly where, as long as it is after you utilize the data you gather), I created two more activities. The first is a Run .Net Script activity called “Increment Number” in which I ran a Powershell script to bump the number up one (the script is below this paragraph). IMPORTANT: do not forget to add to the Published Data tab (in this case, create a string that has the name NumberString and variable NumberString). 

_______________Increment Number_______________

$ID = {Line text from “Increments”}

$NextNumber = [int]$ID

$NextNumber = $NextNumber +1

$NumberString = $NextNumber.ToString()

__________________________________________________

The next was a Search and Replace Text activity from the Text File Management grouping, which took the line text from Increments and replaced it with the NumberString from the Increment Number script.

Now, with a monitor up front to kick the runbook off as needed, our runbook will successfully move to the next line containing a new IR. Now, back to sleep while this runs…

Changing the Data Location for the SCSM Self-Service Portal

Anybody who has installed and configured the SCSM 2012 Self-Service Portal (henceforth, SSP) has surely had their “adventures.” If you still have hair left in your bloody scalp and are looking for even more abuse, I recommend doing what I did and standing up a new Management Server whilst maintaining the desire to keep the previous SSP in tact.

I suppose I owe a little bit of a backstory. You see, the environment that I came into had moved from SCSM 2010 to SCSM 2012. As you may know, there is no direct and supported migration path between the two. However, the organization insisted that they be migrated instead of standing up a new environment. In the end, this proved to be a costly move as errors were rampant, scripts ran unsuccessfully and the health of the environment was worse than a toddler swapping snot at daycare.

I just realized that this has already became a relatively disgusting post. Rather than erase and rewrite, I will just apologize.

Anyway, all of this babbling is to simply provide you with the registry key that you need to change on your portal server. The key is:

HKLM\SOFTWARE\Microsoft\System Center\2010\Common\Database

You will want to alter the data of these two values:

“DatabaseServerName”=”<CSMDBSERVER\INSTANCE>”

“DatabaseName”=”ServiceManager”

If you are lucky enough to have a fully functional SSP after this change, let me know! I love success stories.

Removing Cached SCSM Console View

It was a dark and stormy night. A man, seemingly like any other man, sat down and launched the SCSM console locally. However, he was not an ordinary man and this was not an ordinary night. You see, he was a victim of the “Corrupted Cached View Curse” and whenever he opens a ticket assigned to him, the tasks are all collapsed like so:

Image

What a nightmare! His life has changed forever! Or, has it?

Luckily, a hero enters the room. This beautiful man in tight, powder blue spandex is Registry Man.

“Fear not,” he yelled, “for I am Registry Man. Simply delete the following key (after backing it up, of course) to fix your issues.”

Scared, the man calmly asked Registry Man to stop yelling…it wasn’t making the issue any easier. He then closed the console and deleted everything UNDER HKEY_CURRENT_USER\Software\Microsoft\System Center\2010\Service Manager:

Image

Image

Once he launched the console again, he had to reconnect to the management server…a small price to pay for the convenience of a refreshed console view:

Image

“Thanks, Registry Man!” said the citizen. But Registry Man was nowhere to be found, as he was off attacking corruption in other workstations.

What a true hero and fashion genius.

Forcing ETL Jobs in SCSM 2012 SP1

After a recent upgrade from SCSM 2010 to 2012 SP1 (which was done by none other than Jason Rutherford), I noticed that there were a couple Data Warehouse ETL (Extract, Transform, Load) jobs that were hanging. I determined that this was most likely due to an abundance of data that had been waiting to process, as the previous environment was unhealthy, which caused the jobs to timeout.

During my scouring of the interwebs looking for PowerShell commands, I came across a thread by the brilliant Travis Wright. As Travis constantly demos Service Manager, he found that waiting for the jobs to run according to their default setting (which is adaptable, but that’s beside the point) was holding back his progress. So, he wrote a script to constantly process the jobs until they report back as complete.

Bingo! This is exactly what I needed to ensure that the jobs would not timeout. So, I downloaded the script, changed a few variables to fit my environment and ran it against my jobs. A little bit later, all jobs were complete and all data had been processed!

Below is a link to the script. Hopefully, you find this handy for either your lab or production environment.

http://gallery.technet.microsoft.com/PowerShell-Script-to-Run-a4a2081c

(It is important to note that this script will only run in SCSM 2012)

Moving Service Manager’s 2010 SP1 Database – Summary

Recently I had an opportunity to upgrade a service manager 2010 sp1 environment to 2012 Sp1, easy right?…Right? Okay so there may be some challenges, but people like Travis Wright make the world of Service Manager easier. As many of us know the Service Manager Team Blog is a wealth of knowledge. One such nugget of information is the Moving the Service Manager Database Blog Post where much of the information I was looking for was found.

Here are the high-level steps…

1. Gather the Service Manager Management Group Name and SQL Server Database Server Name.
2. Stop Service Manager Services on the Management Servers and the Data Warehouse Server
3. Stop the Website and the Application Pool on the Server hosting the Self Service Portal
4. Back up the ServiceManager database on the old SQL Server.
5. Take the ServiceManager database offline on the old SQL Server.
6. Restore the ServiceManager database on the new SQL server.
7. Prepare the ServiceManager database on the new database server
8. Update management servers with the new database server name.
9. Back up the DWStagingandConfig database
10. Update the DWStagingandConfig database.
11. Start Service Manager Services on the Management Servers and Data Warehouse Server
12. Start the Website and Application Pool on the server hosting the Self Service Portal

In the coming weeks I’ll detail some of the gotcha’s with the move and the SCSM 2010 SP1 on SQL 2008 to SCSM 2012 SP1 on SQL 2008R2 SP1 upgrade process.