[SOLVED] Splunk: How to apply conditionals for multiple rows with same column value?

Splunk: How to apply conditionals for multiple rows with same column value?

I have got a table with columns in the following format where host_names are repeated and a single host can have both Compliant and Non-Compliant values against it. How can i write a query which checks for each host_name and marks it as Non-Compliant if any of its rows has Non-Compliant.

compliance  host_name
Compliant   Host1
Non-Compliant   Host1
Compliant   Host2
Non-Compliant   Host3
Compliant   Host4

For ex: in the above table, Host1 has both Compliant and Non-Compliant values in two of its rows. Since one of the value is non-compliant, i want to take that host once and create a table in following format.

compliance  host_name
Non-Compliant   Host1
Compliant   Host1
Non-Compliant   Host3
Compliant   Host4

Solution

To show only non-compliant hosts, add | where compliance="Non-Compliant" to your query.

To see the hosts which are non-compliant or both compliance and not, try this run-anywhere example query.

|  makeresults 
|  eval _raw="compliance  host_name
Compliant   Host1
Non-Compliant   Host1
Compliant   Host2
Non-Compliant   Host3
Compliant   Host4" 
| multikv forceheader=1
```Everything above just sets up test data```
```Next, combine compliance values by host```
| stats values(compliance) as compliance by host_name
```Show only those that are non-compliant or both compliant and non-compliant```
| where (mvcount(compliance)>1 OR compliance="Non-Compliant")