Issue I'm facing:
My use case is to detect a successful ssh login from an external ip_address.
I have my linux logs in: index=linux_logs
These logs have a field called "hostname". "hostname" is sometimes a FQDN and sometimes it's an ip_address. I have an asset list (lookup file), assets.csv. Not all of the FQDN from the linux_logs are in this list.
Here is my initial query:
index=linux_logs sourcetype=syslog exe="/usr/sbin/sshd" res=success NOT hostname=?
| stats count, min(_time) as first_time, max(_time) as last_time, values(dest) as dest, values(hostname) as src by acct
| lookup assets.csv dns AS src OUTPUT ip
| fillnull value=no_ip ip
A sample of the results:
| acct | count | first_time | last_time | dest | hostname | ip |
| user1 | 50 | epoch_time_format | epoch_time_format | host1.mycompany.com | src1.mycompany.com | 10.36.25.14 |
| user2 | 40 | epoch_time_format | epoch_time_format | host3.mycompany.com | src3.mycompany.com | no_ip |
I want to eliminate the RFC1918 and keep the "no_ip" and ip's outside of the RFC1918 ranges. I do have a lookup for the rfc1918 ranges but I'm struggling with how to write the spl to check the "ip" field for what I need. Any help is greatly appreciated.
