Splunk Search

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

jbethmont
Explorer

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!

0 Karma
1 Solution

renjith_nair
SplunkTrust
SplunkTrust

@ 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

OR

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

View solution in original post

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@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

Happy Splunking!
0 Karma

jbethmont
Explorer

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 ?

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

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

Happy Splunking!
0 Karma

jbethmont
Explorer

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

@ 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

OR

"your search"| transpose |rename "row 1" as value|where value>100|"chart functions"
Happy Splunking!
0 Karma

jbethmont
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...