Avg Counts in the Last Month

[twistedsixty4]

hey all,
im working on a network overview dashboard. what i currently have is a saved search showing the last 7 days (per hour) of firewall denies but that information is useless without a baseline. so what i need is an overlay of the monthly average by week per hour. (if im not clear enough here let me know)

let me define this a little more..
my overlay needs to be a monthly average, my graph is over the past week, and my sample rate (or span) is by the hour. but my issue here is two-fold, first im not sure how to do the average, second i dont know how to do the overlay. if anyone has done this before or knows how to go about this i would greatly appreciate it!

Alex

---

UPDATE:
here is where i'm stuck right now..

(index=cisco* OR index=f5 OR index=app) linecount=1 | search action="blocked" | bucket _time span=1h | stats count by _time

im thinking using another bucket to create week groups to average by hour..

---

UPDATE#2:
im getting closer, what would make this possible would be having a "day-of-the-week" value concatenated with "hour-of-the-day" value ie date_wday and date_hour merged with counts per hour for the last for weeks (snapped to hour) which would give me 4 counts to avg per day:hour. can anyone help from here?

---

UPDATE#3:
ok i have assigned date_hour and i can stas count by it now, but i cant calldate_wday and if i look at my field extractions i dont see it, how do i extract it? if i can get wday:hour as a field to count by then i can avg and create the graph. google seems to be failing me right now not sure where else to turn to.

[twistedsixty4 · Accepted solution]

i guess i just needed to take a break from it to figure it out..

eval day:hour = strftime(_time, "%A") + ": " + date_hour | stats count by day:hour

my little lifesaver. this was perfect it outputs "Friday: 10" and the count for the hour. it took some digging but after rephrasing to a piece of my question i found..

http://splunk-base.splunk.com/answers/32650/no-date_wday-from-uf-collecting-windows-event-logs

to get date_wday value
and..

http://splunk-base.splunk.com/answers/49394/merge-two-fields-into-one-field

to add the date_hour to it.
I havent gotten the graph yet just made the search and building the saved search so i can avg and graph. I hope this helps someone else!

---

EXTENDED:

quick update, im in the process of graphing the search now, i started it earer before i left for lunch but the sort was off because date_wday is an alphabetic sort so i thought i would share the solution i found.

http://splunk-base.splunk.com/answers/65303/issue-regarding-date_wday

this assigns a numeric value field (1-7) for each day of the week
if it helps anyone i've included my whole search string below.

(index=cisco* OR index=f5 OR index=app) linecount=1 | search action="blocked" | eval date_wday = strftime(_time, "%A") | eval day:hour = date_wday + ": " + date_hour | eval sort_field=case(date_wday=="Monday",1, date_wday=="Tuesday",2, date_wday=="Wednesday",3, date_wday=="Thursday",4, date_wday=="Friday",5, date_wday=="Saturday",6, date_wday=="Sunday",7) | stats count by sort_field,day:hour | stats avg(count) by sort_field,day:hour | fields - sort_field

[twistedsixty4]

not really, the monthly count is irrelevant except for the fact i need it to get a weekly average

[asimagu]

maybe you could first do a timechart span=1h of that average and pipe it to your monthly stat

am I making sense?