topic Re: How to dynamically put formulas in my table column? in Splunk Search

How to dynamically put formulas in my table column?

HattrickNZ — Thu, 24 Nov 2016 03:08:29 GMT

This is my search:

timechart  span=mon max(c117492014) as "attached" |
eval lic=180000 |
eval forecast = "" | 
eval tcheck=round(strptime("2016-12-01","%Y-%m-%d"),0) | 
eval forecast=if(_time==tcheck,164444,forecast) | 
fields - tcheck

And this is the result:

_time   attached          forecast  lic
1   2016-09 133757              180000
2   2016-10 147797              180000
3   2016-11 163994              180000
4   2016-12             164444   180000
5   2017-01                      180000
6   2017-02                      180000
7   2017-03                      180000
8   2017-04                      180000

It is probably a step in the right direction but it is currently very static. I would like to make it more dynamic for future use.

This is what I would like to achive in the forecast column I would like

in row 4 (163994*X)+163994
in row 5 (<value in row 4 of forecast column>*X)+<value in row 4 of forecast column>
in row 6 (<value in row 5 of forecast column>*X)+<value in row 5 of forecast column>
in row 7 (<value in row 6 of forecast column>*X)+<value in row 6 of forecast column>
in row 8 (<value in row 7 of forecast column>*X)+<value in row 7 of forecast column>

Re: How to dynamically put formulas in my table column?

maciep — Sat, 03 Dec 2016 01:44:11 GMT

this doesn't really answer your question, but have you thought about using the predict function? Maybe something like this?

| timechart  span=mon max(c117492014) as "attached"
| predict attached future_timespan=4
| eval license="180000"

Re: How to dynamically put formulas in my table column?

niketn — Sat, 03 Dec 2016 10:12:02 GMT

You need to calculate delta for current bucket and previous one (provided your results are sorted by time).

Logic: attached value for current row minus diffCount for current row will give you the attached value of previous row.

   Your base search | eval X=0.01 | delta attached as diffCount | eval forecast = (attached-diffCount)*X + (attached-diffCount) | table _time, attached, forecast

I dont think you need a forecast value for the first row. However, if you need you can pipe the following code after delta command to set diffCount to 0 for the first row where it is null.

eval diffCount=if(isnull(diffCount),0,diffCount)

PS: Assuming your base search calculates value of factor X, I have hard-coded above as 0.01. Hopefully your base search will calculate the same.