Splunk Search

How to find the time difference in hours between the _time of two different fields ?

pavanae
Builder

I'm going crazy of calculating the difference between two fields which has epoch time. The following is my Query

Updated :-

foo | convert ctime(_time) as Date_and_Time|convert timeformat="%m/%d/%Y %H:%M:%S" mktime(_time) as time |eventstats range(time) as duration by user| stats avg(duration) as avgDurationPeruser by user| eval Total_time_spent(out_of_school)=tostring(ceil(avgDurationPeruser), "duration") | table user Total_AccessTime Total_time_spent(out_of_school)

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi pavanae,
sorry but I don't understand your question: the eval command correctly runs and gives the number of days between now() and the event's _time.
In addition I don't understand the last "if" of your search, because it's incomplete.

Bye.
Giuseppe

View solution in original post

0 Karma

gcusello
SplunkTrust
SplunkTrust

Hi pavanae,
sorry but I don't understand your question: the eval command correctly runs and gives the number of days between now() and the event's _time.
In addition I don't understand the last "if" of your search, because it's incomplete.

Bye.
Giuseppe

View solution in original post

0 Karma

pavanae
Builder

Sorry for posting the wrong Query. please find the updated Query. @cusello

0 Karma

gcusello
SplunkTrust
SplunkTrust

Which are the fields to calculate difference?
every way, you have to transform both the fields in epochtime

| eval time1=strptime(time1,"%Y-%m-%d %H:%M:%S"), time2=strptime(time2,"%Y-%m-%d %H:%M:%S") | eval diff=time1-time2

speculating that time format is %Y-%m-%d %H:%M:%S
_time is already in epochtime.

Bye.
Giuseppe

0 Karma

pavanae
Builder

Thanks @cusello. What if i have something as below

| convert ctime(a) timeformat="%H:%M" | convert ctime(stdev) timeformat="%H:%M" | convert ctime(y) timeformat="%H:%M"

How can i have the difference of y and a interms of %H:%M?

0 Karma

gcusello
SplunkTrust
SplunkTrust

You can use

| eval y=tostring(y,"duration") 

Bye.
Giuseppe

0 Karma

mrgibbon
Contributor
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.