Create a baseline for each day of the week

[vpurushottam]

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 :

[pkeenan87 · Accepted solution]

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]

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

[niketn]

@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.

[vpurushottam]

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

[vpurushottam]

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" .