How to group events based on a fields with predefined values?

[ChrisPatin]

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?

[bowesmana]

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.