How to count for all hosts from lookup?

[Skysurfer]

I have a query that I am using to get the count of events

index=system source=/var/log/syslog/* | rex field=source "(?<host_name>[^\"]*)" | stats count by host_name

Now have a lookup file hf_lookup.csv where there is column as hf_name. This hf_name is the host_name from the above query. I want to get the count against each value of hf_name. Even if the count is 0 for a hf_name, it should be displayed as 0.

Tried using inputlookup with left join and "fillnull value=0 count" but either I am only getting count=>1 or for the hosts that are not in hf_lookup.csv.

[woodcock]

index=system source=/var/log/syslog/*
| rex field=source "(?<hf_name>[^\"]*)"
| stats count by hf_name
| eval which = "data"
| inputlookup append=t hf_lookup.csv
| eval which = coalesce(which, "lookup")
| eval count = coalesce(count, "0")
| stats values(which) AS whiches dc(which) AS whichCount first(count) AS count BY hf_name
| rename hf_name AS host_name

[bowesmana]

So, are you saying that if you find hostname=A and hostname=B with counts 10 and 20 in your search and your lookup file contains hosts A, B and C, you want to see

A=10, B=20, C=0

If so, after your existing search do

| append [
  | inputlookup hf_lookup.csv
  | eval count=0
  | rename hf_name as host_name
]
| stats max(count) as count by hostname