Need help when using variable instead of constant in splunk query construct within where and eval

iTechEvent — Fri, 31 Jan 2014 06:39:16 GMT

I have a query Q1 which is used to collect avg over 10 days.Say the average is AvgQ1 100. I have another query Q2 which collects 5 min samples over 2 different weekend days for the avg. We have say a few values for avg say AvgQ2: 50,60,70,80,90,100,110,120,130,140,150 for example. I want to check how many values in this exceed the avg found above as 100. Things are okay when I have 2 queries and do in manually. But I have to automate this. So I have to use variables than hardcoded values. For example

The normal way to check when values are exceeded in Q2 results above is:

| stats count(eval(AvgQ2 >= 100)) as countoverlimit -->This works when I do it manually

| stats count(eval(AvgQ2 >= AvgQ1 )) as countoverlimt --> When this is constant is treated as variable to automate it, I have issues, because splunk gives error for that construct

The other way is to use different construct

| where AvgQ2 > 100 | stats count as countoverlimit --> This works with hard coded value

| where AvgQ2 > AvgQ1 | stats count as countoverlimit --> When this constant is treated as variable to automate it, there seem issues, since splunk gives error for that construct.

Lets say when you store in a csv file and use it in Q2, it will look like this.

sourcetype="yoursourcetypewiththenumbers" [| inputlookup AvgQ1.csv | fields AvgQ1 ] --> This works as usual and depends on where you use it. This will become the below and works.

sourcetype="yoursourcetypewiththenumbers" 100

But what if want to use this within where clause?

| where AvgQ2 > AvgQ1 . Inside where clause if I used the above construct, it doesnt work since splunk will give error.

| where AvgQ2 > [| inputlookup AvgQ1.csv | fields AvgQ1 ]. Also this wont work either since its within where clause

The same with eval

| stats count(eval(AvgQ2 >= [| inputlookup AvgQ1.csv | fields AvgQ1 ] )) as countoverlimt --> This wont work either.

Other options are to store these values in 2 different csv files. Either as csv or lookup usage.

Then I can also combine the results into 1 csv or lookup file to simplify it. The first line will be from Q1 result. The rest will be from Q2 result. I can reference the first value as first(AvgQ1). But I cant use this as variable in splunk eval and where constructs as above because it has a variable construct rather than hardcoded value.

Here is the combined csv file with values.

time,AvgQ1,AvgQ2
t1,100,
t2, ,50
t3, ,60
t4, ,70
, ,
, ,
tx , , 100
ty , , 150

How would the query be written to get number of times AvgQ2 values is over AvgQ1?

The result should be 110,120,130,140,150 which is count of 5?

Any help will be appreciated. I hope I can get this to automate.

Re: Need help when using variable instead of constant in splunk query construct within where and eval

martin_mueller — Fri, 31 Jan 2014 20:42:20 GMT

How do you - as a human - determine the number of times AQ2 has been greater than AQ1 in your example table at the bottom of the post? I see no timestamp with both values set, so I can't make a comparison in my head - that's before trying to make Splunk do it for me.

Re: Need help when using variable instead of constant in splunk query construct within where and eval

somesoni2 — Fri, 31 Jan 2014 22:59:59 GMT

Try this

<your query 2 giving field AvgQ2, multiple events> | table AvgQ2 | eval joinfield=1
| join joinfield [search <your query 1 giving single value AvgQ1> | table AvgQ1 | eval joinfield=1]
| fields - joinfield | where AvgQ2 > AvgQ1

This should give you all the AvgQ2 values which are higher than AvgQ1. To get count your can further append "| stats count".

Re: Need help when using variable instead of constant in splunk query construct within where and eval

iTechEvent — Sun, 02 Feb 2014 08:32:13 GMT

I tried it and it works great. Thanks for your help.

topic Re: Need help when using variable instead of constant in splunk query construct within where and eval in Splunk Search

Need help when using variable instead of constant in splunk query construct within where and eval

Re: Need help when using variable instead of constant in splunk query construct within where and eval

Re: Need help when using variable instead of constant in splunk query construct within where and eval

Re: Need help when using variable instead of constant in splunk query construct within where and eval