I would like to change the color of a cell based on the date. If the date is in the future, then green. If the date is today, then yellow. If the date is in the past, then red. I have read many posts about how to do this based on _time and appending a word to the date ("overdue", for example), but I can't seem to make this work in my case. The field I'm working with is dueDate (not based on _time), and it's formatted as %Y-%m-%d.
strftime() converts an epoch time to a string.
strptime() parses a string to an epoch time.
Both take a time format string
Date and time format variables - Splunk Documentation
You can easily compare epoch times since they are just numbers
| eval stringdate=if(epochdate=relative_time(now(),"@d"),mvappend(stringdate,"today"), if(epochdate<relative_time(now(),"@d"),mvappend(stringdate,"before today"),mvappend(stringdate,"after todate")))
Yes, newduedate is now in epoch format. How do I now get it to:
strftime() converts an epoch time to a string.
strptime() parses a string to an epoch time.
Both take a time format string
Date and time format variables - Splunk Documentation
You can easily compare epoch times since they are just numbers
| eval stringdate=if(epochdate=relative_time(now(),"@d"),mvappend(stringdate,"today"), if(epochdate<relative_time(now(),"@d"),mvappend(stringdate,"before today"),mvappend(stringdate,"after todate")))
Thank you, that got me close enough!
Make the dueDate field a multi-value field with the second value being the colour you want the cell coloured, then use CSS to hide the second value.
Have a look at this solution and see if you can adapt it to your situation
Solved: Re: Highlight row if unique values exist within dy... - Splunk Community
Yes, that is what I'm looking for. But I'm having trouble as well formatting my query correctly - I'm not sure what the correct syntax is.
I read another post here which you answered (🙂) and said to try this:
| eval newtime=if(relative_time(_time,"@d")=relative_time(now(),"@d"),mvappend(strftime(_time,"%Y-%m-%d"),"today"), if(relative_time(_time,"@d")=relative_time(now(),"-7d@d"),mvappend(strftime(_time,"%Y-%m-%d"),"overdue"),strftime(_time,"%Y-%m-%d")))
But I'm not sure how to adjust this for my case - I tried plugging in dueDate instead of _time, it did not work. And I don't know how to format the strf and strptime correctly so that I am going beyond the past 7 days.
Is dueDate a string or an epoch time?
It is a string.
You should convert it an epoch time with strptime() so you can compare with the time returned by relative_time()
Apologies, but dealing with timestamps has always been confusing for me. So still working with one of your answers from another post, I have:
| eval newduedate=strptime(dueDate, "%Y-%m-%d")
| eval newduedate=if(relative_time(dueDate,"@d")=relative_time(now(),"@d"),mvappend(strftime(dueDate,"%Y-%m-%d"),"today"),
if(relative_time(dueDate,"@d")=relative_time(now(),"-7d@d"),mvappend(strftime(dueDate,"%Y-%m-%d"),"overdue"),strftime(dueDate,"%Y-%m-%d")))
I understand I'm converting to epoch time with the first line, but after that I get lost. And this produces no results, so I'm clearly doing something wrong.
Try something like this
| eval newduedate=strptime(dueDate, "%Y-%m-%d")
| eval dueDate=if(newduedate=relative_time(now(),"@d"),mvappend(dueDate,"today"),
if(newduedate=relative_time(now(),"-7d@d"),mvappend(dueDate,"overdue"),dueDate))
newduedate is the epoch version of dueDate (which remains a string)