Thanks for your responses!
Yes I'll have to think about what _time I want the result represented at.
FYI those coalesces are broken under some circumstances, due to how strptime doesn't seem to initialise timeptr so the result is either relative to current time or affected by the most recent call to strptime (I can't work it out) and so yes I get several large negative values on a large dataset. I think this would be a problem with strptime() reglardless of the coalesce().
A more reliable method is to break up the string manually with substr and tonumber() each component into submission!:
host=myhostindex=os source=ps "java"
| eval host_dot_PID=host + "." + PID
| eval intCPUTIME = tonumber(substr(CPUTIME,-2,2))+tonumber(substr(CPUTIME,-5,2))60+tonumber(substr(CPUTIME,-8,2))*60*60+if(match(CPUTIME,"-"),tonumber(replace(CPUTIME,"-.",""))24*60*60,0)
| eval intELAPSED = tonumber(substr(ELAPSED,-2,2))+tonumber(substr(ELAPSED,-5,2))*60+tonumber(substr(ELAPSED,-8,2))*60*60+if(match(ELAPSED,"-"),tonumber(replace(ELAPSED,"-.",""))*24*60*60,0)
| streamstats window=2 current=true global=false count(_time) as rowcount earliest(intCPUTIME) as startCPUTIME latest(intCPUTIME) as endCPUTIME earliest(intELAPSED) as startELAPSED latest(intELAPSED) as endELAPSED by host_dot_PID
| where rowcount=2
| eval CPUSINCE = endCPUTIME - startCPUTIME
| eval ELAPSEDSINCE = endELAPSED - startELAPSED
| eval CPUAVG=100*CPUSINCE/ELAPSEDSINCE
| timechart span=5mins limit=0 avg(CPUAVG) by host_dot_PID
... View more