How to modify my dashboard design so that it displays all sourcetypes and their current status in one column?

[jhampton3rd]

I'm trying to redesign a dashboard that currently gives the status of certain sourcetypes. The dashboard is working but I'd like to change the format a little. Instead of the two columns showing how many sourcetypes that are up or down. I'd like just one column showing if all of the source types are running or if just one or more is down displaying "RUNNING", "DOWN" or OFFLINE/EVENT IN THE FUTURE".

Here's the search:

| tstats latest(_time) WHERE index=* sourcetype=* by index host sourcetype 
| search sourcetype!="*too_small"  
| search  ( OMITTED ) 
| rename latest(_time) as latest 
| eval unixTimeNow=now() 
| eval timeDiff = unixTimeNow - latest
| eval timeDiff=if(timeDiff>=0, timeDiff, 0)  
| eval status=case(timeDiff>= 1 AND timeDiff < 1800, "RUNNING", 
                  timeDiff> 1801, "DOWN", 
                  timeDiff=0, "OFFLINE/EVENT IN THE FUTURE") 
| table host, sourcetype, timeDiff, status 
| eventstats count(eval(status="RUNNING")) as up by sourcetype 
| eventstats count(eval(status="DOWN")) as down by sourcetype 
| stats values(up) as RUNNING, values(down) as DOWN by sourcetype 
| rename sourcetype AS Sourcetype 
| sort Sourcetype

I also want to keep the capability to click on either "RUNNING", "DOWN" OR "OFFLINE" to see the list of hosts that are or aren't reporting.

Thanks for your help.

[jhampton3rd]

Thanks everyone for your help. Was able to use everything you guys answered with to come up with the following fix:

| eval timeDiff = now()-latest   
| eval status=case(timeDiff>=1 AND timeDiff<=1800, "RUNNING",timeDiff>=1801, "DOWN", timeDiff<=0, "OFFLINE") 
| fields host, sourcetype, status  
| chart count by sourcetype status 
| eval state=case(RUNNING>0 AND DOWN<=0,"RUNNING",DOWN>0, "DOWN",OFFLINE>0, "OFFLINE/EVENT IN THE FUTURE") 
| fields sourcetype state

This did exactly what I was looking for. Thanks again

[lguinn2]

Updated based on your recent post:

| tstats latest(_time) as latest WHERE index=* sourcetype!="*too_small" by host sourcetype 
 | search ( OMITTED )
 | eval timeDiff = now() - latest
 | eval status=case(timeDiff>=1 AND timeDiff<=1800, "RUNNING", 
                    timeDiff>=1801, "DOWN", 
                    timeDiff<=0, "OFFLINE")
 | chart count by sourcetype status
 | eval Status=case(DOWN>0,"DOWN",
                    OFFLINE>0,"OFFLINE/EVENT IN THE FUTURE",
                    RUNNING>0,"RUNNING",
                    1==1,"Unknown")
| rename sourcetype as Sourcetype | fields Sourcetype Status

Now there is only 1 field. The case function implements these rules:
- if any host is DOWN, the overall status is DOWN (even if there are some hosts that are offline or running.)
- if no hosts are down, but at least one host is OFFLINE, the overall status is "OFFLINE/EVENT IN THE FUTURE"
- if no hosts are down or offline, and at least one host is running, the overall status is RUNNING
- Otherwise (this should never happen), the status is unknown.

[jhampton3rd]

I was able to almost get what I wanted. Instead of there being two columns, I would like for it to be just one column saying either "DOWN" or "RUNNING". I'm trying to figure out how to put a clause in this search where if the sourcetype status has a DOWN and RUNNING, it will display the word "DOWN". If sourcetype is RUNNING, it will display the word "RUNNING". If the sourcetype status is DOWN, it will display the word "DOWN" The reason it's showing like this in the screenshot, the sourcetypes has many hosts so there may be some hosts that are down and some that are running at the same time.

Here's the search string:

| tstats latest(_time) as latest WHERE index=* sourcetype!="*too_small" by host sourcetype | search ( OMMITTED )| eval timeDiff = now() - latest | eval status=case(timeDiff>=1 AND timeDiff<=1800, "RUNNING", timeDiff>=1801, "DOWN", timeDiff<=0, "OFFLINE")| table host, sourcetype, status | chart count by sourcetype status | eval RUNNING=if(RUNNING>0,"RUNNING",null()) | eval DOWN=if(DOWN>0,"DOWN",null()) | eval OFFLINE=if(OFFLINE>0,"OFFLINE/EVENT IN THE FUTURE",null())

Thanks for the help

[lguinn2 · Accepted solution]

First, the ability to drill-down is not part of the search, it is in the simple XML for the dashboard. So, without seeing how that is set up, there is no way to know for sure if an alternate solution will still provide this functionality. Hint: it probably will need revision if you change the rows or columns of the results.

Second, I'm having a hard time visualizing what you want to change. As I read the search, I believe it currently produces a table with the columns: Sourcetype RUNNING DOWN. I think you want a result with just 2 columns: Sourcetype Status, where the Status column could be one or more values, without a count.

Third, I think your search can be streamlined a bit.

Given the above, this is what I've come up with

| tstats latest(_time) as latest WHERE index=* sourcetype!="*too_small" by index host sourcetype 
| search ( OMITTED )
| eval timeDiff = now() - latest
| eval status=case(timeDiff>=1 AND timeDiff<=1800, "RUNNING", 
                   timeDiff>=1801, "DOWN", 
                   timeDiff<=0, "OFFLINE/EVENT IN THE FUTURE")
| stats values(status) as Status by sourcetype
| rename sourcetype AS Sourcetype 

Or, to preserve the drill-down capability:

| tstats latest(_time) as latest WHERE index=* sourcetype!="*too_small" by index host sourcetype 
| search ( OMITTED )
| eval timeDiff = now() - latest
| eval status=case(timeDiff>=1 AND timeDiff<=1800, "RUNNING", 
                   timeDiff>=1801, "DOWN", 
                   timeDiff<=0, "OFFLINE")
| chart count by sourcetype status
| eval RUNNING=if(RUNNING>0,"RUNNING",null())
| eval DOWN=if(DOWN>0,"DOWN",null())
| eval OFFLINE=if(OFFLINE>0,"OFFLINE/EVENT IN THE FUTURE",null())

You may still need to re-work the drill-down connection a bit.

Finally, I am not sure that you need the index in the by clause of the first line, in either version. I think it could simply be

| tstats latest(_time) as latest WHERE index=* sourcetype!="*too_small" by host sourcetype 

But I wasn't sure.

[jhampton3rd]

Thanks for the quick response. You definitely put me on the right path. I'm now trying to consolidate the two columns into one column.