Splunk Cloud

time difference not working with min and max values

Loves-to-Learn Lots

I am trying to calculate the difference between 2 times which have been converted to strftime but the duration is not working and i think its to do with min and max stats. I have the below:

 

mysearch | stats count by Location Action Type a_rid _time
| stats list(Action) as action, list(Type) as type, list(_time) as time by Location a_rid Action
| eval times=strftime(time,"%H:%M:%S")
| eval total=mvzip(times, type)
| mvexpand total
| makemv total delim=","
| eval value1=mvindex(total, 0)
| eval value2=mvindex(total, 1)
| stats min(value1) as begins, max(value1) as ends by Location a_rid
| eval duration=ends-begins

 

I get the below results

 

n Location a_rid begins ends
1 AssetRepository 63d71f3f-4a6c-4447-9c37-828062493f68 09:23:53	09:23:53
2 ContributionRepository 63d71f3f-4a6c-4447-9c37-828062493f68 09:23:54 09:23:54
3 FinancialSummaryController 63d71f3f-4a6c-4447-9c37-828062493f68 09:23:53 09:23:54

 

This all works apart from the duration where it doesnt appear. Can you advise if there is another way round this to get the duration to work?

Labels (1)
Tags (1)
0 Karma

Champion

hope below query is helpful.

index=_internal 
| stats min(_time) as min_time max(_time) as max_time by sourcetype
| eval duration=max_time-min_time
| convert ctime(*_time)  timeformat="%d/%m/%Y %T"
————————————
If this helps, give a like below.
0 Karma

Loves-to-Learn Lots

I did try like this but it still doesnt pull through the duration

Tags (1)
0 Karma

Path Finder

It is more likely to be the strftime than the min and max (your results show at least 1 event with different min and max). Either remove the eval times=strftime and use time in the mvzip, or reparse (strptime) the ends and begins back into epoch times before subtracting them

You could consider fieldformat on ends and begins so they are displayed the way you want

0 Karma

Loves-to-Learn Lots

I tried updating it to this but it still doesnt work

 

| stats count by Location Action Type a_rid _time
| stats list(Action) as action, list(Type) as type, list(_time) as time by Location a_rid Action
| eval total=mvzip(time, type)
| mvexpand total
| makemv total delim=","
| eval value1=mvindex(total, 0)
| eval value2=mvindex(total, 1)
| stats min(time) as begins, max(time) as ends by Location a_rid
| eval start=strptime(begins,"%H:%M:%S")
| eval finish=strptime(ends,"%H:%M:%S")
| convert timeformat="%H:%M:%S" ctime(begins) AS start
| convert timeformat="%H:%M:%S" ctime(ends) AS finish
| eval duration=finish-start

 

0 Karma

Path Finder

Try like this

mysearch | stats count by Location Action Type a_rid _time
| stats list(Action) as action, list(Type) as type, list(_time) as time by Location a_rid Action
| eval total=mvzip(time, type)
| mvexpand total
| makemv total delim=","
| eval value1=mvindex(total, 0)
| eval value2=mvindex(total, 1)
| stats min(value1) as begins, max(value1) as ends by Location a_rid
| eval duration=ends-begins
| eval start=strftime(begins,"%H:%M:%S")
| eval finish=strftime(ends,"%H:%M:%S")
0 Karma

Loves-to-Learn Lots

Thanks but the problem im still getting is as per the below

 

Location a_rid begins ends duration finish start
FinancialSummaryController 4a0ae068-ec51-42d7-b4df-53e83fd6053e 1600069138.668	1600069142.214 3.546 08:39:02 08:38:58

 

as my time is coming out with it in all numbers its just doing the calculation on the numbers rather than the actual timeformat 

0 Karma

Path Finder

begin and start are the same and ends and finish are the same just presented differently

if you only want one of them use

mysearch | stats count by Location Action Type a_rid _time
| stats list(Action) as action, list(Type) as type, list(_time) as time by Location a_rid Action
| eval total=mvzip(time, type)
| mvexpand total
| makemv total delim=","
| eval value1=mvindex(total, 0)
| eval value2=mvindex(total, 1)
| stats min(value1) as begins, max(value1) as ends by Location a_rid
| eval duration=ends-begins
| eval begins=strftime(begins,"%H:%M:%S")
| eval ends=strftime(ends,"%H:%M:%S")

or 

mysearch | stats count by Location Action Type a_rid _time
| stats list(Action) as action, list(Type) as type, list(_time) as time by Location a_rid Action
| eval total=mvzip(time, type)
| mvexpand total
| makemv total delim=","
| eval value1=mvindex(total, 0)
| eval value2=mvindex(total, 1)
| stats min(value1) as begins, max(value1) as ends by Location a_rid
| eval duration=ends-begins
| eval start=strftime(begins,"%H:%M:%S")
| eval finish=strftime(ends,"%H:%M:%S")
| fields - begins ends
0 Karma

Loves-to-Learn Lots

unfortunately neither of these work for me as the one that does give a result gives me a result of 2.754 so its calculating from a number rather than the date as the eval for duration is before the time and if you put it after it doesn't work. 

0 Karma

Path Finder

_time is a number representing the number of seconds since the beginning of 1970. The difference in two time values (min(value1) and max(value1) i.e. begins and ends) is the number of seconds between these two times - let's call that duration e.g. 2.754 seconds. What were you expecting as your duration?

0 Karma