Splunk Search

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

abajracharya
Explorer

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.

Tags (1)
0 Karma

somesoni2
Revered Legend

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

abajracharya
Explorer

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

0 Karma

skoelpin
SplunkTrust
SplunkTrust

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
0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!