Splunk Search

Create a baseline for each day of the week

vpurushottam
Explorer

Hi guys,

I have query regarding how i can break my search for one month into weekly searches.

I have been given an access record for a month and i want to define a baseline for number of accesses per department per site.

As of now my plan is to divide the search time range (a month) into week days (for 4 weeks ) and calculate the average to reach the baseline. But i don't know how to split the month time range into weeks. I have tried with week days but that gives me the combined number of accesses for all the particular week day in that month. Search based on date i believe is not required.

In the test environment i can use weekly searches but the problem is i am only able to find number to accesses per day only for that week. But this won't help as to where this thing will be used will allow only monthly input. Is their a way to do this or is there an entirely different method to determine the baseline for each week day within a month.

A dummy query would certainly help me going. I can work onto that.

Thank you in advance.

Below i have attached an image of what i trying to have :

alt text

0 Karma
1 Solution

pkeenan87
Communicator

Thats a tough one. I havent gotten the entire thing figured out but this dummy search should get you most of the way there if not pointed in the right direction at least. Im not sure how to post foreach searches properly in here as it keeps trimming my post. It is supposed to say FIELD in between 3 < signs and 3 > signs

index=internal
| eval foo = date_mday." ".date_wday
| stats count by foo date_wday
| eval foo = foo." ".count
| stats values(foo) AS foos by date_wday
| eval week_one = mvindex(split(mvindex(foos, 0), " "),-1)
| eval week_two = mvindex(split(mvindex(foos, 1), " "),-1)
| eval week_three = mvindex(split(mvindex(foos, 2), " "),-1)
| eval week_four = mvindex(split(mvindex(foos, 3), " "),-1)
| eval total_vals = mvcount(foos)
| eval total = 0
| fillnull value=0
| foreach week
* [eval total = total + '<>']
| eval average = round(total / total_vals,2)
| table date_wday week_one week_two week_three week_four average

View solution in original post

pkeenan87
Communicator

Thats a tough one. I havent gotten the entire thing figured out but this dummy search should get you most of the way there if not pointed in the right direction at least. Im not sure how to post foreach searches properly in here as it keeps trimming my post. It is supposed to say FIELD in between 3 < signs and 3 > signs

index=internal
| eval foo = date_mday." ".date_wday
| stats count by foo date_wday
| eval foo = foo." ".count
| stats values(foo) AS foos by date_wday
| eval week_one = mvindex(split(mvindex(foos, 0), " "),-1)
| eval week_two = mvindex(split(mvindex(foos, 1), " "),-1)
| eval week_three = mvindex(split(mvindex(foos, 2), " "),-1)
| eval week_four = mvindex(split(mvindex(foos, 3), " "),-1)
| eval total_vals = mvcount(foos)
| eval total = 0
| fillnull value=0
| foreach week
* [eval total = total + '<>']
| eval average = round(total / total_vals,2)
| table date_wday week_one week_two week_three week_four average

vpurushottam
Explorer

Got it !! Thank you very much, i forgot to remove my test command from the query. 🙂

0 Karma

niketn
Legend

@vpurushottam I have convered comment by @pkeenan87 to answer. Please accept the answer to mark this question as answered!

@pkeenan87 while posting SPL, Code, Data on Splunk Answers you should use the code button i.e. button with 101010 or Shortcut Ctrl+K to ensure that the special characters do not escape.

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

vpurushottam
Explorer

Can i just hard code the value 4 to it ? will that work?

0 Karma

vpurushottam
Explorer

Hey @pkeenan87, thank you for the quick reply. I did try your query and it is exactly what i was looking for. But there is a slight problem with it. I am not getting value for field "total_vals" .

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

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

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...