topic Re: How to get count 0 for the field value which is not matching with events in Splunk Search

How to get count 0 for the field value which is not matching with events

ssuluguri — Thu, 01 Aug 2024 23:37:00 GMT

Hi Splunkers,

My requirement is below .

I have lookup where 7 hosts defined . when my search is running for both tstats and stats I only get 5 hosts count which are greater than 0 . Can someone help how can we get count 0 for the field which we are passing from lookup .

Query :

| tstats count max(_time) AS latest_event_time where index=firewall sourcetype="cisco:ftd" [| inputlookup Firewall_list.csv | table Primary | Rename Primary AS host] groupby host

Re: How to get count 0 for the field value which is not matching with events

ITWhisperer — Thu, 01 Aug 2024 21:06:49 GMT

Try something like this

Re: How to get count 0 for the field value which is not matching with events

yuanliu — Thu, 01 Aug 2024 23:25:31 GMT

Additionally, you should drop the subsearch with inputlookup because either the lookup contains any host that could ever have, or you should want to catch any count from hosts that are not in the lookup.

Re: How to get count 0 for the field value which is not matching with events

ssuluguri — Fri, 02 Aug 2024 18:20:59 GMT

Thanks for your time , I see the data which is coming 0 for each entry coming from lookup but it should give only value 0 for the host which is not sending events .

Re: How to get count 0 for the field value which is not matching with events

ssuluguri — Fri, 02 Aug 2024 18:22:25 GMT

This is not giving results which needed , I see 0 for each entry from lookup

Re: How to get count 0 for the field value which is not matching with events

yuanliu — Sat, 03 Aug 2024 03:47:55 GMT

First of all, you need to illustrate your data and corresponding lookup entries to prove that the output is incorrect. Otherwise it is just that your lookup has nothing matching raw event.

Second, from a glance, it looks like your events uses lower case whereas your lookup values are in upper case. You need to ask yourself: Is this necessary? If it is necessary, does your lookup have case_sensitive_match=0? A second question you need to answer is: the lookup contains wildcards. Is the lookup set up with matchtype=WILDCARD(host)?

Re: How to get count 0 for the field value which is not matching with events

PickleRick — Sat, 03 Aug 2024 08:32:48 GMT

Your lookup seems to contain wildcarded entries. How is Splunk supposed to know what hosts this should match (assuming you even have your lookup defined correctly with a wildcard match) if you have no events to match with?

Re: How to get count 0 for the field value which is not matching with events

ssuluguri — Mon, 05 Aug 2024 18:22:50 GMT

Thanks a lot it worked .

Adding to that I m passing two values from lookup as below , if both hosts event count is zero then I need the result to display the host name which are passing from lookup . Can you help me here .

Re: How to get count 0 for the field value which is not matching with events

ssuluguri — Mon, 05 Aug 2024 18:36:42 GMT

Overall our requirement is that " We are passing a lookup where both Primary and Secondary devices defined , if only both stopped sending the events then we need to display the hosts "

this is the query I prepared , but not giving the exact requirement , can you optimize , help .

Host are in lookup

Re: How to get count 0 for the field value which is not matching with events

ITWhisperer — Mon, 05 Aug 2024 23:07:19 GMT

Try something like this

| tstats count max(_time) AS latest_event_time where index=firewall sourcetype="cisco:ftd" [| inputlookup Firewall_list.csv | table Primary | Rename Primary AS host] OR [| inputlookup Firewall_list.csv | table Primary | Rename Secondary AS host] groupby host ``` append host (Primary) and Primary for all primaries ``` | append [|inputlookup Firewall_list.csv | table Primary | eval host=Primary | eval count=0] ``` append host (Secondary) and Primary for all secondaries ``` | append [|inputlookup Firewall_list.csv | rename Secondary as host | eval count=0] ``` count for all hosts noting last event time and Primary ``` | stats sum(count) as count max(latest_event_time) AS latest_event_time values(Primary) as Primary by host ``` find all host not reporting ``` | where count = 0 ``` count hosts for each Primary not reporting ``` | eventstats count as hosts_not_reporting by Primary ``` find where both hosts are not reporting ``` | where hosts_not_reporting = 2

Re: How to get count 0 for the field value which is not matching with events

ssuluguri — Wed, 07 Aug 2024 17:26:55 GMT

Appreciated for the time you spent on it , it worked .