Splunk Search

How can I convert week number to starting date of that week?

nikita012
New Member

I have a week_number field in my data. I want to display each week_number with the date of 1st day in that week.
Ex-

week number Date

1 01/01/2019
2 08/01/2019

Can you please provide with the code for this

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try something like this (run anywhere sample, replace everything before eval with your base search)

| gentimes start=-10 | streamstats count as weekno | table weekno | eval firstOfWeek=strftime(relative_time(now(),tostring(weekno-tonumber(strftime(now(),"%V")))."w@w"),"%F %a")

This is treating Sunday as the first day of week. IF you want Monday, change w@w with w@w1.

0 Karma

nikita012
New Member

I applied the same code and below is the output. The code is not working for dates of previous year. I used this line in my code.

eval firstOfWeek=strftime(relative_time(now(),tostring(weekno-tonumber(strftime(now(),"%V")))."w@w"),"%F ")

Date weeknum firstOfWeek

06/03/2019 10 2019-03-04
06/03/2019 10 2019-03-04
14/03/2019 11 2019-03-11
23/03/2019 12 2019-03-18
24/03/2019 12 2019-03-18
01/10/2018 40

03/10/2018 40

03/10/2018 40

07/10/2018 40

08/10/2018 41

09/10/2018 41

10/10/2018 41

11/10/2018 41

11/10/2018 41

Can you specify the solution?

0 Karma

kmorris_splunk
Splunk Employee
Splunk Employee

Try something like this:

BASE SEARCH GOES HERE...
| eval modifier=case(date_wday == "sunday", 0, date_wday == "monday", 1, date_wday == "tuesday", 2, date_wday == "wednesday", 3, date_wday == "thursday", 4, date_wday == "friday", 5, date_wday == "saturday", 6, 1=1, 0) 
| eval first_wday_date=_time - (modifier * 86400) 
| eval first_wday_date=strftime(first_wday_date, "%m/%d/%y") 
| table date_wday _time modifier first_wday_date

This is assuming Sunday is the first day of your week. I wasn't sure if you wanted it to be Monday. If so, just mess with the modifiers (the amount of days to subtract from the current weekday, to get you to the beginning day of the week. This also assumes, you could go back into the previous month if the beginning of the week was not in the current month.

In the image below, the table has the current day of the week, the current date, the modifier value, and the date of the first day of the week.
alt text

0 Karma

FrankVl
Ultra Champion

@nikita012 asks how to translate week_number into a date (first day of that week). You're using _time. Not sure how that is a solution to the question?

0 Karma

FrankVl
Ultra Champion

Assuming you want to get the date of the monday of that week number, I would say this should work:

| eval date="1 ".week_number." 2019"
| eval date=strftime(strptime(date,"%w %V %Y"),"%d/%m/%Y")

But for some reason it is not able to parse that string using day of week, week number and year into a timestamp...

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...