Transaction and summary indexing, average duration

[guilhem]

Hello everyone!

I am currently trying to use summary indexing using the si- commands. It is working well for "simple" request (medians, distinct counts of user etc etc...)

But I am now stuck on something and after 2 days thinking on how to do it I didn't reach any kind of conclusion. Here is the problem:

I have a search like this:

index=XXX "some random filters" 
| transaction ID maxpause=30m keepevicted=false keeporphans=false
| stats avg(duration)

I want to run this query using summary indexing si- commands, like this, run it every hour.

 index=XXX "some random filters" 
    | transaction ID maxpause=30m keepevicted=false keeporphans=false
    | sistats avg(duration)

And then request the index using:

index=summary | stats avg(duration)

Several things come up when thinking on what is happening:

1°) a transaction that span over (eg) 1h45m between 4AM and 6AM will be cut in half, then "summarized" once in the 4AM-5AM search and then the remaining will be "summarized" inside the 5AM-6AM range, leading to a compeltly false average:

4AM first last event first event last event
event occuring in this hour 5AM occuring in this hour 6AM
-|---------S-----------------------------------------L---------------|-------------F--------------------------------E---------|
4h05 4h45 5h10 5h50

Ultimatly I want to store the real duration of the transaction, which is 105mn, but as I understand summary indexing, it will store two values: 40m between 4AM and 5AM and 40mn between 5AM and 6AM, leading to a wrong average

2°) Some sessions can span over several days, with many interruption (here comes the maxspause=30m), that means that some sessions must be counted several time in the average (one time for each "transaction split"), so I don't want to take the min and max time for a session and then compute the average of this duration as it will be false too.

As an exemple in the upside chronology, if the last event in the 4-5AM had occured at 4h25, I want to store two values because the maxpause would have been more than 30 mn:

20m for the 4AM-5AM hour, and then 40mn for the 5AM-6AM hour, so the average would have been 30mn, if I had used the min/max time for a session, I would have end up with 105mn in one session, which is not want I want in this case.

3°) here are some links that I have read but doesn't really adress this particular problem:
transaction count and summary index
transaction count over 2 hour on 5m search schedule
transaction keep field

I am really not sure if it is clear, I can give further explanations and sample test to clarify if needed. Furthermore I am totally open to alternative ways of speeding this query up, and also using something else than transaction (I mainly use this because I need the maxpause to split inactive sessions into several)

Any help would be much appreciated.

Guilhem

[guilhem · Accepted solution]

So I have finally solved this problem, but I had to add more event logic client side. I have added an event that fire after 5minutes of inactivity, and it is collected in the same index. So now basically what I do is a (somewhat complex) search that just takes only into account the session which has this event in the hour the search is currently running on.

This way I can't get duplicates, and I don't have to agregates sessions that spans over several hours, because I am running the search on something like -24h, and join only with the ones that have the endSession event in the hour I am looking for.

Running this every hours gives the real session length. final search looks like this:

index=XXX "some random filters" action=endSession
| eventstats max(_time) as maxTime by session userId
| join userId [search index=XXX earliest=-24h | stats min(_time) as minTimeby session]
| eval duration=maxTime-minTime
| stats avg(duration)

Thanks very much for the help!

[dart]

You could store the value for each transaction, then on the reporting side do two sets of stats:

index=XXX "some random filters" 
| transaction ID maxpause=30m keepevicted=false keeporphans=false | table ID duration | collect index=summary

Then

index=summary | stats sum(duration) as duration by ID | stats avg(duration)

[guilhem]

Thanks for the answer!

I think I understand what you are suggesting here, but as I store the transaction, wouldn't I loose all the benefits of summary indexing, if I need to perform the sum+avg on all the transaction on the reporting side?

I guess it will be faster than rerunning every time the transaction though.

I will test this solution and see if it's suitable for the traffic we have.

Guilhem

PS: I cannot make the "timeline" display nicely for all browser, so I recommend looking at it using firefox ^^.

[guilhem]

Hum, I have just checked the transaction maxpause field, and in fact it will discard transaction with pauses that are greater that maxpause, it will not split them and make two transaction out of it, which is not really what I was thinking, anyway the problem still hold.