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

[pench2k19]

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

[chrisyounger]

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

[pench2k19]

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