topic Re: Eval formula to display dates till 31st december where start day is determined by a formula in Splunk Search

Eval formula to display dates till 31st december where start day is determined by a formula

puneetkharband1 — Thu, 21 Sep 2017 18:01:59 GMT

Hey Everyone I am trying to write an eval when a user enter an year it should return a date
formula works fine in excel DATE(F6,11,29)-WEEKDAY(DATE(F6,11,24))
F6 is user input for an year.

idea is to display the days from the days from thanksgiving to december 31st for any year I input.

Re: Eval formula to display dates till 31st december where start day is determined by a formula

Richfez — Thu, 21 Sep 2017 18:58:16 GMT

It's not clear in what format you need this, or where you expect to place it. Regardless of those difficulties, here's an option.

If you can do it in SPL -

| gentimes start=12/25/2017 end=12/26/2017
| eval DaysToChristmas = round((starttime - now())/86400,0) 
| fields DaysToChristmas

That would require updating the new gentimes once per year. There's probably a workaround for that, but it's a 10 second fix once per year. Note you should also confirm that number - you might have to adjust it by + or - 1.

The logic is simple: in this case, line 1 is just to create a fake event on Christmas Day, 2017.
Line 2 first subtracts the current date and time now() from the time the gentimes created starttime, both of which are in seconds so we have to then divide by 86400 (the number of seconds in a day) to get days, then we round it to no decimals to get whole numbers.
Line 3 is only display that one field.

If you need to do this for a lot of events to see how far each is from Christmas, you can do it with similar logic in a regular search.

| mysearch ...
| eval DaysToChristmas = round((1514160001 - _time)/86400,0)

Same logic, essentially. Here I use the simpler "already converted to epoch format" number (I used Epoch Converter) and the event's _time, but otherwise it's the same.

Happy Splunking!
-Rich

Re: Eval formula to display dates till 31st december where start day is determined by a formula

puneetkharband1 — Tue, 29 Sep 2020 15:54:30 GMT

Hey Rich

I am actually calculating the difference between 2 dates where my year is dynamic based on the data.
In return I need to whole date in the same format
whole idea is to calculate the thanks giving day of the year also when I ll have thanks giving day as year-mon-day I need to print one week in the same format from that day.

2016
2015
2017

I tried this index="test" source="test" date=* mon=* year=* (STATDATE>=2016-11-22 AND STATDATE<=2016-11-30) SITE=USA | eval day_c = strftime(_time,"%Y-%m-%d") | eval enddate= year+"-11-29"| eval startdate= year+"-11-24" | eval DiffInSecs = strptime(enddate, "%Y-%m-%d")-strptime(startdate, "%Y-%m-%d") | eval td = strftime(DiffInSecs, "%Y-%m-%d %A") | table day_c td

but seems strftime doesnt work here

Re: Eval formula to display dates till 31st december where start day is determined by a formula

somesoni2 — Thu, 21 Sep 2017 22:09:10 GMT

See this runanywhere search. Replace line1 with your search. What this does is for each year value, it first gets the thanksgiving day (getting 7day range from Nov-22 and getting the Thursday) and then calculates difference between that and year end.

| gentimes start=-1 | eval year=mvrange(2012,2021) | table year | mvexpand year 
| eval thanksgivingrangestart=strptime(year."/11/22","%Y/%m/%d")  | eval date=mvrange(thanksgivingrangestart,thanksgivingrangestart+(86400*7),86400) | eval date=strftime(date,"%a %F") | eval thanksgivingday=mvfilter(match(date,"Thu")) | fields - date thanksgivingrangestart | eval dayFromThanksGivingToYearEnd=round((strptime(year."/12/31","%Y/%m/%d")-strptime(thanksgivingday,"%a %F"))/86400)

Re: Eval formula to display dates till 31st december where start day is determined by a formula

lfedak_splunk — Fri, 22 Sep 2017 00:16:47 GMT

Hey @puneetkharbanda, if they solved your problem, please don't forget to accept an answer! You can upvote posts as well. (Karma points will be awarded for either action.) Happy Splunking!

Re: Eval formula to display dates till 31st december where start day is determined by a formula

puneetkharband1 — Fri, 22 Sep 2017 15:19:58 GMT

thank you Somesh ...that will definitely bring me closer to what I am trying to do.