Archive

Query using inputlookup as primary, with nested query

tlmayes
Communicator

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.

index=
| 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

Tags (1)
0 Karma

starcher
SplunkTrust
SplunkTrust
 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
0 Karma

tlmayes
Communicator

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

0 Karma

somesoni2
Revered Legend

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

Updated

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
0 Karma

tlmayes
Communicator

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

0 Karma

tlmayes
Communicator

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.

0 Karma

somesoni2
Revered Legend

Try the updated answer.

0 Karma

tlmayes
Communicator

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
0 Karma

somesoni2
Revered Legend

Few questions:

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??

0 Karma

tlmayes
Communicator
  1. Yes, corrected to read host=server4
  2. Yes, but that was just for testing limitation (get it to work). Ultimately I want all fields in the lookup table displayed.

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.

0 Karma