Detect Start-of-Week & Weekly aggregations

[anirbandasdeb]

Hello splunkers,

We have to calculate some KPIs, on time-series data, aggregated by multiple factors, with time being the most important one.
The most common time aggregations for us are monthly and weekly.

Scenario: Calculate the weekly trend of a KPI for the month of May 2018 [ref image attached]

How one would normally do it would be [assume week start on Sunday]:
Week Date
1: 1 - 5
2: 6 - 12
3: 13 - 19
4: 20 - 26
5: 27 - 31

Thus there will be 5 data points.

How do I implement this in Splunk?

The way I figured out to do it now is use "bin span=1w _time", but it does not detect the week start.
However, this approach plainly takes 7 days from the 1st of the month and the result is that we have skewed weeks leading to misleading KPI values.

[anirbandasdeb]

One way I figured out how to do this is using:

| eval Week = strftime(strptime(_time, "%Y-%m-%d %H:%M:%S.%N"), "%V")

strptime converts the _time [formatted in "%Y-%m-%d %H:%M:%S.%N"] to Unix epoch time. Then strftime extracts the week of year from the epoch time using "%V"

The variable %V is not mentioned in the documentation.

However, how do I declare custom weeks, if the business requirements are as such?