topic How to merge two counts into one search in Splunk Search

How to merge two counts into one search

power12 — Thu, 16 Feb 2023 18:40:20 GMT

Hello Splunkers,

I have the following raw data

2023-02-15T12:43:06.774603-08:00 abc OpenSM[727419]: osm_spst_rcv_process: Switch 0x900a84030060ae40 MF0;www:MQM9700/U1 port 29 changed state from DOWN to INIT #012

2023-02-15T12:42:02.861268-08:00 abc OpenSM[727419]: osm_spst_rcv_process: Switch 0x900a84030060ae40 MF0;www:MQM9700/U1 port 29 changed state from ACTIVE to DOWN #012

I am using the below regex

index=abc "ACTIVE to DOWN #012" host=ufmc-ndr* Switch IN(*) port IN(*) | stats count by Switch port | rename count as "ACTIVE to DOWN Count" | appendcols [search index=abc "DOWN to INIT #012" host=ufmc-ndr* Switch IN(*) port IN(*) | stats count by Switch port | rename count as "DOWN to INIT Count"] | sort - "ACTIVE to DOWN Count"

I am trying to count the total events with "ACTIVE TO DOWN" by switch and port and also for "DOWN TO INIT"...If i run the search separately I am getting the correct count but when I join both its not showing correct values .

I want to have A table panel with fields Switch port "ACTIVE to DOWN Count" " DOWN to INIT Count"

Thanks in Advance

Re: How to merge two counts into one search

ITWhisperer — Thu, 16 Feb 2023 19:04:48 GMT

Yet another case where appendcols is not the answer!

Try something like this

index=abc "ACTIVE to DOWN #012" OR "DOWN to INIT #012" host=ufmc-ndr* Switch IN(*) port IN(*) | stats count(eval(match(_raw, "ACTIVE to DOWN #012"))) as "ACTIVE to DOWN Count" count(eval(match(_raw, "DOWN to INIT #012"))) as "DOWN to INIT Count" by Switch port | sort - "ACTIVE to DOWN Count"

Re: How to merge two counts into one search

power12 — Thu, 16 Feb 2023 19:10:18 GMT

@ITWhisperer Thanks that worked.

I used join and it worked but yours is way simple.