1

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
Ankit Vashistha
  • 325
  • 6
  • 17

1 Answers1

2

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")
RichG
  • 9,063
  • 2
  • 18
  • 29
  • Thanks a lot @RichG. It works just fine in my query. I just have one more query. How can i get the counts of total Non-Compliant and Compliant Hosts from the above. – Ankit Vashistha Oct 28 '20 at 09:31
  • Got it, i updated `| where (mvcount(compliance)<2 AND compliance="Compliant") | stats count by host_name | stats sum(count)` for Compliant counts and `| where (mvcount(compliance)>1 OR compliance="Non-Compliant") | stats count by host_name | stats sum(count)` for Non-Compliant counts. – Ankit Vashistha Oct 28 '20 at 12:36
  • One query though. Could you please explain the use of `| multikv forceheader=1` in your example query? – Ankit Vashistha Oct 28 '20 at 12:45
  • 1
    The `multikv` command extracts fields from events formatted as a table, as in the output of Linux commands like `ps`, `top`, and `netstat`. The `forceheader` option says to take the first row as field names. – RichG Oct 28 '20 at 13:07
  • Thank you for the explanation and thanks a lot for your answer. – Ankit Vashistha Oct 28 '20 at 15:55