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

pench2k19 — Tue, 29 Sep 2020 23:06:36 GMT

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

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

chrisyounger — Thu, 07 Feb 2019 10:20:55 GMT

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

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

pench2k19 — Thu, 07 Feb 2019 11:03:01 GMT

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

topic Re: need help in finding the time differece btween two fields when thid field value is null in Splunk Search

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

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

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