Splunk Search

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

soumyasaha25
Contributor

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

0 Karma

HiroshiSatoh
Champion

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

soumyasaha25
Contributor

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...