topic Re: Query using inputlookup as primary, with nested query in Splunk Dev

Help with query using inputlookup as primary, with nested query

tlmayes — Fri, 20 May 2022 19:48:06 GMT

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

Re: Query using inputlookup as primary, with nested query

somesoni2 — Thu, 14 Sep 2017 14:55:38 GMT

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

Re: Query using inputlookup as primary, with nested query

starcher — Thu, 14 Sep 2017 16:55:06 GMT

 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

Re: Query using inputlookup as primary, with nested query

tlmayes — Thu, 14 Sep 2017 16:56:28 GMT

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

Re: Query using inputlookup as primary, with nested query

tlmayes — Thu, 14 Sep 2017 17:09:53 GMT

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.

Re: Query using inputlookup as primary, with nested query

somesoni2 — Thu, 14 Sep 2017 19:03:56 GMT

Try the updated answer.

Re: Query using inputlookup as primary, with nested query

tlmayes — Thu, 14 Sep 2017 19:28:26 GMT

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

Re: Query using inputlookup as primary, with nested query

tlmayes — Thu, 14 Sep 2017 19:30:58 GMT

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

Re: Query using inputlookup as primary, with nested query

somesoni2 — Thu, 14 Sep 2017 19:40:51 GMT

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

Re: Query using inputlookup as primary, with nested query

tlmayes — Thu, 14 Sep 2017 23:26:28 GMT

Yes, corrected to read host=server4
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.

Re: Query using inputlookup as primary, with nested query

Taruchit — Fri, 20 May 2022 19:38:37 GMT

Hi All,

I have the below SPL: -

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.

Thank you

Re: Query using inputlookup as primary, with nested query

ITWhisperer — Sat, 21 May 2022 07:18:35 GMT

Does this work for you?

Re: Query using inputlookup as primary, with nested query

Taruchit — Sat, 28 May 2022 18:42:12 GMT

Thank you sir, it works successfully.

Re: Query using inputlookup as primary, with nested query

Taruchit — Wed, 01 Jun 2022 18:04:25 GMT

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.

host	cluster
10.0.0.1	A
10.0.0.2	A
10.0.0.3	A
10.0.0.4
10.0.0.5

Thank you