I have a lookup table that I created that only has ip address and hostnames. I want to run the following search against the lookup table but I am not getting the results I expect.
index=_internal sourcetype=splunkd [inputlookup dmzhosts.csv | table ip | rename ip as search | format] group=tcpin_connections NOT eventType=* | stats max(_time) as last_connected, sum(kb) as sum_kb by guid, hostname | addinfo | eval "Source Host" = hostname | eval ttnow = now() | eval Current = strftime(ttnow,"%m-%d-%Y %H:%M:%S") | eval Status = if(isnull(sum_kb) or (sum_kb <= 0) or (last_connected < (info_max_time - 60)), "Not Reachable", "active") | eval "Last Connected" = strftime(last_connected,"%m-%d-%Y %H:%M:%S") | where Status = "Not Reachable" | table "Source Host" "Last Connected" Current Status
The search seems to run but I know it isn't really working as the lookup table has 160 IP addresses and the events only show 46 sourceIp's. What I really need is the is a for loop it seems so that the search will set the sourceIp to the ip from the lookup table and then provide a list of all the ones that are missing at the end of the search. Ideas?
I have a lookup file that I use to store IP,Host pairs on each line. I use this in my searches:
| lookup SensitiveIPs ip AS IP OUTPUTNEW ip AS isFound, host as Hostname | eval eiris=if(isnull(isFound), "No","Yes")
SensitiveIPs is an alias for the .csv file in $SPLUNK_HOME/etc/apps/search/lookups/sensitive_ips.csv
I think this is what I am looking for
index=* [inputlookup dmzhosts.csv | table ip | rename ip AS host | format] OR [inputlookup dmzhosts.csv | table host | rename host AS host | format]
| eval host=upper(host)
| stats count by host
| append [inputlookup dmzhosts.csv | eval count=0, host=upper(host) | fields host, count]
| stats sum(count) AS Total by host
| where Total=0
Yes, that looks better because in the first try you used
rename ip as search which will mess up the returned list of results. Usually I test the lookup based subsearch on its own until
format returns the list I want to search for or want to exclude from a search.
Basically I have 46 systems with a UF installed and I can find those but the other 114 which is probably syslog data or netscaler data how can I use the lookup table to find that data and prove we are collecting it?