Dashboards & Visualizations

Need help in getting output in table format

Path Finder

Pls find below search and sample alerts. I am looking for below output.
Job Name Completion-Time Last-Completion-Time Delay(Completion-Time-Last-Completion-Time)
AJOB Not Yet completed 02-13-2017 9:40

BJOB Not Yet completed 02-13-2017 9:40

CJOB Not Yet completed 02-13-2017 9:40

Would you pls help on below
1) If job hasn't run for today it should show " Not yet completed"
2) How to run same search to get last Last-Completion-Time ( Previous days run)
3) How to get the difference from Previous days run & today's run ( if today's run is not yet completed it should show empty)

host=Host1* OR host=Host2* U0011502 | rex "U0011502 Workflow '(?(?[^.]+)\.[^.]+\.[^.]+\.(?[^.]+\.[^.]+)[^']+)" | search env=CERT (job=AJob OR job= BJob OR job= C) | eval desired_time=strftime(_time, "%d/%m/%Y %I:%M:%S %p") | table job desired_time | sort desired_time | rename job as "UC4 Job"|  rename desired_time as "Completion-Time"

Sample alert :-
Time Event
8:30:40.866 PM 20170213/203040.866 - U0011502 Workflow 'AJob' (RunID '11111') ended normally.
host = host2 source = /xxx/uc4/AutomationEngine/temp/abc.txt sourcetype = uc4
6:29:06.744 PM 20170213/182906.744 - U0011502 Workflow 'BJob' (RunID '222222') ended normally.
host = host1 source = /M/zx/AutomationEngine/temp/def1.txt sourcetype = uc4

Tags (1)
0 Karma

Revered Legend

Give this a try (the rex commands are truncated, so update them)

host=Host1* OR host=Host2* U0011502 earliest=-1d@d latest=now | rex "U0011502 Workflow '(?(?[^.]+)\.[^.]+\.[^.]+\.(?[^.]+\.[^.]+)[^']+)" | search env=CERT (job=AJob OR job= BJob OR job= C) | stats values(_time) as desired_time by job 
|  eval "Completion-Time"=case(mvcount(desired_time)=1 AND mvindex(desired_time,0)>=relative_time(now(),"@d"), strftime(mvindex(desired_time,0), "%d/%m/%Y %I:%M:%S %p") ,mvcount(desired_time)=1 AND mvindex(desired_time,0)<relative_time(now(),"@d"),"Not Yet completed", mvcount(desired_time)=2,strftime(mvindex(desired_time,1), "%d/%m/%Y %I:%M:%S %p"),1=1,"Unknown") 
| eval "Last-Completion-Time"=case(mvcount(desired_time)=1 AND mvindex(desired_time,0)>=relative_time(now(),"@d"), null() ,mvcount(desired_time)=1 AND mvindex(desired_time,0)<relative_time(now(),"@d"),strftime(mvindex(desired_time,0), "%d/%m/%Y %I:%M:%S %p"), mvcount(desired_time)=2,strftime(mvindex(desired_time,0), "%d/%m/%Y %I:%M:%S %p"),1=1,"Unknown") 
| eval Delay=if('Completion-Time'="Not Yet completed",tostring(now()-mvindex(desired_time,0),"duration"),"NA")
| table job "Completion-Time" "Last-Completion-Time" Delay | rename job as "UC4 Job"
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!