topic Re: Looping a "forecasting" column using stats in Splunk Search

Looping a "forecasting" column using stats

dojiepreji — Thu, 12 Dec 2019 10:23:32 GMT

Hello all,

I just cannot wrap my head around how splunk does looping.
Below is what I'm currently trying to do:

Percentage is calculated as (met / score) * 100.
In my "forecast" column, I need to show how many more I need to score to pass the minimum percentage of 90% (assuming that every ticket scored in the future will be met).

In order to solve this, I need to somehow generate a loop that produces this output:

Through this loop, I have determined that I needed a score of 15 more (due to 15 iterations) to pass the minimum of 90%.

Now I'm trying to produce this table using SPL but I have no idea where to start.
Assuming I'm able to generate the table, how can I "delete" the generated table and simply add '15' to my forecast column?

Any help would be greatly appreciated.

Thanks.

Re: Looping a "forecasting" column using stats

richgalloway — Thu, 12 Dec 2019 13:23:12 GMT

SPL is not a procedural language. It has looping commands ( foreach and map ), but they don't do what you require. In addition, there is no way to delete results once you create them.

I think there is a mathematical way to compute 'forecast' without having to count iterations. According to Google, for (x-2/x)*100=90 x equals 20. More generally, for (x-y/x)*100=90 x is 10y. In this case, the number of iterations would be (10*missed)-score.

Re: Looping a "forecasting" column using stats

dindu — Thu, 12 Dec 2019 15:52:35 GMT

Hi,

You could use the below method to calculate the no of iterations.
Need to supply the increment value for the score. I am taking it as one here.
Please test and let us know.

   |makeresults
   |eval _raw="
   score,met,missed,percentage,minimum,forecast
   5,3,2,60,90"
   |multikv forceheader=1
   |rename COMMENT AS "The below  code is the actual query" 

   |eval required_score=(100*missed)/(100-minimum)
   |eval increment=1
   |eval forecast=ceil((required_score-score)/increment)
   |table score,met,missed,percentage,minimum,required_score,forecast

Re: Looping a "forecasting" column using stats

woodcock — Thu, 12 Dec 2019 17:18:25 GMT

I am assuming that your raw data just has a met or missed indicator. If so, do it like this:

| makeresults
|  eval _raw="metORmissed
missed
missed
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met
met"
| multikv forceheader=1
| streamstats count AS _serial
| eval _time = _time + _serial
| fields - linecount
| sort 0 - _time

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| reverse
| streamstats count AS score count(eval(metORmissed="met")) AS met count(eval(metORmissed="missed")) AS missed
| eval minimum=90
| eval pct = 100 * met / (met + missed)

| where pct >= 90
| head 1

Re: Looping a "forecasting" column using stats

woodcock — Thu, 12 Dec 2019 17:38:42 GMT

I think my other answer was missing the mark. I think that this one is really what you need:

| makeresults
| eval _raw="score=5, met=3 missed=2,pct=60"
| kv

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| eval predicted_events = 0, count=0
| append [ |makeresults | eval predicted_events=mvrange(1,100) | mvexpand predicted_events | streamstats count ]
| filldown score met missed
| eval score = score + count, met = met + count
| fields - count
| eval pct = 100 * met / (met + missed)
| where pct >= 90
| head 1

Re: Looping a "forecasting" column using stats

woodcock — Thu, 12 Dec 2019 17:41:29 GMT

Nice. You gave him the answer in the most efficient way whereas I gave him the answer in the method that he was describing.

Re: Looping a "forecasting" column using stats

dindu — Thu, 12 Dec 2019 18:55:46 GMT

Thanks, Woodcock!!!
I am glad to be mentioned by you.

Re: Looping a "forecasting" column using stats

dojiepreji — Fri, 13 Dec 2019 06:08:32 GMT

Thank you. I have learned a lot from this answer.

Re: Looping a "forecasting" column using stats

dojiepreji — Fri, 13 Dec 2019 06:22:15 GMT

Could you please explain why this works? Did you get the formula from somewhere? I really should have paid more attention in math class.

Re: Looping a "forecasting" column using stats

dindu — Fri, 13 Dec 2019 14:17:46 GMT

Hi,

it was similar to an arithmetic progression.
I just solved the equation and tested it for a few values.