Splunk Search

Weekly change percentage query

dhavamanis
Builder

Can you please help us to find the weekly change percentage, we have a splunk query

 index="mobileapps" sourcetype="mobileiostsv" Title="*" Brand="*" Status="Published" NOT (Type="Update" OR Type="In-App             Purchase") | eval week = strftime(relative_time(_time,"@w0"),"%m/%d/%Y")
 | stats sum(Units) as Downloads values(Brand) as Brand by Title week
 | append [search index="mobileapps" sourcetype=mobileiostsv Title="*" Brand="*" Status="Published" Type="Update" NOT Type="In-App Purchase" | eval week = strftime(relative_time(_time,"@w0"),"%m/%d/%Y")
 | stats sum(Units) as Updates values(Brand) as Brand by Title ]| stats values(Brand) as Brand values(Downloads) as Downloads values(Updates) as Updates by Title week
 | rename Title as App | addtotals | sort by App week  | fillnull value="-"

and its giving output with these fields,

 App
 Brand
 Week
 Downloads
 Updates
 Total

from this we want to find the weekly change percentage, like below output,

 App1, Brand1, 04/17/2016, 50, 50, 100, 10%
 App1, Brand1, 04/10/2016, 40, 50, 90,  12.5%
 App1, Brand1, 04/03/2016, 30, 50, 80,  -
 App2, Brand2, 04/17/2016, 40, 50, 90,  77.78%
 App2, Brand2, 04/10/2016, 10, 10, 20,  -
 App3, Brand3, 04/17/2016, 10, 10, 20,  -75%
 App3, Brand3, 04/10/2016, 10, 10, 80,  -

Also with another one report order by week date only with all the columns.

 App1, Brand1, 04/17/2016, 50, 50, 100, 10%
 App2, Brand2, 04/17/2016, 40, 50, 90,  77.78%
 App3, Brand3, 04/17/2016, 10, 10, 20,  -75%
 App1, Brand1, 04/10/2016, 40, 50, 90,  12.5%
 App2, Brand2, 04/10/2016, 10, 10, 20,  -
 App3, Brand3, 04/10/2016, 10, 10, 80,  -
 App1, Brand1, 04/03/2016, 30, 50, 80,  -
0 Karma
1 Solution

somesoni2
Revered Legend

Try something like this

For requirement 1

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0 App Brand  -week

For requirement 2

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0  -week App Brand

View solution in original post

somesoni2
Revered Legend

Try something like this

For requirement 1

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0 App Brand  -week

For requirement 2

Your current search | sort 0 App Brand week | streamstats current=f window=1 values(Total) as prev by App Brand | eval change=coalesce(round((Total-prev)*100/prev,1),"-") | fields - prev  | sort 0  -week App Brand
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...