topic Re: How to chart a list of key/value pair fields only if a condition matches? in Splunk Search

How to chart a list of key/value pair fields only if a condition matches?

jbethmont — Tue, 29 Sep 2020 21:05:01 GMT

Hi Splunk'az,

I have events composed of 64 key/value pairs that are being extracted into fields at indexing time:

"d" : {
"field01" : [ 0 ],
"field02" : [ 5 ],
"field03" : [ 2 ],
"field04" : [ 3 ],
[...]
"field64" : [ 38]
}

I would like to chart the "value" of the field "only if" it is above a certain threshold.

I was initially thinking using 'where':

| stats count last(field*) | where field* > 100

But the above doesn't work as 'where' can't contain a wildcard...
Then I was looking at the 'foreach' function. And trying something like:

| foreach c5x* [eval new_<>=if(<> > 0, <>, null)]
| table new_*

But the above doesn't work either, and still, I would have to get rid of the 'null' values from fields using a wildcard again. So the problem would remain the same.

So, how can I chart a set of fields values only if the value is above a certain threshold? And without having to hardcode to complete list of fields obviously 😉

Thanks in advance!

Re: How to chart a list of key/value pair fields only if a condition matches?

renjith_nair — Wed, 29 Aug 2018 15:44:13 GMT

@ jbethmont,

If you have only one value for each field, you could use transpose and do the operation
for e.g.

    "your search to get all fields and values"| transpose |rename "row 1" as value|stats count(eval(if(value>100,1,null()))) as count

"your search"| transpose |rename "row 1" as value|where value>100|"chart functions"

Re: How to chart a list of key/value pair fields only if a condition matches?

jbethmont — Wed, 29 Aug 2018 23:58:10 GMT

Thanks a lot!!! This is much more simpler using transpose!

Re: How to chart a list of key/value pair fields only if a condition matches?

jbethmont — Thu, 30 Aug 2018 00:20:29 GMT

Based on @renjith.nair 's comment the solution was to use the transpose function.

<search>
| stats last(field*) as field*
| transpose
| rename "column" as key "row 1" as value
| where value > 20

This works fine for a snapshot of the values at a given time (last events basically).
However, If I'd like to graph them over time, the issue remain the same as the table will be composed of multiple columns per time bucket. And I will need again to deal with a * in my where command.

alarm     value1    value2  value3  value4  value5
Time      10:33:23 AM   10:36:23 AM 10:36:28 AM 10:37:23 AM 10:37:28 AM
field01 43  46  47  47  48
field02 5    5    5  5   5
field03 2    2    2  2
[...]
field64     ....

Maybe a combination of transpose and foreach command ?

Re: How to chart a list of key/value pair fields only if a condition matches?

renjith_nair — Fri, 31 Aug 2018 15:42:44 GMT

@jbethmont,
In this case, how do you want to filter? For e.g. lets take field02 - you want to chart only if all the values of field02 is above threshold or at least one value ?
Forgot to mention that in transpose you could mention header_field="column_name"

Re: How to chart a list of key/value pair fields only if a condition matches?

jbethmont — Tue, 29 Sep 2020 21:06:36 GMT

Thanks for your help @renjith.nair
So if I go back to my initial question, I would like to chart the average value of a field02 only if this average is above a threshold.

So this works fine for 1 field (threshold is 70):
| stats avg(field02) as avg_c5x02 | where avg_c5x02 > 70

But this doesn't work if my field contains a wildcard:
| stats avg(field*) as avg_c5x* | where avg_c5x* > 70

Re: How to chart a list of key/value pair fields only if a condition matches?

renjith_nair — Tue, 04 Sep 2018 09:08:29 GMT

@jbethmont ,

Try this approach by assuming your data is in the below format where the numbers are "values"

 alarm     time1 time2 time3 
 field01    43    46    47 
 field02    5     5      5 
 field03    2     2      2

I tried to simulate this by creating a run anywhere example

index=_*|timechart span=1m count by sourcetype|untable _time,sourcetype,count|sort sourcetype|streamstats dc(sourcetype) as num global=t|eval Field="Field".num
|xyseries _time,Field,count|transpose header_field=_time|rename column as alert

If this matches our requirement, then we can go to the next step by adding

|untable alert,_time,count|eventstats avg(count) as avg by alert|where avg>70

So the final search would be

index=_*|timechart span=1m count by sourcetype|untable _time,sourcetype,count
|sort sourcetype|streamstats dc(sourcetype) as num global=t|eval Field="Field".num
|xyseries _time,Field,count|transpose header_field=_time|rename column as alert
|untable alert,_time,count|eventstats avg(count) as avg by alert|where avg>70

So to adapt your field names it would be

" your existing search to get the tabular format as mentioned above"|untable alert,Time,value|eventstats avg(value) as avg by alert|where avg>70