splunksplunk-querylog-analysis

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")