check for clashing events on a channel

[alexm2a]

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.

[FrankVl · Accepted solution]

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.

[alexm2a]

This is soooooo helpful! Thank you very much!

[alexm2a]

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