Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results forย

Splunk Search

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results forย

- Community
- :
- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Looping a "forecasting" column using stats

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dojiepreji

Path Finder

โ12-12-2019
02:23 AM

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.

1 Solution

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dindu

Contributor

โ12-12-2019
07:52 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

woodcock

Esteemed Legend

โ12-12-2019
09:38 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dojiepreji

Path Finder

โ12-12-2019
10:08 PM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

woodcock

Esteemed Legend

โ12-12-2019
09:18 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dindu

Contributor

โ12-12-2019
07:52 AM

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

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dojiepreji

Path Finder

โ12-12-2019
10:22 PM

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dindu

Contributor

โ12-13-2019
06:17 AM

Hi,

it was similar to an arithmetic progression.

I just solved the equation and tested it for a few values.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

woodcock

Esteemed Legend

โ12-12-2019
09:41 AM

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

dindu

Contributor

โ12-12-2019
10:55 AM

Thanks, Woodcock!!!

I am glad to be mentioned by you.

- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

richgalloway

SplunkTrust

โ12-12-2019
05:23 AM

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.

If this reply helps you, an upvote would be appreciated.