Maximo Open Forum

From time to time, we see the Crontask Instances for processing the outbound queues just stop executing on the defined schedule. Not sure why, but I thought it would be a good idea to create an Escalation for finding related Crontask Instances that are overdue for executing and define a new CommunicationTemplate to use for notification about these overdue Crontask Instances.

To my great frustration, Maximo does not allow creating Escalations for the CRONTASKINSTANCE object! Are there any good alternatives for implementing an overdue Crontask Instance notification scheme?

I suppose I could create a new Crontask that executes a custom Automation Script that does all of the checking and notifying. Does this community have other workable alternatives to achieve the same goal?

#Administration

------------------------------
Kind Regards,

Jared Schrag
Koch Ag & Energy Solutions
------------------------------

Hi Jared,

We have a report using the below query which tells us any cron task that hasn't run when it's supposed to. This report then emails to our Maximo admins to investigate. Would something like this work? You could probably split this query into a couple different escalations and create a notification from those, but we haven't played around with that too much.

select crontaskinstance.crontaskname, crontaskhistory.instancename, endtime, servername, right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)
from crontaskhistory
inner join crontaskinstance on crontaskhistory.instancename=crontaskinstance.instancename
where activity='ACTION' and active='1' and crontaskhistory.instancename=crontaskinstance.instancename
and endtime = (select max(endtime) from crontaskhistory where activity='ACTION' and instancename=crontaskinstance.instancename)
and endtime <= 
case when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)='s'
then dateadd(second,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) + 10), getdate())
when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)  COLLATE Latin1_General_CS_AS = 'm'  COLLATE Latin1_General_CS_AS
    then dateadd(minute,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) + 1), getdate())
when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)  COLLATE Latin1_General_CS_AS = 'h'  COLLATE Latin1_General_CS_AS
    then dateadd(hour,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) + 1), getdate())
when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)='d'
    then dateadd(day,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) +1), getdate())
when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)='w'
    then dateadd(week,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) +1), getdate())
when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)  COLLATE Latin1_General_CS_AS = 'M'  COLLATE Latin1_General_CS_AS
    then dateadd(month,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) +1), getdate())
when right(substring(schedule, 1, CHARINDEX(',', schedule)-1),1)='y'
    then dateadd(year,-1 * (substring(schedule, 1, CHARINDEX(',',schedule)-2) +1), getdate())
end

------------------------------
Griffin Kotarek
Kwik Trip, Inc.
------------------------------

This is my Oracle SQL. I compare the "should have run" (last run plus schedule) to system date.

 select CRONTASKNAME,INSTANCENAME,lastRun 
 ,lastRun + decode(substr(schedule,instr(schedule,',')-1,1),'d',substr(schedule,1,instr(schedule,',')-2), 
 'w',substr(schedule,1,instr(schedule,',')-2)*7,'h',substr(schedule,1,instr(schedule,',')-2)/24, 
 'm',substr(schedule,1,instr(schedule,',')-2)/1440,'s',1/1440, 
 'M',sysdate - add_months(sysdate, - substr(schedule,1,instr(schedule,',')-2)), 
 'y',sysdate - add_months(sysdate, - substr(schedule,1,instr(schedule,',')-2)*12), 
 0) should_have_run 
 ,decode(sign( sysdate - decode(substr(schedule,instr(schedule,',')-1,1),'d',substr(schedule,1,instr(schedule,',')-2), 
 'w',substr(schedule,1,instr(schedule,',')-2)*7,'h',(substr(schedule,1,instr(schedule,',')-2) + .5)/24, 
 'm',(substr(schedule,1,instr(schedule,',')-2) + 5)/1440,'s',5/1440, 
 'M',sysdate - add_months(sysdate, - substr(schedule,1,instr(schedule,',')-2)), 
'y',sysdate - add_months(sysdate, - substr(schedule,1,instr(schedule,',')-2)*12), 
 0) - decode(CRONTASKNAME,'DTERECEIPTSUPLOAD',2/24,0) 
 - lastrun),-1,'ok','ERROR') status 
 ,servername,substr(schedule,1,instr(schedule,'*,') -2) scheduled, description 
 from CRONTASKINSTANCE ,TASKSCHEDULER where ACTIVE =1 
 and TASKNAME(+) =  CRONTASKINSTANCE.CRONTASKNAME||'.'||INSTANCENAME 
 and CRONTASKNAME != 'REPORTSCHEDULE' and lastRun is not null 
 order by status, should_have_run  

------------------------------
James Beil
DTE Energy
------------------------------

I appreciate the suggestions. I've decided to simply create an Automation Script Cron Task that will query the targeted, overdue CRONTASKINSTANCE MBOs and use a configured Communication Template to send the notification.

------------------------------
Kind Regards,

Jared Schrag
Koch Ag & Energy Solutions
------------------------------