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
SplunkTrust
SplunkTrust

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!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...