Green/Red indicator of health

[dwong2]

I have a basic search that returns multiple results.

| stats count by activity

....which returns these results.

activity counts
Open 24
Closed 2
Conflict 5
Empty 100

Is there a way to create a report or dashboard to show green or red for each respective activity. If there is at least 1 count then red, and if there are no counts per activity then green?

[diogofgm]

Add a table to you dashboard with your search.
On the top of the count column you’ll have a pencil to edit the column. There you can make custom behavior like changing color depending on a range, value, etc

[dwong2]

Thank you. This should work...thought there was some kind of "magic" spl .

Do you know how to show the activity with 0 counts as well? Right now I only see the activity ones with counts 1 or greater.

[diogofgm]

you only see the ones with activity because its your "by" clause. You can add something like this before your stats command:

|eval activity = coalesce(activity,"No activity")

What this does is every event will have the activity field filed with whatever comes first as not null in the coalesce. Meaning:

• if an event has activity = "open" the coalesce result will be "open" so activity value will be "open"
• if and event doesn't have activity the coalesceresult will be "No activity" so activity value will be "No activity"

This way the "by activity" clause will have results for all events you are searching.
NOTE: this, depending on your raw data, might need some tweaking. But just play with it. coalesce can take any number of fields and returns always the first not null value.

Other approach, if you do not want to have the "No activity" result is to use lookups and join.
Step 1
Build a csv (e.g activity_list.csv) with the activity and count fields
"activity","count"
"open","0"
"closed","0"
...

Step 2
after your lookup use:
| join activity type=left [|inputlookup activity_list.csv ]

this will add whatever activity is missing from the results and present in the csv with count as "0"

Hope this helps!

[dwong2]

Not sure how to use lookups but I created a csv file like your example comma separated in one column. So I created the lookup table followed by the lookup definition. I am unsure to use the lookup.

index=mysearch sourcetype=mysource
| join activity type=left [|inputlookup activity.csv]
| chart count by activity

[dwong2]

I'm looking to have something along these lines:

open 8
closed 4
stuck 0
locked 0