Extracting monitoring data from the operations database

Run SQL queries against the operations database to extract monitoring data.

Extract the names of all the jobs running on a specific engine

This SQL query extracts the names of all the jobs running on a specific engine.

Run this SQL query:

SELECT 
       H.HostName
     , J.ProjectName
     , J.JobName
     , R.InvocationId 
FROM   
       DSODB.JobExec AS J
     , DSODB.JobRun as R
     , DSODB.Host as H
WHERE  H.HOSTID = J.HOSTID
  AND  R.JOBID = J.JOBID
  AND  R.RunMajorStatus = 'STA'   -- i.e. status = “started and running” --
  AND  H.HostName = 'HOSTNAME'
;

Extract the full status of all the jobs started after a certain time on any host

This SQL query extracts the full status of all the jobs started after a certain time on any host.

Run this SQL query:

SELECT 
       H.HostName
     , J.ProjectName
     , J.JobName
     , R.InvocationId
     , S.MajorStatusName
     , R.RunStartTimeStamp 
FROM  
       DSODB.JobExec AS J
     , DSODB.JobRun AS R
     , DSODB.Host AS H
     , DSODB.RunMajorStatusRef AS S
WHERE  H.HOSTID = J.HOSTID
  AND  R.JOBID = J.JOBID
 	 AND  R.RunMajorStatus = S.MajorStatusCode
  AND  R.RunStartTimeStamp >= '2011-09-26'
ORDER BY R.RunStartTimeStamp
;

Extract the details of all jobs ever run, showing the run type as readable string

This SQL query extracts the details of all jobs ever run, showing the run type as readable string.

Run this SQL query:

SELECT 
       H.HostName
     , J.ProjectName
     , J.JobName
     , R.InvocationId
     , R.RunStartTimeStamp 
     , T.RunTypeName            -- code converted to a readable name --
FROM  
       DSODB.JobExec AS J
     , DSODB.JobRun AS R
     , DSODB.Host AS H
     , DSODB.RunTypeRef AS T
WHERE  H.HOSTID = J.HOSTID
  AND  R.JOBID = J.JOBID
  AND  R.RunType = T.RunTypeCode
ORDER BY R.RunStartTimeStamp
;

Extract the job run logs for a particular job run

This SQL query extracts the job run logs for a particular job run.

Run this SQL query:

SELECT
       L.LogTimestamp
     , T.LogTypeName           -- code converted to a readable name --
     , L.MessageId
     , L.MessageText
FROM
       DSODB.JobExec AS J
     , DSODB.JobRun AS R
     , DSODB.JobRunLog AS L
     , DSODB.LogTypeRef AS T
WHERE  J.ProjectName = 'projectname'
  AND  J.JobName = 'job1name'
  AND  R.InvocationId = 'id'
  AND  R.JOBID = J.JOBID
  AND  L.RUNID = R.RUNID
  AND  L.LogType = T.LogTypeCode
  AND  R.CreationTimestamp > '2011-09-26'
ORDER BY L.EventId
;

Extract the details of all the job runs with a particular parameter set to a given value

This SQL query extracts the details of all the job runs with a particular parameter set to a given value.

Run this SQL query:

SELECT 
       H.HostName
     , J.ProjectName
     , J.JobName
     , R.InvocationId
     , R.RunStartTimeStamp 
     , S1.MajorStatusName
     , S2.MinorStatusName
     , R.ElapsedRunSecs
FROM  
       DSODB.JobExec AS J
     , DSODB.JobRun AS R
     , DSODB.Host AS H
     , DSODB.JobRunParamsView AS P
     , DSODB.RunMajorStatusRef AS S1
     , DSODB.RunMinorStatusRef AS S2
WHERE  H.HOSTID = J.HOSTID
  AND  R.JOBID = J.JOBID
  AND  R.RUNID = P.RUNID
  AND  P.ParamName = 'paramname' AND P.ParamValue = 'paramvalue'
  AND  R.RunMajorStatus = S1.MajorStatusCode
  AND  R.RunMinorStatus = S2.MinorStatusCode
ORDER BY R.RunStartTimeStamp
;

Extract the details of all job runs that were active after a given time on a particular host

This SQL query extracts the names of all the jobs running on a specific engine.

There are three sets of job runs that meet these criteria:

Job runs that started after the given time
Job runs that finished after the given time
Job runs that are still running

These three sets of jobs are identified by the final condition in the WHERE clause, which are OR’ed together because they might overlap.

Run this SQL query:

SELECT 
       R.RUNSTARTTIMESTAMP 
     , R.INVOCATIONID
     , J.PROJECTNAME
     , J.JOBNAME
 FROM  
       DSODB.JOBRUN AS R
     , DSODB.JOBEXEC AS J
     , DSODB.HOST AS H
 WHERE 
       R.JOBID = J.JOBID 
   AND J.HOSTID = H.HOSTID 
   AND H.HOSTNAME = 'HOSTNAME'
   AND R.CREATIONTIMESTAMP >= '2011-09-26'
    OR R.RUNENDTIMESTAMP >= '2011-09-26'
    OR R.RUNENDTIMESTAMP IS NULL
	;

Extract the details of all job runs that were active in a given period on a particular host

This SQL query extracts the names of all the jobs running on a specific engine.

There are three sets of job runs that meet these criteria:

job runs that started in the given period
job runs that ended in the given period
job runs that started before the end of given period and that are still running

These three sets of jobs are identified by the final condition in the WHERE clause, which are OR’ed together because they might overlap.

Run this SQL query:

SELECT 
       R.RUNSTARTTIMESTAMP 
     , R.INVOCATIONID
     , J.PROJECTNAME
     , J.JOBNAME
 FROM  
       DSODB.JOBRUN AS R
     , DSODB.JOBEXEC AS J
     , DSODB.HOST AS H
 WHERE 
       R.JOBID = J.JOBID 
   AND J.HOSTID = H.HOSTID 
   AND H.HOSTNAME = 'HOSTNAME'
   AND ( R.CREATIONTIMESTAMP >= '2011-09-26'
     AND R.CREATIONTIMESTAMP <= '2011-09-27' )
    OR ( R.RUNENDTIMESTAMP >= '2011-09-26'
     AND R.RUNENDTIMESTAMP <= '2011-09-27' )
    OR ( R.CREATIONTIMESTAMP <= '2011-09-27'
     AND R.RUNENDTIMESTAMP IS NULL )
	;

Extract the slowest jobs in a project based on their last runs

This SQL query extracts the slowest jobs in a project based on their last runs.

Run this SQL query:

SELECT 
       J.JOBNAME
     , MAX(R.RUNSTARTTIMESTAMP) AS LATESTRUN
     , MAX(R.ELAPSEDRUNSECS)    AS MAXTIME
 FROM  
       DSODB.JOBRUN AS R
     , DSODB.JOBEXEC AS J
     , DSODB.HOST AS H
 WHERE 
       R.JOBID = J.JOBID
   AND J.HOSTID = H.HOSTID
   AND J.PROJECTNAME = 'projectname'
   AND H.HOSTNAME = 'HOSTNAME'
 GROUP BY J.JOBNAME
 ORDER BY MAXTIME DESC
 ;