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.

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:

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

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.

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.

Red branch: “++Occurrence”

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

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”}’

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:

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

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.

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.

Child Runbook: Monitor Problem

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”}’

Check the database

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

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.

Related articles

• Automating Incident & Problem management. Part I: Creating a SCOM monitor and generating fake alerts
• Automating Incident & Problem management – Part II: Configuring the SCOM – SCSM connector
• Automating Incident & Problem management – Part III: Using Orchestrator to remediate the issue (Counter edition)