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.
Procedure
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.
Procedure
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.
Procedure
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.
Procedure
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.
Procedure
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.
About this task
- Job runs that started after the given time
- Job runs that finished after the given time
- Job runs that are still running
Procedure
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.
About this task
- 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
Procedure
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.
Procedure
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
;