Splunk Search

Avg Counts in the Last Month

twistedsixty4
Path Finder

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.

0 Karma
1 Solution

twistedsixty4
Path Finder

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

View solution in original post

0 Karma

twistedsixty4
Path Finder

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
0 Karma

twistedsixty4
Path Finder

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

0 Karma

asimagu
Builder

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

am I making sense?

0 Karma
Get Updates on the Splunk Community!

Ready, Set, SOAR: How Utility Apps Can Up Level Your Playbooks!

 WATCH NOW Powering your capabilities has never been so easy with ready-made Splunk® SOAR Utility Apps. Parse ...

DevSecOps: Why You Should Care and How To Get Started

 WATCH NOW In this Tech Talk we will talk about what people mean by DevSecOps and deep dive into the different ...

Introducing Ingest Actions: Filter, Mask, Route, Repeat

WATCH NOW Ingest Actions (IA) is the best new way to easily filter, mask and route your data in Splunk® ...