Hello,
I have two types of events: clicks and searches.
I want to group two searches into a transaction if
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
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
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
updated line 15 to by groupno mygroup
What is the use case for only joining pairs? If bar and baz should be combined, why not bar, baz, bak and ban?
It's needed for further analysis.