Calculating the percentage growth value of a field day to day

[roberto_mendes]

Hello everyone!

I would like to know the percentage of growth of the field "wasted_MB" day by day, that is, the percentage of growth compared with the 27 on 26, 26 on 25, 25 to 24, 24 to 23 and so on...

Here is a brief sample of the data I have been analyzing:

TABLE      actual_MB     size_MB       wasted_MB    _time
TK_LOGON    458.83      615.72      156.89      27/04/2015 18:22
TK_LOGON    454.19      615.72      161.53      26/04/2015 18:22
TK_LOGON    496.77      615.72      118.95      25/04/2015 18:22
TK_LOGON    481.16      615.72      134.56      24/04/2015 18:22
TK_LOGON    482.07      615.72      133.65      23/04/2015 18:22

Today only can do the comparison between two values, for example:
- Today's values with those of yesterday.
- The values of this week with the week of values passes.

Follows search used to compare the present day with the previous day:

index=data_base sourcetype=size_table_frag earliest=-2d@d latest=@d TABLE=TK_LOGON | stats first(wasted_MB) AS current, last(wasted_MB) AS previous by TABLE | eval per_grown = (current/previous -1)*100 | stats values(per_grown) AS per_grown, values(previous) AS previous, values(current) AS current by TABLE

Follow the result I can with this search:

TABLE        per_grown      previous    current
TK_LOGON    -2.873       161.53   156.89

However, I would calculate the percentage day to day according to the time when I determine. So it could put the evolution of growth in a "timechart".

I hope I was clear in the description of the problem.

Now grateful for the support.

Sincerely.

[lguinn2 · Accepted solution]

This should work. Note that I "made up" the second command, but it should be whatever it takes to get the "sample" table that you show above

yoursearchhere
| timechart span=1d   sum(x) as actual_MB  sum(y) as  size_MB  sum(z) as   wasted_MB  by TABLE
| sort _time
| delta wasted_MB AS delta_wMB p=1
| eval percentChange=round(delta_wMB*100/wasted_Mb,2)
| xyseries _time TABLE percentChange

Finally, the last command will put the data in the correct format for a timechart. If what you want is a table, you should probably replace the xyseries command.

[roberto_mendes]

Hello lguinn.

Thank you for your quick response. I was testing the search following his guidance but did not succeed. I had not used the "delta" command. Analyzing the search steps noticed the command "delta" and the command "eval" do not return a value.

The search is used in this query:

index=data_base sourcetype=size_table_frag TABLE="TK_LOGON"
| timechart span=1d sum(actual_MB) as actual_MB sum(size_MB) as size_MB sum(wasted_MB) as wasted_MB  by TABLE
| sort _time
| delta wasted_MB AS delta_wMB p=1
| eval percentChange=round(delta_wMB*100/wasted_MB,2)
| table *

Follows the search result:

actual_MB: TK_LOGON delta_wMB   size_MB: TK_LOGON   wasted_MB: TK_LOGON _time
482.07                           615.72            133.65                2015-04-23
481.16                           615.72            134.56                2015-04-24
496.77                           615.72            118.95                2015-04-25
454.19                           615.72            161.53                2015-04-26
458.83                           615.72            156.89                2015-04-27
467.35                           615.72            148.37                2015-04-28

Using the "xyseries" I just do not appear any results. Can you tell me where I am making the mistake.

Already grateful for the support.

Sincerely.

[lguinn2]

Well, that's annoying. I actually can't see what is wrong with that search. But try this one:

 index=data_base sourcetype=size_table_frag TABLE="TK_LOGON"
 | timechart span=1d sum(actual_MB) as actual_MB sum(size_MB) as size_MB sum(wasted_MB) as wasted_MB 
 | sort _time
 | streamstats current=f window=1 earliest(wasted_MB) as last_wasted
 | eval percentChange=round((last_wasted-wasted_MB)*100/wasted_MB,2)
 | table *

And the reason that the xyseries didn't work was that the delta command didn't produce any data, so the percentChange field didn't exist at all - and xyseries needed the percentChange field. If this works, replace the table with the xyseries, and you are good to go

[roberto_mendes]

Uaul, it worked perfectly, thank you!!!

Now I'm trying to apply the same calculation for a table relationship, but the percentage of the value only applies to the first table. You must know how to apply this percentage calculation for a table relationship, for example by changing the search parameter

from:

  index=data_base sourcetype=size_table_frag TABLE="TK_LOGON"

to:

  index=data_base sourcetype=size_table_frag TABLE="*"

Using a timechart the end of the search as:

| timechart values(percentChange) as percentChange by TABLE

Ps: The question has already been successfully answered. I'm just trying to refine the search result. Thank you!

[lguinn2]

Ah, if you want this for a set of tables, that's a little different, but easy enough:

index=data_base sourcetype=size_table_frag TABLE="*"
  | timechart span=1d sum(actual_MB) as actual_MB sum(size_MB) as size_MB sum(wasted_MB) as wasted_MB by TABLE
  | sort TABLE _time
  | streamstats current=f window=1 earliest(wasted_MB) as last_wasted by TABLE
  | eval percentChange=round((last_wasted-wasted_MB)*100/wasted_MB,2)
  | table *

Note that "by TABLE" most be added both in the timechart and the streamstats commands. I also included TABLE in the sort, although that probably wasn't necessary.

[roberto_mendes]

Hello!

I was so far trying to use his suggestion changing some parameter settings but did not succeed. Using "by TABLE" in both the "stats" as in "streamstats" the search does not return a result.

However, I used part of your search modifying some parameter settings and got the desired result. Follows the used search command:

index=data_base sourcetype=size_table_frag TABLE="*"
| table _time actual_MB size_MB wasted_MB TABLE
| sort TABLE _time
| streamstats current=f window=1 earliest(wasted_MB) as last_wasted
| eval percentChange=round((wasted_MB-last_wasted)*100/last_wasted,2)
| timechart sum(percentChange) as percentChange by TABLE

Thank you very much for your support, I learned some commands more than Splunk with their tips, :).

Sincerely.

[lguinn2]

Last thought: you don't need the first table command (line 2) - it does not add anything to your search except a little overhead.

Good job!