stats results using lookup and index fields, event count =0 or more, so I can report on something that does not exist

[tlmayes]

I've seen many fine examples on how to present stats results even if a zero output, but for some reason I cannot get it to work in my environment. Not sure if there is a loop causing false output or not.

One of my users created a lookup table that has only two columns: Location, ApplianceName. He created an "Automatic Lookup" using both field names, so both show up when executing a query against the index=proxies. All entries in the lookup table field ApplianceName have a match in the index. Only some of the entries in the lookup table field Location have a match.

What they want, is an event count from the index, by Location, and want zero to display where there are no events. Cannot seem to wrap my head around how to report on something that does not exist (i.e. the Location will not appear in the index).

  'index=proxies Location=* | stats distinct_count(Location) = 16 Locations'
   '| inputlookup proxy_appliances | stats count BY Location = 23 Locations'

How do I generate a list of events, per location, listing all 23 locations?

[somesoni2 · Accepted solution]

Try like this

index=proxies Location=* | stats count by Location
| append [| inputlookup proxy_appliances | eval count=0]
| stats max(count) as count by Location

[tlmayes]

Had seen similar queries in "answers" but was obviously getting something wrong. Your feedback worked exactly as expected. Thanks