Dashboards & Visualizations

Need help in getting output in table format

harsush
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
2/13/17
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
2/13/17
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

somesoni2
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
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...