search based on lookup table help

[edwardrose]

Hello All

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?

thanks
ed

[wrangler2x]

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

[edwardrose]

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

[MuS]

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.

cheers, MuS

[edwardrose]

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?