Splunk Search

## Looping a "forecasting" column using stats

Path Finder

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.

Tags (5)
1 Solution
Contributor

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
``````
Esteemed Legend

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
``````
Path Finder

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

Esteemed Legend

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
``````
Contributor

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
``````
Path Finder

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

Contributor

Hi,

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

Esteemed Legend

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

Contributor

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

SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
Get Updates on the Splunk Community!