Splunk Search

need help in finding the time differece btween two fields when thid field value is null

pench2k19
Explorer

Hi Team,

Can you please help me with the solution for the following usecase.

i have three fields named as follows, i want to create another field named with now_sla_diff where we should calculate the difference between now_time and sla_time when the FILE_ARRIVALTIME value is null.

sla_time FILE_ARRIVALTIME now_time

2/6/2019 23:55 2/6/2019 23:02 2/7/2019 3:48
2/6/2019 23:55 2/7/2019 3:48

P S: i cant makresults command in my query as i m already joining two queries.

Thank you in advance.

@vnravikumar @jkat54

Tags (1)
0 Karma

chrisyounger
SplunkTrust
SplunkTrust

Try this: |eval now_sla_diff = if(isnull(FILE_ARRIVALTIME ), (now_time - sla_time),null())

pench2k19
Explorer

thanks for the quick response mate @chrisyoungerjds ...it works....the following is my udapted queery...somehow its not printing negative values in the time difference values. can you please help

| inputlookup SLA.csv  |rex field=SOR_TDQ_FAIL_SLA_THRESHOLD "(?P.*)\,(?.*)" | eval sla_time = case(date="BUS_DT+1",strftime(now(),"%Y-%m-%d").",".time,date="BUS_DT+0",strftime(relative_time(time(), "-d"),"%Y-%m-%d".",".time))|eval sla_time=replace (sla_time,","," ") |eval sla_time=sla_time + ":00" |table SOR_NAME FEED_NAME sla_time |dedup SOR_NAME FEED_NAME |join type=outer SOR_NAME FEED_NAME [search index=bp_ede_om_mbda source="XXXX.log" |rex "info\s:\s\+{4}\sSTARTED\s\+{4}\sJob run_ingest_(?\w+)_(?\d+-\d+-\d+-\d+-\d+-\d+)_"|rex field=Datafeed_name "^(?\w{2,5})_(?\w+)$" | eval FILE_ARRIVALTIME = strftime(strptime(start_time,"%Y-%m-%d-%H-%M-%S") ,"%Y-%m-%d %H:%M:%S") | eval FILE_ARRIVALTIME_epoch=strptime(FILE_ARRIVALTIME,"%Y-%m-%d %H:%M:%S") |fields SOR_NAME FEED_NAME FILE_ARRIVALTIME FILE_ARRIVALTIME_epoch] |eval now_time=strftime(now(), "%Y-%m-%d %H:%M:%S") | eval now_time_epoch = strptime(now_time,"%Y-%m-%d %H:%M:%S")| eval sla_time_epoch = strptime(sla_time,"%Y-%m-%d %H:%M:%S")| eval time_diff = tostring((FILE_ARRIVALTIME_epoch-sla_time_epoch), "duration")| eval arr_sla_diff=replace(time_diff,"(\d*)\+*(\d+):(\d+):(\d+)","\1 day(s) \2 hour(s) \3 minute(s) \4 sec(s)")|rex field=arr_sla_diff "^(?\d{1,2})\sday....(?\d{1,2})\shour...\s(?\d{1,2})\s"|eval dur_min=(day*1440+hour*60+minute)  |eval now_arr_diff=tostring((FILE_ARRIVALTIME_epoch-now_time_epoch), "duration")| eval now_arr_dif_day=replace(now_arr_diff,"(\d*)\+*(\d+):(\d+):(\d+)","\1 day(s) \2 hour(s) \3 minute(s) \4 sec(s)")|rex field=now_arr_dif_day "^(?\d{1,2})\sday....(?\d{1,2})\shour...\s(?\d{1,2})\s"|eval now_arr_min=(days*1440+hours*60+minutes) |eval now_sla_diff = if(isnull(FILE_ARRIVALTIME ), tostring(now_time_epoch - sla_time_epoch),null())|eval now_sla_diff = tostring(now_sla_diff, "duration")|table SOR_NAME FEED_NAME sla_time FILE_ARRIVALTIME  now_time   arr_sla_diff dur_min  now_arr_dif_day now_arr_min now_sla_diff
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...