Splunk Search

Conditional transaction

ablake1
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

DalJeanis
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

DalJeanis
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

DalJeanis
SplunkTrust
SplunkTrust

updated line 15 to by groupno mygroup

0 Karma

DalJeanis
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

ablake1
Engager

It's needed for further analysis.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...