Dashboards & Visualizations

How to change cell color based on relative date?

mistydennis
Communicator

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. 

Labels (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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")))

View solution in original post

0 Karma

mistydennis
Communicator

Yes, newduedate is now in epoch format. How do I now get it to:

  1. Be in %Y-%m-%d format
  2. Adjust the relative time so it's accounting for future dates (any date after today), today, and any date prior to today
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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")))
0 Karma

mistydennis
Communicator

Thank you, that got me close enough!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

mistydennis
Communicator

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. 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Is dueDate a string or an epoch time?

0 Karma

mistydennis
Communicator

It is a string.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You should convert it an epoch time with strptime() so you can compare with the time returned by relative_time()

0 Karma

mistydennis
Communicator

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)

0 Karma
Get Updates on the Splunk Community!

Almost Too Eventful Assurance: Part 1

Modern IT and Network teams still struggle with too many alerts and isolating issues before they are notified. ...

Demo Day: Strengthen Your SOC with Splunk Enterprise Security 8.1

Today’s threat landscape is more complex than ever. Security operation centers (SOCs) are overwhelmed with ...

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...