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
I have the below SPL: -
| inputlookup table1.csv where index="xxx" | fields index, host | search NOT [search index="xxx" | dedup host | table index, host]
I have table2.csv with following fields: -
index, host, lastTime
I need to search the results from above SPL based on host and index in table2.csv and get the corresponding value of the column: lastTime. Thus, as the final resultset, I need: - index, host, lastTime.
Please help with your suggestions.
Does this work for you?
| inputlookup table1.csv where index="xxx" | fields index, host | search NOT [search index="xxx" | dedup host | table index, host] | lookup table2.csv host OUTPUT lastTime
Hi @ITWhisperer ,
I need your guidance for the below use case: -
I need to fetch list of hosts present in lookup table and not fetched in the index, but with some conditions: -
In lookup table, there are some hosts which have cluster and some which do not have cluster.
Case 1: -
Let us say at present index1 has 2 distinct IP address under field: host => 10.0.0.2, 10.0.0.4
When we search the values against the lookup table to fetch the list of missing hosts, we should get result as: -10.0.0.5
Logic: - 10.0.0.1, 10.0.0.2, 10.0.0.3 are belonging to same cluster in the lookup table. Thus, if any one of them gets fetched, we are fine.
10.0.0.4 does not have a cluster but it is present in the lookup table and also getting fetched.
10.0.0.5 is present in lookup table but not getting reported in the index, thus, it should be given as missing.
Case 2: -
Let us say at present index1 has 1 IP address under field: host => 10.0.0.5
When we search the values against the lookup table to fetch the list of missing hosts, we should get result as: -10.0.0.1, 10.0.0.2, 10.0.0.3, 10.0.0.4
Logic: - 10.0.0.1, 10.0.0.2, 10.0.0.3 are of the same cluster as per lookup table and none of them are getting reported in the index.
10.0.0.4 is present in the lookup table without a cluster but not getting fetched in the index.
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.