return results even when the count of a field value is zero

[JyPl4wNYu7GV1uL]

Stupid form editor adds extra CRs.

Having trouble getting this search to work as desired. I've tried these 2 methods and can't them to work:

eventtype="x" Name="x" 
     | fields Name, host 
     | dedup host
     | stats count by host
     | appendpipe [stats count | where count=0 | eval host="Specify your text here"]

and using the

fillnull

command.

Here is my search:

index=idx1 host=host1 OR host=host2 source=*filename*.txt field1!=20250106 (field2="20005") OR (field2="20006") OR (field2="20007") OR (field2="666") | stats count(field2) by field2, field3 | sort count(field2)

In this case the value for field2="666" does not exist in the results. Here're the results I get:

  field2 field3                   count(field2)
1 20005  This is field3 value 1   2
2 20006  This is field3 value 2   6
3 20007  This is field3 value 3   13

To summarize, I want to search for all the values of field2 and return the counts for each field2 value even if the field2 value is not found in the search; so, then, count(field2) for field2=666 would be 0. As follows:

  field2  field3                  count(field2)
1 666     <empty string>          0
2 20005   This is field3 value 1  2
3 20006   This is field3 value 2  6
4 20007   This is field3 value 3  13

This is a simplified example. The actual use case is that I want to search one data set and return all the field2 values and then search for those values in the first data set.

This actual search I'm running looks like this:

index=idx1 host=host1 OR host=host2 source=*filename*.txt field1!=20250106 [search index=idx1 host=host1 OR host=host2 source=*filename*.txt field1=20250106 | fields field2 | dedup field2 | return 1000 field2]
| stats count(field2) by field2, field3 | sort count(field2)

I want to find all the field2 values when field1=20250106 and then find the counts of those values in the field1!=20250106 events (even for when the count of some field2 values have count=0 in results).

[yuanliu]

As @bowesmana exemplifies, putting your complete set of values in a lookup is one way to count "missing" values.  Another way to is to put them in an multivalued field and use this field for counting.  Here is an example

index=idx1 host=host1 OR host=host2 source=*filename*.txt field1!=20250106
 (field2="20005") OR (field2="20006") OR (field2="20007") OR (field2="666")

| eval field2prime = mvappend("20005", "20006", "20007", "666")
| mvexpand field2prime
| eval field2match = if(field2 == field2prime, 1, 0)
| stats sum(field2match) as count by field2prime
| rename field2prime as field2

Here is an emulation you can play with and compare with real data:

| makeresults count=16
| streamstats count as _count
| eval field2 = round(_count / 😎 % 3 + 20005
``` the above emulates
index=idx1 host=host1 OR host=host2 source=*filename*.txt field1!=20250106 (field2="20005") OR (field2="20006") OR (field2="20007") OR (field2="666")
```

Mock data looks like this:

fiel2
20005
20005
20005
20006
20006
20006
20006
20006
20006
20006
20006
20007
20007
20007
20007
20007

If you count this against field2 directly, you get

field2	count
20005	3
20006	8
20007	5

Using the above search, the result is

field2	count
20005	3
20006	8
20007	5
666	0

[bowesmana]

You can't count non-existence of a field value if that value does not exist unless you know what values are expected - that is generally termed the 'proving the negative' in these forums.

You would typically have a lookup file of the expected values for field 2, e.g. if you have a csv with field2 having 2 values 666 and 999 and in your search you get field2 for value 999 has N results but no 666 results, then this at the end will add a 0 for all missing expected values

| inputlookup append=t field2.csv
| stats max(count) as count by field2
| fillnull field2