Splunk Dev

Help with query using inputlookup as primary, with nested query

tlmayes
Contributor

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

Labels (1)
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

Taruchit
Contributor

Hi All,

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. 

Thank you

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Taruchit
Contributor

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.

hostcluster
10.0.0.1A
10.0.0.2A
10.0.0.3A
10.0.0.4 
10.0.0.5 


Thank you

0 Karma

Taruchit
Contributor

Thank you sir, it works successfully. 

0 Karma

tlmayes
Contributor

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

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
Contributor

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
Contributor

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

Try the updated answer.

0 Karma

tlmayes
Contributor

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

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
Contributor
  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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...