Comparing search results to lookup?

[cmcdole]

I am trying to create a query to compare thousands of thresholds given in a lookup without having to hardcode the thresholds in eval statements.  Example 

Query:

index=abc | stats count field1 as F1, field2 as F2, field3 as F3, field4 as F4

Lookup: (thresholds.csv)

	Val1	Val2	Val3	Val4
Threshold1	15	50	60	60
Threshold2	52	75	85	95

Condition: if ((F1>Val1 AND F2>VAL2 AND F3>Val3 AND F4>Val4), "Violation", "Compliant")

Hopefully this makes sense. 

[ITWhisperer]

Do you need to assess each stats event (returned by the stats command) with every threshold setting in the csv or just one? If one, how do you identify which one?

[cmcdole]

I have to compare against each threshold....literally thousands 🙂

[Tom_Lundie]

This is a really interesting question!

I think this will be the most efficient solution:

    ``` Assume no events have violated the threshold. ``` 
| eval violated_threshold = 0
    ``` Pass all the events into an appendpipe to search across them without dropping any events. ``` 
| appendpipe 
    [| search
        ``` Use a subsearch to return a "search" field to use to filter all of the threshold_violations. ```
        ``` The search must be in this format: (F1 > Val1 AND F2 > Val2 AND F3 > Val3 AND F4 > Val4 ) OR (F1 > Val1 AND F2 > Val2 AND F3 > Val3 AND F4 > Val4 ) OR ... ``` 
        [| inputlookup thresholds.csv 
        | eval search = "(F1 > " + Val1 + " AND F2 > " + Val2 + " AND F3 > " + Val3 + " AND F4 > " + Val4 + ")" 
        | table search 
        | stats values(search) as search 
        | eval search = mvjoin(search, " OR ")
            ]
        ``` Any events that are returned from the above search meet the threshold critra (therefore they have violated the threshold in OPs parameters. ``` 
    | eval violated_threshold = 1 
        ``` Mark these events to be dropped later so that there are no duplicates. ``` 
    | eval drop=1] 
    ``` Take the max violated threshold for each event by _raw, only violated events wil have been appended to the current results. ``` 
| eventstats max(violated_threshold) as violated_threshold by _raw
    ``` Drop the duplicated events. ``` 
| where isnull(drop) 
    ``` Remove the drop field ``` 
| fields - drop
    ``` Apply field as per OPs post. ``` 
| eval violated_threshold = case(violated_threshold=0, "compliant", violated_threshold=1, "violated")

It looks a bit messy in the code block but if you copy this into a Splunk Search the inline comments should help explain what's going on.

There is no way to have dynamic eval statements in Splunk but you can have dynamic searches. This solution pipes off the results into an appendpipe (so you can search them without losing them from the main search) and then searches across them for the "violations" e.g. where all of the fields are larger than those thresholds. The results are then appended back into the main search branch, the violated_threshold field is extrapolated back to the original search results via an eventstats command and then the duplicated events are dropped from the main search.

This solution relies on each result (event) having a unique identifier (in this case I am assuming the _raw field will work) If you don't have a _raw field at this stage in your search then you you can replace _raw with any unique property for each event. If you don't have a unique property then make one with:

| streamstats count as id

[cmcdole]

I'll research this and let you know if it works for me or not.  Give me a couple days.

[cmcdole]

This is just an update to say that I haven't left you hanging.  I'm still looking into this.