I have an inputlookup table that has a list of details, specifically IP's. The user wanted a list of all IP's that existed in both the index and the inputlookup so I wrote a query similar to the following which lists ONLY the IP's that exist in both locations.
| dedup clientip
| search [inputlookup file.csv | table clientip]
| table IP, host
Now they want a query that lists all IP's in the inputlookup file in the output, noting whether or not they were found or not in the index (an eval statement?). Essentially list all hits AND misses
index=myindex [ | inputlookup file.csv | table clientip] | stats count by host, clientip | fields - count
If you use the table as a filter assuming the number of rows is relatively low you can just do the above.
If the table is large, say less than a hundred.
index=myindex | lookup file.csv clientip OUTPUTNEW clientip AS isFound | where isnotnull(isFound) | stats count by host, clientip | fields - count
Thanks starcher, but only give me the count. Trying to get the output to be an updated lookup table output, with some type of notation that a record does or does not exist in the index, but maintaining all of the original lookup table records
If they just want to list IP's from lookup table, why not just use
inputlookup file.csv only? Unless you're adding few fields from index, above should work.
If you're getting additional field from index, use like this
index=* [inputlookup file.csv | table clientip] | dedup clientip | inputlookup file.csv append=t | stats values(IP) as IP values(host) as host by clientip | table IP, host clientip
index=* [inputlookup file.csv | table IP | rename IP as clientip] | stats count by clientip host | fields - count | rename clientip as IP | inputlookup file.csv append=t | stats values(*) as * by IP
Ultimately desired output should be:
- All rows & columns (fields) in the lookup (roughly 12 fields), whether or not there is a relationship
- (1) additional field that lists the "host" from the index where there is a match between the field clientip in the index and the field IP in the lookup table
To the question of "if they just want a list of IP's from a lookup table, why not just use inputlookup". They want a list of IP's found in the lookup table, with a notation of which IP's are also found in the index.
Hmmm.... I am sure I am missing some detail. Seems the output is only giving me the contents of the lookup table with none of the logic, i.e. the output of the below script is exactly the same as if I was looking at the raw CSV (no correlation indicator).
index=_* host=server8 OR host=server7 OR host=server4 "services/broker/phonehome/connection" | rename host AS Manager, clientip AS IP | search [inputlookup 2017-09-statistics.csv | table IP, DOMAIN, Manager] | stats count by IP, Manager | fields - count | inputlookup 2017-09-statistics.csv append=t | stats values(*) as * by IP
1) Is it really
host=server7 OR server4 OR should be
host=server7 OR host=server4? The former searches for literal string server4.
2) Line 3 is adding field DOMAIN also as filter. Does your data in indexes had field DOMAIN??
The output should be:
1. All fields found in the lookup table
2. Plus the field "Manager" with an entry pulled from the index for every IP that exists in both index and lookup table. The Manager field is a default field from the Forwarder Manager.