need to display zero if count is zero for data that is searched dynamically from a lookupfile

[soumyasaha25]

i am matching strings from the lookup file(only has one column with my_field) and then checking occurrence count of each string. This is working perfectly, but the stats only shows the matched strings which has some corresponding count

My Query:

index = abc sourcetype=xyz [|inputlookup | return 100 $my_field]
| rename _raw as rawText
| eval match_field=[|inputlookup |stats values(my_field) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval match_field=split(match_field,",")
| mvexpand match_field
| where like(rawText,"%"+match_field+"%")
| stats values(host) AS HostName count by match_field

Sample output:

match_field count
String1 235
String2 532

I would like to include the strings for which occurrence count is zero, so that the output is like below:

match_field count
String1 235
String2 532
String3 0
String4 0
String5 0
String6 0

Any suggestions on how to get this done

[HiroshiSatoh]

Try this!

index = abc sourcetype=xyz [|inputlookup | return 100 $my_field]
| rename _raw as rawText
| eval match_field=[|inputlookup |stats values(my_field) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval match_field=split(match_field,",")
| mvexpand match_field
| where like(rawText,"%"+match_field+"%")
| stats values(host) AS HostName count by match_field
| append [|inputlookup |table my_field|eval HostName="",count=0 ]
| stats list(HostName ) as HostName ,max(count) as count by my_field

[somesoni2]

In the last subsearch, you need to rename my_field to match_field (to match base search result). Also, last stats should be using match_field.

...your search...
| append [|inputlookup |table my_field | rename my_field as match_field|eval HostName="",count=0 ]
| stats list(HostName ) as HostName ,max(count) as count by match_field

[soumyasaha25]

This is returning all the match_fields count as 0
match_field count
String1 0
String2 0
String3 0
String4 0
String5 0
String6 0

i used the below query
index = abc sourcetype=xyz [|inputlookup my_lookup.csv| return 100 $my_field]
| rename _raw as rawText
| eval match_field=[|inputlookup my_lookup.csv |stats values(my_field) as query | eval query=mvjoin(query,",") | fields query | format "" "" "" "" "" ""]
| eval match_field=split(match_field,",")
| mvexpand match_field
| where like(rawText,"%"+match_field+"%")
| stats values(host) AS HostName count by match_field
| append [|inputlookup my_lookup.csv |table my_field|eval HostName="",count=0 ]
| stats list(HostName ) as HostName ,max(count) as count by my_field