Splunk Search

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

shaileshmali
Path Finder

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

acharlieh
Influencer

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

View solution in original post

acharlieh
Influencer

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

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
Motivator

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

V

shaileshmali
Path Finder

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

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...