Splunk Search

Day of the Week table - current day vs Monthly avg

splunk_zen
Builder

I'm trying to get a table showing the current daily average vs the previous month average,

but I'm unsure I got the composed search right.
I've reverse engineered the following search starting from this one,
http://wiki.splunk.com/Community:Search_Report:_How_To_Create_a_Table_of_Day_of_Week_-_Monthly_Avera...

source=*SDP_term_causes.csv earliest=-4w@w latest=now
  | bucket _time span=1d
  | eval DayOfMonthN=strftime(_time, "%d")
  | eval DayOfWeekC=strftime(_time, "%a")
  | stats avg(SERVICE_ACCEPTED_INVOCATIONS) AS AvgAcceptInv by DayOfMonthN, DayOfWeekC
  | append
     [ search source=*SDP_term_causes.csv earliest=-2mon@mon latest=-1mon@mon
            | bucket _time span=1d
            | eval DayOfMonthN=strftime(_time, "%d")
            | eval DayOfWeekC=strftime(_time, "%a")
            | stats avg(SERVICE_ACCEPTED_INVOCATIONS) AS AvgAcceptInv_LastMonth by DayOfMonthN, DayOfWeekC ]
  | stats first(DayOfWeekC) AS DayOfWeek, first(AvgAcceptInv) AS AvgAcceptInv_Month, first(AvgAcceptInv_LastMonth) AS AcceptInv_Avg_LastMonth by DayOfMonthN
  | eval Diff=AvgAccepInv_Month-AccepInv_Avg_LastMonth
  | fields DayOfMonthN, DayOfWeek, AvgAcceptInv_Month, AcceptInv_Avg_LastMonth, Diff

Can someone more experienced tell me if the search needs some correction?
Thanks

Tags (3)
0 Karma
1 Solution

lguinn2
Legend
source=*SDP_term_causes.csv earliest=-30d@d latest=@d
  | bucket _time span=1d
  | eval DayOfMonthN=strftime(_time, "%d")
  | eval DayOfWeek=strftime(_time, "%a")
  | stats avg(SERVICE_ACCEPTED_INVOCATIONS) AS AvgAcceptInv by DayOfMonthN, DayOfWeek
  | join type=outer DayOfMonthN
     [ search source=*SDP_term_causes.csv earliest=-60d@d latest=-30d@d
            | bucket _time span=1d
            | eval DayOfMonthN=strftime(_time, "%d")
            | stats avg(SERVICE_ACCEPTED_INVOCATIONS) AS AvgAcceptInv_LastMonth by DayOfMonthN
            | fields DayOfMonthN AvgAcceptInv_LastMonth ]
  | eval Diff=AvgAccepInv-AccepInv_Avg_LastMonth
  | fields DayOfMonthN, DayOfWeek, AvgAcceptInv, AcceptInv_Avg_LastMonth, Diff

This is how I would do it, but it might not be that different....

View solution in original post

splunk_zen
Builder

Ended up using comparing the current daily data with a 2month day of week average,

source=*SDP_term_causes.csv earliest=-4w@w latest=@d
  | bucket _time span=1d
  | eval nMonthDay=strftime(_time, "%d")
  | eval WeekDay=strftime(_time, "%a")
  | stats avg(TIMEOUT) AS AvgTimeouts by nMonthDay, WeekDay
  | join type=outer WeekDay
     [ search source=*SDP_term_causes.csv earliest=-2mon@mon latest=-1w@w
            | bucket _time span=1d
            | eval WeekDay=strftime(_time, "%a")
            | stats avg(TIMEOUT) AS WeekDay2MonAvg by WeekDay
            | fields WeekDay WeekDay2MonAvg ]
  | eval Diff=AvgTimeouts-WeekDay2MonAvg
  | fields nMonthDay, WeekDay, AvgTimeouts, WeekDay2MonAvg, Diff
0 Karma

lguinn2
Legend
source=*SDP_term_causes.csv earliest=-30d@d latest=@d
  | bucket _time span=1d
  | eval DayOfMonthN=strftime(_time, "%d")
  | eval DayOfWeek=strftime(_time, "%a")
  | stats avg(SERVICE_ACCEPTED_INVOCATIONS) AS AvgAcceptInv by DayOfMonthN, DayOfWeek
  | join type=outer DayOfMonthN
     [ search source=*SDP_term_causes.csv earliest=-60d@d latest=-30d@d
            | bucket _time span=1d
            | eval DayOfMonthN=strftime(_time, "%d")
            | stats avg(SERVICE_ACCEPTED_INVOCATIONS) AS AvgAcceptInv_LastMonth by DayOfMonthN
            | fields DayOfMonthN AvgAcceptInv_LastMonth ]
  | eval Diff=AvgAccepInv-AccepInv_Avg_LastMonth
  | fields DayOfMonthN, DayOfWeek, AvgAcceptInv, AcceptInv_Avg_LastMonth, Diff

This is how I would do it, but it might not be that different....

splunk_zen
Builder

Thanks, ended up using the search in the following answer.

0 Karma
Get Updates on the Splunk Community!

Index This | Forward, I’m heavy; backward, I’m not. What am I?

April 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

A Guide To Cloud Migration Success

As enterprises’ rapid expansion to the cloud continues, IT leaders are continuously looking for ways to focus ...

Join Us for Splunk University and Get Your Bootcamp Game On!

If you know, you know! Splunk University is the vibe this summer so register today for bootcamps galore ...