topic Re: Lowest single value from multiple fields in Dashboards & Visualizations

Lowest single value from multiple fields

epacke — Tue, 29 Sep 2020 15:33:19 GMT

Dear experts!
I have a sourcetype that contains fields like this:
domain_field1=5
domain_field2=5
domain_field3=4
domain_field4=3

And I want to display the lowest number available. To make it more complicated, the number of fields can differ, but they will always be prefixed with "domain_"

So in the example above the value for the search would be "3".

Is this possible?

Re: Lowest single value from multiple fields

cmerriman — Tue, 29 Aug 2017 13:13:59 GMT

try this:

...|foreach domain_* [|eval domain_all=min('<<FIELD>>')]

the foreach statement will grab any field beginning with domain_ and eval the minimum value for all fields. https://docs.splunk.com/Documentation/SplunkCloud/6.6.1/SearchReference/Foreach

Re: Lowest single value from multiple fields

epacke — Tue, 29 Aug 2017 13:37:56 GMT

Thank you! I tried this, but the result was still 5. Want to clarify that I need to evaluate only for the latest event. Did not know that foreach was possible, will check that out.

Re: Lowest single value from multiple fields

epacke — Tue, 29 Aug 2017 13:54:42 GMT

I tried this one:

... | foreach domain_* [|eval laggingdomains=laggingdomains + (5 - '<>')] | table laggingdomains

That should give me the amount of domains missing (every count below 5 is one missing domain).

However, the table only contains NULL events. Any idea what I'm doing wrong?

Re: Lowest single value from multiple fields

epacke — Tue, 29 Aug 2017 14:02:07 GMT

Need to declare the field first first:

... | eval laggingdomains=0 | foreach domain_* [|eval laggingdomains=laggingdomains + (5 - '<<FIELD>>')] | table laggingdomains

Re: Lowest single value from multiple fields

epacke — Tue, 29 Aug 2017 14:03:01 GMT

Thank you for your help. It was invaluable. 🙂

Re: Lowest single value from multiple fields

cmerriman — Tue, 29 Sep 2020 15:33:27 GMT

when i ran this:

|makeresults | eval domain_field1=5| eval domain_field2=5| eval domain_field3=4| eval domain_field4=3|foreach domain_* [|eval domain_all=min('<<FIELD>>')]

domain_all came back with 3.
can i see the query before you run the foreach command? are you doing a |stats latest(domain_*) as domain_* first since you only want the most recent results?

Re: Lowest single value from multiple fields

epacke — Tue, 29 Sep 2020 15:33:29 GMT

Here's the final query that I used:

Each domain that is lagging behind will increment the counter by 1.

/Patrik

Re: Lowest single value from multiple fields

black_bagel — Wed, 09 Jun 2021 15:13:09 GMT

Actually, @cmerriman's solution will not work as expected.

When I tried putting other values in the domain fields, domain_all gets the last value foreach sees, which just happens to be domain_field4 in cmerriam's example.

After doing a bit more digging, I managed to find a solution that in fact does return the minimal value no matter the field names' order.

This works like a classic "find the minimal value in a list" loop by always giving domain_all the smallest seen value.
In my opinion this is a better answer than the accepted answer, since you don't always know what the biggest value could be

Either way, I got the idea from epacke's accepted answer, so thanks for that 🙂

Re: Lowest single value from multiple fields

cmerriman — Wed, 09 Jun 2021 15:47:22 GMT

That's a good catch @black_bagel , but you don't have to eval domain_all before you do the foreach statement, you could just have

and that will still produce 1 for domain_all.