Find what jobs were running on a runbook server

A question came up from a team-mate, how do I see what jobs were running on a runbook server in SMA at some point in time? To answer the question we made a nice little sql query

Find out what runbooks ran for a given time window on a given runbook worker box:

DECLARE @low INT, @high INT, @host nvarchar(255),@start datetime, @end datetime
 
--Which host do you want to see jobs on?
SET @host = 'MGOAPSMAP2'
--What time range do you want to see jobs for?
set @start = '2014-11-20 11:13:00'
set @end = '2014-11-20 14:35:00'
 
SELECT @low = LowKey, @high = HighKey
FROM [SMA].[Queues].[Deployment]
WHERE ComputerName = @host
 
select r.RunbookName,
count(*) as RunCount
from sma.core.vwJobs as j
inner join [SMA].[Core].[RunbookVersions] as v
on j.RunbookVersionId = v.RunbookVersionId
inner join [SMA].[Core].[Runbooks] as r
on v.RunbookKey = r.RunbookKey
where PartitionId > @low and PartitionId @start
and StartTime < @end
group by r.RunbookName

Advertisements
Posted in Uncategorized
One comment on “Find what jobs were running on a runbook server
  1. mdedeboer says:

    Nice!

    I’ve made a few small changes and one functionality change. It appears your Where statement has a few words missing:

    DECLARE @low INT, @high INT, @host nvarchar(255),@start datetime, @end datetime
    
    --Which host do you want to see jobs on?
    SET @host = 'MGOAPSMAP2'
    --What time range do you want to see jobs for?
    SET @start = '2014-01-01 12:01:00'
    SET @end = '2015-12-31 23:59:00'
     
    SELECT @low = LowKey, @high = HighKey
    FROM [SMA].[Queues].[Deployment]
    WHERE ComputerName = @host
    
    select r.RunbookName,
    j.JobStatus,
    j.StartTime,
    j.EndTime
    from sma.core.vwJobs as j
    inner join [SMA].[Core].[RunbookVersions] as v
    on j.RunbookVersionId = v.RunbookVersionId
    inner join [SMA].[Core].[Runbooks] as r
    on v.RunbookKey = r.RunbookKey
    where PartitionId > @low and PartitionId < @high and StartTime > @start
    and StartTime < @end
    group by r.RunbookName, j.JobStatus, j.StartTime, j.EndTime
    ORDER BY STartTime DESC
    

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: