topic Re: Display comparison between last week vs this week data in 2 rows and calculated change in percent in Splunk Search

Display comparison between last week vs this week data in 2 rows and calculated change in percent

abajracharya — Sat, 20 Feb 2016 01:54:57 GMT

I don't know if this is possible. I am trying to compare last week data vs this week data and displayed in such a way as shown:

LastWeekDate (12Feb-12:00) LastweekData [ 200K] ChangeIn%
ThisWeekDate (19Feb-12:00) ThisweekData [250K] [25%]of lastweek&thisweek

I do not know even this is possible but just seeing if anyone has done this.

Re: Display comparison between last week vs this week data in 2 rows and calculated change in percent

skoelpin — Sat, 20 Feb 2016 02:51:29 GMT

Here's my query which compares tax errors in the previous hour vs yesterdays previous hour and alerts if the errors in the previous hour were 25% higher than the previous hour of yesterday

So to answer your question, yes it's possible. You will need to use a subsearch which will look at yesterdays house and your main search will look back the previous hour

index=vertex7-access   RTG_Error="500" earliest=-1h@h latest=@h                           
| append [| noop | stats count AS RTG_Tax | eval RTG_Tax = "LookupTaxAreas70"]          
| append [| noop | stats count AS RTG_Tax | eval RTG_Tax = "CalculateTax70"]
| stats count AS TodayLastHour by RTG_Tax          
| eval TodayLastHour = TodayLastHour - if((RTG_Tax = "LookupTaxAreas70"), 1, 0)   
| eval TodayLastHour = TodayLastHour - if((RTG_Tax = "CalculateTax70"), 1, 0)
| addtotals col=t row=f labelfield=RTG_Tax label=Total
| appendcols [search index=vertex7-access RTG_Error="500" earliest=-25h@h latest=-24h@h   
| append [| noop | stats count AS RTG_Tax | eval RTG_Tax = "LookupTaxAreas70"] 
| append [| noop | stats count AS RTG_Tax | eval RTG_Tax = "CalculateTax70"] 
| stats count AS YesterdayLastHour by RTG_Tax    
| eval YesterdayLastHour = YesterdayLastHour - if((RTG_Tax = "LookupTaxAreas70"), 1, 0)  
| eval YesterdayLastHour = YesterdayLastHour - if((RTG_Tax = "CalculateTax70"), 1, 0)   
| addtotals col=t row=f labelfield=RTG_Tax label=Total   
| table RTG_Tax, YesterdayLastHour | rename RTG_Tax AS Total]
| rename RTG_Tax AS Total 
| where TodayLastHour >  1.25 * YesterdayLastHour

Re: Display comparison between last week vs this week data in 2 rows and calculated change in percent

somesoni2 — Sat, 20 Feb 2016 04:02:53 GMT

This is how I'll do this (run anywhere sample, for count of events comparison for last week vs this week, full week)

index=_internal earliest=-1w@w latest=now() | timechart span=1w count as EventCount | eval Period=if(_time=relative_time(now(),"@w"),"This Week Data","Last Week Data") | streamstats current=f window=1 values(EventCount) as prev | eval Change_Percentage=if(isnotnull(prev),(EventCount-prev)*100/prev,"NA") | table Period EventCount Change_Percentage

Sample output

Period           EventCount  Change_Percentage
Last Week Data   281386      NA 
This Week Data   1112784     295.465304

If you want to compare only specific timeframe of this week vs last week (like today's data vs same day last week), try like this

index=_internal earliest=@d latest=now | stats count as EventCount| eval Period="Today" | append [search index=_internal earliest=-8d@d latest=-7d | stats count as EventCount | eval Period="Last Week Same Day" ]  | streamstats current=f window=1 values(EventCount) as prev | eval Change_Percentage=if(isnotnull(prev),(EventCount-prev)*100/prev,"NA") | table Period EventCount Change_Percentage

Re: Display comparison between last week vs this week data in 2 rows and calculated change in percent

abajracharya — Sat, 20 Feb 2016 17:36:31 GMT

Thanks somesoni2. i was actually looking for second scenario and it worked good.