Splunk Search

Looping a "forecasting" column using stats

dojiepreji
Path Finder

Hello all,

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

alt text

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:

alt text

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.

1 Solution

dindu
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

View solution in original post

woodcock
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

dojiepreji
Path Finder

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

woodcock
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
0 Karma

dindu
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

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

0 Karma

dindu
Contributor

Hi,

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

0 Karma

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

0 Karma

dindu
Contributor

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

richgalloway
SplunkTrust
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

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