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
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
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
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