How to drop extra fields while maintaining groupby?

[yuanliu]

To groupby?  Or not to groupby?  That is the question. (Not really.  The question arises because trellis splitby seems to depend on an invisible groupby register.)

Take the following example:

| makeresults count=10
| eval value = "value" . random() % 3
| stats count by value
| eventstats sum(count) as total
| eval ratio = count / total

If I want to visualize ratio as single value in trellis, I can add `| fields - total count` in the end.

But if there are many intermediate variables, it gets tedious to list them for dropping.  I thought `table value ratio` would be simpler, as statistics table is exactly the same, but SPL's invisible hand prevents splitby from seeing the original groupby field, so I get weird output like

I can avoid tedious `fields -` listing by doing another stats with groupby, e.g., `stats values(ratio) as ratio by value`.

 But I feel silly to do a useless calculation.  Is there a simpler way to preserve groupby register without the tedious listing?

[yuanliu]

I just realize that I can carefully name intermediate variables so I can drop them with wildcard.  But is there any SPL answer to the original question?

[yuanliu · Accepted solution]

---

is there any SPL answer to the original question?

---

The answer, it turns out, is yes.  Instead of listing intermediate field names for exclusion, name all intermediate fields to be "hidden", i.e., start with underscore _.  The above search can be written as

| makeresults count=10
| eval value = "value" . random() % 3
| stats count as _count by value
| eventstats sum(_count) as _total
| eval ratio = _count / _total

Splunk visualization will conveniently ignore hidden fields.

This writing has more elaborate use cases of this technique: Up Your Textual Viz with Splunk.