Compare and filter table results

[bertzela]

Given the table below:

VIP Group State
Primary_VIP Group1 Down
Backup_VIP Group1 Down
Primary_VIP Group3 Down
Backup_VIP Group4 Down

How can I filter the results to show only the events where both Primary and Backup VIPs are down in same group?

e.g.
I'd like to keep just:
VIP Group State
Primary_VIP Group1 Down
Backup_VIP Group1 Down

[somesoni2 · Accepted solution]

Give this a try

your current search giving current output with fields VIP Group State
| eval score=case(like(VIP,"Primary%") AND State="Down",1,like(VIP,"Backup%") AND State="Down",2, true(),0)
| eventstats sum(score) as score by Group 
| where score=3
| fields - VIPs score