Splunk Search

Eliminating "duplicate" rows

_smp_
Builder

I am trying to construct an alert for someone when there is a duplex-mismatch on our network switches. When it happens, both switches send an event that references their own interface, and the device and interface on the other side of the connection. So there are two events, but I only want a single alert.

Imagine conceptually there are two switches connected directly to each other and when there is a duplex mismatch, I get two events:

from host=host_a: there is a problem on interface_a, which is connected to host_b, interface_b
from host=host_b: there is a problem on interface_b, which is connected to host_a, interface_a

Each event extracted these three fields:
src_interface
dest_host
dest_interface

I'm trying to construct that would understand the correlation between these two events and trigger one alert. I want to include the body from both messages (message_text) in the text of the alert. And ideally, I would have some mechanism to determine which of the two events to trigger on, but I guess that's not a requirement at this point.

I'm staring at this |stats table, but I can't come up with any methodology to correlate the two events:

| stats values(message_text) as message_text by host, src_interface, dest_host, dest_interface 

 

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

You could start with something like this

| stats values(message_text) as message_text by host, src_interface, dest_host, dest_interface
| eval key=mvjoin(mvsort(mvappend(host, src_interface, dest_host, dest_interface)),":")
| stats list(message_text) as message_text list(host) as src_host list(src_interface) as src_interface list(dest_host) as dest_host list(dest_interface) as dest_interface by key

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

You could start with something like this

| stats values(message_text) as message_text by host, src_interface, dest_host, dest_interface
| eval key=mvjoin(mvsort(mvappend(host, src_interface, dest_host, dest_interface)),":")
| stats list(message_text) as message_text list(host) as src_host list(src_interface) as src_interface list(dest_host) as dest_host list(dest_interface) as dest_interface by key

_smp_
Builder
Yup, this did the trick. That's exactly the technique I had in mind, just couldn't figure out how to execute it. Thanks a lot for the assist!
0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...