subtraction using eval

[ChhayaV]

Hi,

I have two fields : In-Time and Out-Time
Here is some sample entries

In-Time Out-Time

8:33 17:39

8:44 17:45

8:83 17:50

Here i wanted to subtract Out-Time with In-Time and display the result as new field

I tried with the below query:

host="sample" | eval Newfield=(Out_Time - In_Time) | table Newfield

But if i do so ,its not generating the output(showing blank).Is it because of the ":" in between the time ? how this can be done?

[siraj198204]

Hi ,
source="dbmon-tail://idwarehouse/idw_account" application=TFAYD [|inputlookup execSSO.csv |rename sso as owner] |eval exp_date=strftime(relative_time(strptime(lastPasswordChange,"%Y-%m-%d %H:%M:%S"),"+90d"), "%Y-%m-%d %H:%M:%S") |eval CurrentTime=strftime(now(), "%Y-%m-%d %H:%M:%S") |rex field=exp_date "(?\d+) (?\d+):(?\d+)" | rex field=CurrentTime "(?\d+) (?\d+):(?\d+)" | eval in_s = (in_d *86400)+(in_h * 3600) + (in_m * 60) | eval out_s = (out_d * 86400)+(out_h * 3600) + (out_m * 60) | eval diff = in_s - out_s |eval tot_d = floor(diff/86400) |eval diff = diff - (tot_d * 86400) | eval tot_h = floor(diff/3600) | eval diff = diff - (tot_h * 3600) |eval tot_m = (diff/60) | eval duration = tot_d . ":" . tot_h . ":" . tot_m

I tried the above query , it is working good , but month wise result is wrong .

i want to minux two field , it is exp_date - CurrentTime

my output ,
CurrentTime =2014-10-09 13:19:55
application =TFAYD
duration =15:5:43
exp_date =2014-12-24 19:02:04
in_d =24
in_h =19
in_m =02
in_s =2142120
lastPasswordChange =2014-09-25 19:02:04
out_d =09
out_h =13
out_m =19
out_s =825540
owner =206046611
refreshedDate =2014-10-09 12:26:05
sourcetype =mysql
tot_d =15
tot_h =5
tot_m =43

in_d - out_d , 24 -9 = 15 (tot_d) .... but it is wrong ...

2014-12-24 19:02:04 (exp_date) - (CurrentTime ) 2014-10-09 13:19:55 .... it is only doing minus ... 24-9 ... not month wise ...

Thank u ...

Regards,
Siraj

[kristian_kolb]

You'll have to do it manually I think. According to the docs, mstime() will deal with MM:SS.SSS, not with HH:MM (which is what I suspect you are using).

... | rex field=In_Time "(?<in_h>\d+):(?<in_m>\d+)" 
| rex field=Out_Time "(?<out_h>\d+):(?<out_m>\d+)" 
| eval in_s = (in_h * 3600) + (in_m * 60) 
| eval out_s = (out_h * 3600) + (out_m * 60) 
| eval diff = out_s - in_s
| eval tot_h = floor(diff/3600)
| eval diff = diff - (tot_h * 3600)
| eval tot_m = diff/60 
| eval Duration = tot_h . ":" . tot_m

Or you could do some clever manipulation with convert dur2sec(field) and eval;

...| convert dur2sec(In_Time) dur2sec(Out_Time) 
| eval dur_in_sec = Out_Time - In_Time
| eval Duration = strftime(dur_in_sec, "%H:%M")

Which I have not tested. Techically Duration would be a timestamp sometime in Januray 1st, 1970 - but since you only extract the hour:minute part, that would be irrelevant.

Hope this helps (and that it works),

/K

[sowings]

I don't think strftime is correct, either: try "| stats count | eval foo=strftime(1, "%H:%M")". I'm in eastern daylight time (-0500), and I get "19:00".

[kristian_kolb]

Hi MuS! Yup, seems right. Took me a few minutes to write that down. tostring() also seems like a more intuitive idea than strftime().

/k

[MuS]

Hi /k, see my update came up with the same in the end - dur2sec works and mstime() fails because of the missing MM:SS.SSS format

[MuS]

Hi ChhayaV

you can use the convert command on Out_Time and In_Time to do your eval, take a look at the examples in docs.
After some testing and proofing, this works for me:

host="sample" | convert dur2sec(Out_Time) dur2sec(In_Time) | eval diff=(Out_Time - In_Time) | eval Newfield=tostring(diff, "duration") | table Newfield

This was tested and it will convert Out_Time and In_Time into second values, the eval will substract it and the last eval will revert it into HH:MM format.

hope it works and helps

cheers, MuS

[chiennylin]

Help, my eval subtraction is not working:
| convert ctime(_time) AS time
| eval TID=if(Type=="Inbound",obj_type,corrID)
| eval inboundTime=if(Type=="Inbound",time,null())
| eval outboundTime=if(Type=="Outbound",time,null())
| eval ResponseTime=strptime(outboundTime,"%Y%m%d %H:%M:%S.%N")-strptime(inboundTime,"%Y%m%d %H:%M:%S.%N")
| stats values(inboundTime) AS starttime values(outboundTime) AS endtime values(ResponseTime) as ResponseTime by TID

i even tried
| eval ResponseTime=outboundTime-inboundTime

but there's no output

[to4kawa]

. . .
| stats values(inboundTime) AS starttime values(outboundTime) AS endtime values(ResponseTime) as ResponseTime by TID

this result is starttime , endtime , ResponseTime and TID

| eval ResponseTime=outboundTime-inboundTime

both fields are nothing.

[chiennylin]

the start and end have time. but when i do this: | eval ResponseTime=outboundTime-inboundTime
there is no result.
here's the sample:
TID starttime endtime ResponseTime
0b44ffc9-8e92-44a0-b487-da9acba0bc52 01/21/2020 00:12:45.168 01/21/2020 00:12:45.362

0d501b27-ad34-4481-bc16-7c029baa8bec 01/21/2020 00:10:56.951 01/21/2020 00:10:57.293

there's no response time by simply eval ResponseTime=outbound-inbound

[to4kawa]

your starttime and endtime are both STRING because | convert ctime(_time) AS time
String cannot be calculated.

please convert both to UNIX epoch time.

[MuS]

yes we can and see my update, did some tests and this works fine for me.

[ChhayaV]

Can we use two convert statements in a single search query (as we have used in the above example)?

[ChhayaV]

Yes,tried with the same thing but itime column values are coming blank.