Splunk Search

check for clashing events on a channel

alexm2a
Engager

Hi there,

Apologies in advance for this question. I'm a beginner learning Splunk and I can't for the life of me figure out how the syntax for this would look.

I have multiple sets of data a bit like this:

activity_id:     1131c134-d771-41e7-918d-d42772fc1316    
        date_time:     2018-02-13T08:21:40.682844+00:00    
        env:     prod    
        event_data:    {    [-]    
          channel:     1124    
          day:     2018-02-18    
          eventId:     97356218    
          streamEndDateTime:     1518974100000    
          streamStartDateTime:     1518965640000    
       }    
        event_name:     update.event
        timestamp:     1518510100682    

And I need a query that will check whether 2 or more events will clash based on the event_data.channel and the event_data.streamEndDateTime and event_data.streamEndDateTime.

So if the event_data.channel AND event_data.streamStartDateTime is in the range between event_data.streamStartDateTime and event_data.streamEndDateTime of a matching event_data.channel , then show results.

Any help would be greatly appreciated.

Tags (1)
0 Karma
1 Solution

FrankVl
Ultra Champion

Try this:

<your base search here>
| table activity_id channel day streamEndDateTime streamStartDateTime 
| eval slot=activity_id+","+streamStartDateTime+","+streamEndDateTime 
| eventstats values(slot) as otherslots by channel,day
| mvexpand otherslots 
| rename otherslots as otherslot
| where slot!=otherslot
| eval otherslot=split(otherslot,",")
| eval otherStart=mvindex(otherslot,1)
| eval otherEnd=mvindex(otherslot,2)
| where ((streamStartDateTime>=otherStart AND streamStartDateTime < otherEnd) OR (streamEndDateTime>otherStart AND streamEndDateTime <= otherEnd)) OR (streamStartDateTime<=otherStart AND streamEndDateTime >=otherEnd)
| eval clashed_activity_id = mvindex(otherslot,0)
| table activity_id channel streamStartDateTime streamEndDateTime clashed_activity_id otherStart otherEnd

It basically adds the ID Start and End of all other events on the same channel and day in a multivalued field and then expands that, such that you basically get on entry for each combination of events on a channel. You can then filter those for combinations that clash. I'm not promising this is the most efficient way of doing this, but at least it worked, when i tested it with some simple samples. There may be simpler and more efficient ways of generating this set of pairs of events on the same channel.

View solution in original post

0 Karma

FrankVl
Ultra Champion

Try this:

<your base search here>
| table activity_id channel day streamEndDateTime streamStartDateTime 
| eval slot=activity_id+","+streamStartDateTime+","+streamEndDateTime 
| eventstats values(slot) as otherslots by channel,day
| mvexpand otherslots 
| rename otherslots as otherslot
| where slot!=otherslot
| eval otherslot=split(otherslot,",")
| eval otherStart=mvindex(otherslot,1)
| eval otherEnd=mvindex(otherslot,2)
| where ((streamStartDateTime>=otherStart AND streamStartDateTime < otherEnd) OR (streamEndDateTime>otherStart AND streamEndDateTime <= otherEnd)) OR (streamStartDateTime<=otherStart AND streamEndDateTime >=otherEnd)
| eval clashed_activity_id = mvindex(otherslot,0)
| table activity_id channel streamStartDateTime streamEndDateTime clashed_activity_id otherStart otherEnd

It basically adds the ID Start and End of all other events on the same channel and day in a multivalued field and then expands that, such that you basically get on entry for each combination of events on a channel. You can then filter those for combinations that clash. I'm not promising this is the most efficient way of doing this, but at least it worked, when i tested it with some simple samples. There may be simpler and more efficient ways of generating this set of pairs of events on the same channel.

0 Karma

alexm2a
Engager

This is soooooo helpful! Thank you very much!

0 Karma

alexm2a
Engager

Having thought about this a bit more, i believe the first step would be to find all the duplicate channels for that day using event_data.day, then work from there. If only i know how to do that. Sort of like a negative dedup...

I've tired

| where event_data.channel=event_data.channel 

but that doesn't work

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...