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.
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.
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.
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.