Splunk Search

subtraction using eval

ChhayaV
Communicator

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?

Tags (1)

siraj198204
Explorer

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

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma

sowings
Splunk Employee
Splunk Employee

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".

0 Karma

kristian_kolb
Ultra Champion

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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
New Member

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

0 Karma

to4kawa
Ultra Champion
. . .
| 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.

0 Karma

chiennylin
New Member

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

0 Karma

to4kawa
Ultra Champion

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

please convert both to UNIX epoch time.

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

ChhayaV
Communicator

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

0 Karma

ChhayaV
Communicator

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

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