I have a basic search that returns multiple results.
| stats count by activity
....which returns these results.
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?
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.
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:
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.
Build a csv (e.g activity_list.csv) with the activity and count fields
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!
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.
| join activity type=left [|inputlookup activity.csv]
| chart count by activity