Percentage difference between two successive events as a new field

[rturk]

So a quick and dirty one.

If I have a search that gives me a daily summary of the bytes downloaded by web users:

sourcetype="web_logs" | timechart span=1d sum(sc_bytes) AS "Daily Traffic (Bytes)"

What would be the best way to compare two successive days and give a % increase/decrease value? e.g.

_time                      Daily Traffic    perc_diff
8/9/11  12:00:00.000 AM    318606425        0
8/10/11 12:00:00.000 AM    66560892         -79.10
8/11/11 12:00:00.000 AM    2987269232       4388.02
8/12/11 12:00:00.000 AM    7981047          -99.73
8/13/11 12:00:00.000 AM    160388640        1909.61 
8/14/11 12:00:00.000 AM    52523918         -67.25

Hope someone can help.. I have Splunkers block 😛

[echalex · Accepted solution]

Hi R.Turk,

I think you should be succesful using bucket, delta and eval. Use bucket to group the results into daily sets, stats to calculate the daily sum, delta to calculate the change and eval to get the percentage.

...|bucket _time span=1d | stats sum(sc_bytes) as sc_bytes_daily by _time |delta sc_bytes_daily as change |eval change_percent=change/(sc_bytes_daily-change)*100 |timechart span=1d  first(sc_bytes_daily) AS "Daily traffic (bytes)", first(change_percent) AS "Change (%)"

HTH

[kristian_kolb]

Well done! I was playing with an alternate solution, but not using bucket - noticed that bucket reduced the memory used during the search by a factor of 3 in my case. The job runtimes were similar for both searches. K.

[echalex]

:blush: You noticed! Yep, I did some testing with 5m buckets.

And yes, I'll be there. Looking forward to that beer! 🙂

[rturk]

Thanks echalex! That's exactly what I was after! I was going to ask you why the 5 minute spans, but you edited it 🙂 If you're heading to .conf2012, I'll be sure to buy you a beer 🙂