Orchestrator SQL Query to get Runbook running-time

Lately, we had a Requirement, at one of our Customers, to alert someone, if a Runbook is longer running than 30 minutes. There is a way to write an Event Log if a Runbook is running longer than XX Minutes, but this has to be configured for each single Runbook, so that wasn’t a good Solution.

So I have created a small SQL Query for that, which I would like to present to you. feel Free to use.

 

The Script

select 
Policies.name,
ins.TimeStarted,
DateDiff(second, ins.TimeStarted, GETUTCDATE()) as Totalseconds ,
DateDiff(second, ins.TimeStarted, GETUTCDATE()) / 3600 as Hours, 
(DateDiff(second, ins.TimeStarted, GETUTCDATE()) % 3600) / 60 as Minutes, 
DateDiff(second, ins.TimeStarted, GETUTCDATE()) % 60 as Seconds

from POLICYINSTANCES as Ins
inner join POLICIES on Ins.PolicyID=POLICIES.UniqueID

 where Ins.Status is null and Name not like '%-MON-%'
order by Totalseconds desc

The Result

image

Some Information

The SQL Script is excluding all Runbooks which contain “-MON-“, cause this are the Monitoring Runbooks, we don’t want to alert, cause they are running all the time.

We are calculating the totalseconds, and additionally the hours, minutes and seconds as a Result.

 

Michael Seidl aka Techguy

Leave a Comment

Your email address will not be published. Required fields are marked *

*