Splunk Enterprise

Comparing search results to lookup?

cmcdole
Path Finder

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. 

Labels (2)

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

cmcdole
Path Finder

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

0 Karma

Tom_Lundie
Contributor

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
Path Finder

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

0 Karma

cmcdole
Path Finder

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

Get Updates on the Splunk Community!

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...

Observability protocols to know about

Observability protocols define the specifications or formats for collecting, encoding, transporting, and ...

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...