splunk query help

[surekhasplunk]

index=ABC Check!=D 
| stats count by Device Check

I am using this query and getting Device and Related Checks reported by the device.

Now i have a scenario where i need to show only those devices where the Check!=D
And i have data like below

Device Check
india_1 A
india_1 D
india_2 B
india_3 B
india_3 C
india_4 A
india_4 D

output i want is only below 2 devices where D is not there in the check although we have other names in the Check.
Device count
india_2 1
india_3 2

please help me with the query

[gcusello · Accepted solution]

Hi @surekhasplunk,
you cannot filter events in the main search because, in this way, you cannot identify the Devices to exclude, try something like this:

 index=ABC 
| stats values(Device) AS Device count by Check
| where Check!=D 
| mvexpand Device

You need of mvexpand command because you could have more than one value in Device field.

Ciao.
Giuseppe