Splunk Search

Conditional transaction

Engager

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
0 Karma
1 Solution

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

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

View solution in original post

SplunkTrust
SplunkTrust

updated line 15 to by groupno mygroup

0 Karma

SplunkTrust
SplunkTrust

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

0 Karma

Engager

It's needed for further analysis.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!