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?
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"
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
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
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")
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
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
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.
_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?