Automating Incident & Problem management – Part IV: Replacing Counters with Database tables


Continuing this series, I will provide a more reliable and flexible replacement mechanism for counters: database tables. I am not going again through all the activities in the runbook, since most of them are already described in Part III. I will only focus on the four new activities which replace the counter-based activities from the previous version of the runbook.

Replace Orchestrator Counters with Database

Before going into details about the changes inside the runbook, a table needs to be created on the SQL database. For my demo, I created a new database called “LABDB”. Inside this DB, I created a table called “IRAutomation”, containing the following columns:

  • ProblemActive(bit)
  • ProblemGUID(varchar)
  • IncidentOccurrence(tinyint)
  • IncidentName(PK,varchar)

Any new incident generated by SCOM alerts and matching the filters inside the SCOM-SCSM connector will be registered in this table, along with the number of occurrences (sorted in the “IncidentOccurrence” column). If a new incident with the same title is triggered, the number of occurrences will increase. Once it reaches a value equal or greater than 5, a problem will be generated. Solving the problem resets the “IncidentOccurrence” filed to 0.

Check IR

You need to check whether the incident was already registered to the custom database or not. Use the “Query Database” activity, configure the connection to the SQLServer where the DB was created and input the following query:

FROM [LABDB].[dbo].[IRAutomation]
WHERE [IncidentName] = ‘{Title from “Get IR”}’

query database

Green branch: “Register new IR”

If the number of rows returned by the query is 0, it means this is the first time the incident was triggered.

query database link condition

Proceed and register the new incident, using the “Write to Database” activity. Use the Title from “Get IR” to fill the “IncidentName” field and change the value for “IncidentOccurrence” to 1.

write to database

Red branch: “++Occurrence”

If the number of rows returned by the query “Check IR” is 1, it means the incident was triggered before.

number of rows equals 1

Increase the number of occurrences by 1. The “Write to Database” activity allows only to insert new rows, so in order to do an update on existing rows, you can use the “Query Database” activity.

UPDATE [LABDB].[dbo].[IRAutomation]
SET IncidentOccurrence += 1
WHERE IncidentName = ‘{Title from “Get IR”}’

plus occ

Red branch: “Check # Occurrences”

Next, you need to check the number of occurrences reached by an incident . Use again the “Query Database” activity and type the following query:

FROM [LABDB].[dbo].[IRAutomation]
WHERE [IncidentName] = ‘{Title from “Get IR”}’ AND IncidentOccurrence >=5

select from database

If the number of rows returned by the query is 0, it means the incident occurrence is bellow 5. The flow goes up to the green branch, without creating/updating a problem.

link condition after query database

If the number of rows returned by the query is 1, it means that the incident occurrence is equal to or greater than 5. The runbook continues with checking weather or not the problem was already created.

query db link condition

Child Runbook: Monitor Problem

monitor problem and update incident

Since counters were deprecated in this version of the runbook, it’s also necessary to replace them inside child runbooks as well.

Query database

Once the problem is  solved/closed the incident occurrence is set to 0. Use again the “Query Database” activity to do an “UPDATE”.

UPDATE [LABDB].[dbo].[IRAutomation]
SET IncidentOccurrence = 0
WHERE IncidentName = ‘{IncidentName from “Initialize Data”}’

Query DB in child runbook

Check the database

After generating some alerts, the “IRAutomation” table should look like this:

sqlserver table

As I said at the beginning of this series, this is not a “production ready” solution. I tried to show as many Orhcestrator features.capabilities as possible, and from time to time to recommend some additional developments that would make this solution better. I might use this runbook as a base for future posts, and add to it more and more features and improvements that are required in production runbooks.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s