This is just a way to display NCM Scheduled Job stats with a bit more detail.
A version of this query is used to display the jobs on a timeline, which can be found here: Using Your Custom HTML Resource To View Events On A Timeline
The query is not perfect, so there may be some odd data that shows here and there. Not having a dedicated column to pull some of the dates/times, I had to tell the query to make an educated guess. So, I'm pretty sure things should work if all of the jobs have fired off automatically, at their regularly scheduled times. I'm also pretty sure you will see incorrect graphing if you manually run a job that repeats less than once per day. (If you manually fire off a weekly job, it will probably show up on the timeline with incorrect data.)
This is what the default NCM Jobs List page looks like:
Here is the SWQL query used to populate the custom query resource:
SELECT
NCMJobName
--We will use this to find the number of days between the nextdaterun and lastdaterun. This will be used to determine the laststart date/time.
--,DAYDIFF(NextDateRunUtc,LastDateRun) AS NewDiffDaysN --as a negative number
--,DAYDIFF(LastDateRun,NextDateRunUtc) AS NewDiffDaysP --as a positive number
--We take the "NewDiffDays" above, and place it in the following to determine when the most recently completed job started.
--I am using both a case statement and an isnull thingy as a workaround for jobs which are manually ran. In those cases, the start and end times will be the same, and the values will be incorrect.
,CASE WHEN ISNULL(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun)) > TOLOCAL(LastDateRun) THEN TOLOCAL(LastDateRun) ELSE ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)) END AS JobLastStart
,LastDateRun AS JobLastCompleted
--This is where we break the job duration down into hours, minutes, and seconds, then concat them all together for an easy to read time
,CONCAT(SECONDDIFF(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun))/3600,'h ',(SECONDDIFF(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun))%3600/60),'m ',(SECONDDIFF(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun))%60),'s') AS JobDuration
--This is where we show how many days until the next scheduled run time, followed by the datetime.
,CONCAT('This job is scheduled to run again in ',DAYDIFF(TOLOCAL(LastDateRun),TOLOCAL(NextDateRunUtc)),' days, on ',TOLOCAL(NextDateRunUtc)) AS NextScheduledStart
FROM Cirrus.NCM_NCMJobsView
WHERE Enabled='True' AND ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)) IS NOT NULL AND NextDateRunUtc IS NOT NULL AND LastDateRun IS NOT NULL
--We are sorting the values by hours, minutes, seconds, with the longest/highest times at the top.
ORDER BY SECONDDIFF(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun))/3600 DESC,SECONDDIFF(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun))%3600/60 DESC,SECONDDIFF(ADDDATE('DAY',DAYDIFF(TOLOCAL(NextDateRunUtc),TOLOCAL(LastDateRun)),TOLOCAL(NextDateRunUtc)),TOLOCAL(LastDateRun))%60 DESC
This is what the query results look like:
Nothing fancy, but it works well enough for me, so I figured I'd share.
Thank you,
-Will
