topic Re: given a date find which week of the year in Splunk Search

given a date find which week of the year

ajaylowes — Wed, 05 Jul 2017 11:48:44 GMT

given a date find which week of the year

For example : if date is "27-Feb-17" the result will be "09".
As 27th Feb 2017 comes in 9th Week of that year.

Another example: 8-Mar-17 will give "10" result.

Re: given a date find which week of the year

richgalloway — Wed, 05 Jul 2017 12:49:04 GMT

You'll probably have to write a custom command to do that.

Re: given a date find which week of the year

niketn — Wed, 05 Jul 2017 12:51:19 GMT

@ajaylowes, you can try %U date time conversion for Week of the Year
Following is run anywhere search:

| makeresults
| eval WeekOfYear=strftime(_time,"%U")

Re: given a date find which week of the year

adonio — Wed, 05 Jul 2017 12:52:30 GMT

not sure how to do it but you can quickly create an excel spreadsheet like explained below:
https://exceljet.net/formula/get-week-number-from-date
save as csv -> upload as a lookup -> use as you wish
hope it helps

Re: given a date find which week of the year

ajaylowes — Wed, 05 Jul 2017 12:53:56 GMT

for eval WeekOfYear=strftime(_time,"%U") , instead of _time can it take value from a column say "SubmitDate"

Re: given a date find which week of the year

cmerriman — Wed, 05 Jul 2017 12:54:18 GMT

try adding this:

|eval weeknum=strftime(_time,"%V")

I believe it runs Monday-Sunday, regardless of the day the year started. I also don't believe it's supported by Splunk, as it's not listed here: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables

Re: given a date find which week of the year

niketn — Wed, 05 Jul 2017 12:57:43 GMT

@cmerriman, seemed like %U and %V were missing in Splunk Documentation. I have submitted a feedback for adding the same.

%U- Week number of the year [0-53], Sunday is the first day of the week. Used in calculating the day of the year.
%V- ISO week number of the year [1-53]. Monday is the first day of the week. If the week containing January 1st has four or more days in the new year, it is considered week 1. Otherwise, it is the last week of the previous year, and the next week is week 1 of the new year. Used in calculating the day of the year.

Re: given a date find which week of the year

niketn — Wed, 05 Jul 2017 13:02:59 GMT

Yes, however, SubmitDate should be epoch time and not string time.

Following is run anywhere search to convert to epoch time first prior to converting to Week Of Year

| makeresults
| eval SubmitDate="27-Feb-17"
| eval SubmitDate=strptime(SubmitDate,"%d-%b-%y")
| eval WeekOfYear=strftime(SubmitDate,"%U")

Re: given a date find which week of the year

ajaylowes — Wed, 05 Jul 2017 13:07:15 GMT

Sorry but i missed one information earlier.
I am working on .csv file which has column "date" on which we need to apply the above logic .
eval WeekOfYear= strftime(date,"%V") - this doesn't work for me

Re: given a date find which week of the year

ajaylowes — Wed, 05 Jul 2017 13:07:52 GMT

Re: given a date find which week of the year

cmerriman — Wed, 05 Jul 2017 13:12:40 GMT

you'll need to make that into epoch time.

| eval date=strptime(date,"%d-%b-%y")
| eval yearNum=strftime(date,"%V")

Re: given a date find which week of the year

niketn — Wed, 05 Jul 2017 14:11:38 GMT

@ajaylowes, have you tried above example. strptime need to be run first on String Time.

Re: given a date find which week of the year

ajaylowes — Wed, 05 Jul 2017 15:45:51 GMT

Does eval SubmitDate="17-Feb-17" accepts column name ( example: "date") and then apply this to all the values of that column ?

Re: given a date find which week of the year

niketn — Thu, 06 Jul 2017 02:16:36 GMT

First two lines here are sample to generate mock data since you had not provided the name of the field earlier. 3rd and 4th lines perform the actual conversion that your need. Following is the query that you should apply to your base search:

 | eval date=strptime(date,"%d-%b-%y")
 | eval WeekOfYear=strftime(date,"%U")

However, I can see that @cmerriman has already suggested you this. Please try out and let us know if this does not work. If you face any issue please provide further details. Kindly upvote all comments that helped and accept any one of the solution once the issue is resolved.

Re: given a date find which week of the year

ajaylowes — Thu, 06 Jul 2017 09:01:00 GMT

Thanks @niketnilay ....its working now