Why is outer join not working to find how many IPs in a lookup file are not found in the metrics.log?

[shaileshmali]

I am trying to write a search that will give me syslog log sources not sending logs to splunk

I have 1) metrics logs with information on udp log sources.
2) syslog_lookup with list of syslog devices integrated with splunk

I am trying to see how many of ip's in lookup file are not found in metrics logs, but i always get results with all the entries in lookup file. syslog sources sending data should not be shown in this search

| inputlookup syslog_lookup | fields Ip,Data_Source| dedup Ip | rename Ip as hostip | join type=outer hostip [ search index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" "group=udpin_connections"] | fields Ip,Data_Source,hostip

[acharlieh · Accepted solution]

Not being as readily familiar with metrics.log as I should be, I'm assuming that

| inputlookup syslog_lookup | dedup Ip | rename Ip as hostip

gives the hostips in the lookup and

index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" "group=udpin_connections" | dedup hostip 

gives the hostips that have reported into splunk

One approach you could take is:

index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" "group=udpin_connections" | fields hostip | dedup hostip | eval reported="yes" | append [inputlookup syslog_lookup | fields Ip | dedup Ip | rename Ip as hostip | eval known="yes"] | stats first(*) as * by hostip | fillnull value="no" reported known | where reported="no" OR known="no"

Get all reported ips, and mark them as reported yes, get all known ips and mark them as known yes. combine all records per ip and set "no" on the reported and known fields that do not have "yes". Keep only those with at least one "no".

[shaileshmali]

Thanks acharlieh

Your solution worked for me.

to make it simpler I modified syslog lookup to use hostip field name instead of Ip.

index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" "group=udpin_connections" | fields hostip | dedup hostip | eval reported="yes" | append [inputlookup syslog_lookup | eval known="yes"] | stats first(*) as * by hostip | fillnull value="no" reported known | where reported="no" OR known="no"

[vasanthmss]

Try this,

index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" "group=udpin_connections" NOT [| inputlookup syslog_lookup | fields Ip,Data_Source| dedup Ip | rename Ip as hostip|return hostip] | fields Ip,Data_Source,hostip

Cheers!
V

[shaileshmali]

I modified lookup column name from ip to hostip. now both lookup as well as logs have hostip field. I tried search as you suggested but I get outcome as 37 which is common ip's between logs and lookup . I want exactly opposite result , ip which are in lookup but not in logs.

index=_internal source="/opt/splunk/var/log/splunk/metrics.log*" "group=udpin_connections" NOT [| inputlookup syslog_lookup | fields hostip| dedup hostip | return hostip] | stats count by hostip