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
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