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