Conditional transaction

[ablake1]

Hello,

I have two types of events: clicks and searches.
I want to group two searches into a transaction if

1. they don't have any other events in between
2. they are within 5 seconds from each other

Input:

time=1505404370 query=foo type=search
time=1505404371 query=foo type=click
time=1505404372 query=bar type=search
time=1505404373 query=baz type=search
time=1505404374 query=bak type=search
time=1505404375 query=ban type=search

Output:

time=1505404372 query=bar type=search
time=1505404373 query=baz type=search
--------------------
time=1505404374 query=bak type=search
time=1505404375 query=ban type=search

[DalJeanis · Accepted solution]

Here's one way.

| rename COMMENT as "Sort in ascending order, copy time and previous type to each new record."
| sort 0 _time
| streamstats current=f window=1 last(_time) as prevtime last(type) as prevtype

| rename COMMENT as "It is a new group if it is the first group, if type changes, or if there had been 5 seconds."
| eval groupchange=case(isnull(prevtype),1, prevtype!=type,1, _time-prevtime>5,1, true(),0)

| rename COMMENT as "Determine the group number, kill groups that aren't search"
| streamstats sum(groupchange) as groupno
| eval groupno = if(type="search",groupno,null())

| rename COMMENT as "Add up the members of each group, pair them off, keep only pairs"
| streamstats count as countoff by groupno
| eval mygroup = floor((countoff+1)/2,0)
| eventstats count as paircheck by groupno mygroup
| where paircheck=2

[DalJeanis]

updated line 15 to by groupno mygroup

[DalJeanis]

What is the use case for only joining pairs? If bar and baz should be combined, why not bar, baz, bak and ban?

[ablake1]

It's needed for further analysis.