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

[ssuluguri]

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  

[ITWhisperer]

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] groupby host
| append [|inputlookup Firewall_list.csv | table Primary | Rename Primary AS host | eval count=0]
| stats sum(count) as count max(latest_event_time) AS latest_event_time by host

[ssuluguri]

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 .

[PickleRick]

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?

[yuanliu]

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.

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

[ssuluguri]

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

[yuanliu]

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

[ssuluguri]

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 .

| 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
| append [|inputlookup Firewall_list.csv | table Primary | Rename Primary AS host | eval count=0]
| stats sum(count) as count max(latest_event_time) AS latest_event_time by host |rename host as Pri

|append [| tstats count max(_time) AS latest_event_time where index=firewall sourcetype="cisco:ftd" [| inputlookup Firewall_list.csv | table Secondary | Rename Secondary AS host] groupby host
| append [|inputlookup Firewall_list.csv | table Secondary | Rename Secondary AS host | eval count=0]
| stats sum(count) as count max(latest_event_time) AS latest_event_time by host |rename host as Sec]

Host are in lookup 

[ITWhisperer]

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

[ssuluguri]

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

[ssuluguri]

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 .