How to merge two counts into one search

[power12]

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 

[ITWhisperer · Accepted solution]

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"

[power12]

@ITWhisperer  Thanks that worked.

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

index=abc ....| replace "ACTIVE to DOWN #012" with "ACTIVE to DOWN Count" IN State | replace "DOWN to INIT #012" with "DOWN to INIT Count" IN State
| chart count over Switch by State
| join
    [search index=abc  
| stats count by Switch port] 
| fields Switch port "ACTIVE to DOWN Count" "DOWN to INIT Count"