How can my search query show all duplicated events by field?

[EricG1793]

I'm trying to search data from our Infoblox switch port capacity source, and there are many interfaces that have an identical last_changed_at value. I want to see all events that have a duplicate last_changed_at value. The search, with an attempt at using stats count, is below. The problem with this is that it only shows one event for each last_changed_at value. It also only shows the last_changed_at value, all other fields blank. Any examples of how to accomplish this would be much appreciated!

source=ib:discovery:switch_port_capacity index=ib_discovery device_ip_address=10.64.16.1* port_last_changed_at>="2017-02-07 00:00:00" interface_port_status=down interface_type=ethernet-csmacd interface_name!="Gi1/1/*" interface_name!="Te*" is_trunk_port=no | fillnull value="N/A" | dedup network_view, device_ip_address, interface_name | stats count by port_last_changed_at | rename network_view as "Network View" InterfaceSubnet as "Network" device_ip_address as "Device IP" interface_ip_address as "Interface IP" interface_name as "Interface Name" interface_description as "Interface Description" device_model as "Device Model" device_vendor as "Device Vendor" device_version as "Device OS Version" device_type as "Device Type" device_name as "Device Name" is_trunk_port as "Trunk Port" interface_type as "Type" interface_speed as "Speed" interface_vlan as "Vlan ID" interface_vlan_name as "Vlan Name" interface_admin_status as "Admin Status" interface_port_status as "Operation Status" port_last_changed_at as "Last port Changed" | table "Device IP" "Device Name" "Interface Name" "Operation Status" "Last port Changed" "Vlan ID"

[elliotproebstel · Accepted solution]

EDIT: Converted to an answer, as it meets the requirement of only expecting there to be one group of results with the same port_last_changed_at value.

source=ib:discovery:switch_port_capacity index=ib_discovery device_ip_address=10.64.16.1* port_last_changed_at>="2017-02-07 00:00:00" interface_port_status=down interface_type=ethernet-csmacd interface_name!="Gi1/1/*" interface_name!="Te*" is_trunk_port=no
| eventstats count AS plc_count BY port_last_changed_at
| where plc_count > 1
| fillnull value="N/A" 
| dedup network_view, device_ip_address, interface_name
| rename network_view as "Network View" InterfaceSubnet as "Network" device_ip_address as "Device IP" interface_ip_address as "Interface IP" interface_name as "Interface Name" interface_description as "Interface Description" device_model as "Device Model" device_vendor as "Device Vendor" device_version as "Device OS Version" device_type as "Device Type" device_name as "Device Name" is_trunk_port as "Trunk Port" interface_type as "Type" interface_speed as "Speed" interface_vlan as "Vlan ID" interface_vlan_name as "Vlan Name" interface_admin_status as "Admin Status" interface_port_status as "Operation Status" port_last_changed_at as "Last port Changed"
| table "Device IP" "Device Name" "Interface Name" "Operation Status" "Last port Changed" "Vlan ID"

[EricG1793]

Yes, this produces exactly what I'm looking for! Thanks so much!

I'd like to accept your answer as a solution, but since it's a comment, I can't. Feel free to re-post as a separate answer, and I will accept it.

[elliotproebstel]

Thanks! Glad we got it sorted out. I converted the comment to an answer. Cheers!

[elliotproebstel]

I am trying to parse out the search query you posted here, but I can't understand how anything useful is happening after this:

| stats count by port_last_changed_at 

That should leave you with a table with only two fields: port_last_changed_at and count. So how you could do any renaming and useful tabling after that is not clear to me.

Based on your description of the goal, you might try this:

source=ib:discovery:switch_port_capacity index=ib_discovery device_ip_address=10.64.16.1* port_last_changed_at>="2017-02-07 00:00:00" interface_port_status=down interface_type=ethernet-csmacd interface_name!="Gi1/1/*" interface_name!="Te*" is_trunk_port=no 
| eventstats count BY port_last_changed_at
| where port_last_changed_at > 1
| stats list(_raw) AS event BY port_last_changed_at

If that view doesn't suit you, you could try replacing

| stats list(_raw) AS event BY port_last_changed_at

with

| sort 0 port_last_changed_at

which won't technically group the events together, but it will list them adjacently.

[EricG1793]

Elliott - The fact that nothing useful is happening is the very reason I'm asking how it should be done, so thank you for your detailed response - much appreciated! Unfortunately, with the search built that way, no results are found. Here's a screenshot showing this, and another of all the data with an example of the duplicates I want to show outlined.

[elliotproebstel]

Just to make sure I understand what's going on under the hood - do you also get 0 results when you run only this:

source=ib:discovery:switch_port_capacity index=ib_discovery device_ip_address=10.64.16.1* port_last_changed_at>="2017-02-07 00:00:00" interface_port_status=down interface_type=ethernet-csmacd interface_name!="Gi1/1/*" interface_name!="Te*" is_trunk_port=no 
| eventstats count BY port_last_changed_at
| where port_last_changed_at > 1
| stats list(_raw) AS event BY port_last_changed_at

Don't add any renames or tables or anything - just see if that alone is already junking it up. Let me know what the output is, and I'll try to help from there.

[elliotproebstel]

Oh dear, I see the issue now. Sorry. Try this instead:

 source=ib:discovery:switch_port_capacity index=ib_discovery device_ip_address=10.64.16.1* port_last_changed_at>="2017-02-07 00:00:00" interface_port_status=down interface_type=ethernet-csmacd interface_name!="Gi1/1/*" interface_name!="Te*" is_trunk_port=no 
 | eventstats count AS plc_count BY port_last_changed_at
 | where plc_count > 1
 | stats list(_raw) AS event BY port_last_changed_at

[EricG1793]

We're getting closer! Now, I see 27 events, all with the same port_last_changed_at. Awesome! However, they're only showing as events, not as statistics; again, no results found - so I can't export a spreadsheet from the info. 😕 If I get rid of the renames and tables, I get 1 row with the redundant value in the port_last_changed_at column, and all the events that occurred at that time in the next (event) column.

source=ib:discovery:switch_port_capacity index=ib_discovery device_ip_address=10.64.16.1* port_last_changed_at>="2017-02-07 00:00:00" interface_port_status=down interface_type=ethernet-csmacd interface_name!="Gi1/1/*" interface_name!="Te*" is_trunk_port=no
| eventstats count AS plc_count BY port_last_changed_at
| where plc_count > 1
| stats list(_raw) AS event BY port_last_changed_at
| fillnull value="N/A" 
| dedup network_view, device_ip_address, interface_name
| rename network_view as "Network View" InterfaceSubnet as "Network" device_ip_address as "Device IP" interface_ip_address as "Interface IP" interface_name as "Interface Name" interface_description as "Interface Description" device_model as "Device Model" device_vendor as "Device Vendor" device_version as "Device OS Version" device_type as "Device Type" device_name as "Device Name" is_trunk_port as "Trunk Port" interface_type as "Type" interface_speed as "Speed" interface_vlan as "Vlan ID" interface_vlan_name as "Vlan Name" interface_admin_status as "Admin Status" interface_port_status as "Operation Status" port_last_changed_at as "Last port Changed"
| table "Device IP" "Device Name" "Interface Name" "Operation Status" "Last port Changed" "Vlan ID"