Eliminating "duplicate" rows

[_smp_]

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 

[ITWhisperer · Accepted solution]

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_]

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!