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
Revered Legend

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