How to create colored tiles based on time?

[vishalduttauk]

I am struggling to find a solution where I would like a dashboard tile to turn red if an email is not received by 11.30 every week day morning.

The search behind the tile has the timeframe set to Today so I would like it to be green by default and if Splunk ingests the email before 11.30 am.

It should only turn red if the email hasn't been ingested by 11.30 am.

This is the base search query:

index=msexchange sourcetype="MSExchange:2013:MessageTracking" subject="**** - Success" directionality=Originating psender="********" | stats dc(message_subject)

Can you help?

[maciep]

Not sure if you need help coloring the tile or help with the search or both.  But here is an idea for the search at least.

index=msexchange sourcetype="MSExchange:2013:MessageTracking" subject="**** - Success" directionality=Originating psender="********" 
| earliest(_time) as _time stats dc(message_subject) as message_count
| eval now = now(), cutoff = relative_time(now,"@d+11h+30m")
| eval color = case(cutoff > now,"green",message_count=0, "red",_time < cutoff,"green",true(),"red")

So we add the earliest time for the matching even into the search.  Then we create a variable for now (when the search is running) and then use that to determine 11:30am today.  Then we can apply the logic.  The case() statement will run through each condition/value pair until it finds one that is true.  

So if it is now before 11:30am, then green.  Otherwise, it's past 11:30am.  In that case, if we have 0 messages, then red.  Otherwise if we do have at least one message and it's timestamp is before 11:30am, then green.  And then the default condition will be red - at least one message but the timestamp is after 11:30.

I did assume that it's running today for today but that can be more generlalized if needed I think.

[vishalduttauk]

Thanks for your reply and apologies for not being clear.  I've added a screenshot of the dashboard tile and these are the conditions (as the timeframe is set to Today).

1. The tile to be green if the value is 0 and the time is before 11.29am

2. The tile to be green if the value is 1 at any time of day . 

3. The tile should only turn red if the value is 0 and the time is after 11.30.