I need to group by a field where all possible values should be shown in the result.
For example, the below snippet groups by interface, but rows can be omitted if the query does not return results for an interface.
<search> | stats count(state='success') as count by interface
For example, three interfaces exist. [A, B, C]. The search has no results for C.
Output
interface count
A 100
B 200
Missing Record
C 0
How can any missing records be included? Any option where a lookup table is not used?
Something along these lines
| makeresults
| eval _raw="interface count
A 100
B 200"
| multikv forceheader=1
| stats sum(count) as count by interface
``` Up to here simulates your example ```
``` Use this to append/resolve all expected interfaces ```
| append [
| makeresults
| fields - _time
| eval interface = split("A,B,C", ",")
| mvexpand interface
| eval count=0
]
| stats max(count) as count by interface
Just append the required interfaces to the and and then resolve them with stats. It's effectively the same as how you do it with a lookup, but here you are just manufacturing the required values.