topic Re: Subsearch in Splunk Search

Subsearch

bigll — Fri, 01 Mar 2024 16:55:53 GMT

I have two SPL

#1

index=index1 service IN (22, 53, 80, 8080) | table src_ip

index=index2 dev_ip IN ( value from #1 src_ip) |table dev_ip, OS_Type

----------------------
I try to create a single SPL with sub search
I.e.

index=index2 dev_ip IN ([search index=index1 service IN (22, 53, 80, 8080) | table src_ip]) |table dev_ip, OS_Type

I get an error message
Error in 'search' command: Unable to parse the search: Right hand side of IN must be a collection of literals. '(src_ip = "130.197.32.155")' is not a literal.

Thank you.

Re: Subsearch

isoutamo — Fri, 01 Mar 2024 17:11:32 GMT

You should change this like

index=index2 dev_ip IN ([search index=index1 service IN (22, 53, 80, 8080) | table src_ip | rename src_ip as search]) |table dev_ip, OS_Type

Re: Subsearch

bowesmana — Sun, 03 Mar 2024 22:17:30 GMT

You don't need to use the IN construct when using subsearches, as the default returned from a subsearch is

field=A OR field=B or field=C...

so in practice you can just do

index=index2 [ search index=index1 service IN (22, 53, 80, 8080) | table src_ip | rename src_ip as dev_ip ] | table dev_ip, OS_Type

however, how many src_ips are you likely to get back from this subsearch? If you get a large number, this may not perform well at all. In that case you will have to approach the problem in a different way, e.g.

index=index2 OR (index=index1 service IN (22, 53, 80, 8080)) ``` Creates a common dev_ip field which is treated as the common field between the two indexes ``` | eval dev_ip=if(index=index2, dev_ip, src_ip) ``` Now we need the data to be seen in both indexes, so count the indexes and collect the OS_Type values and split by that common dev_ip field ``` | stats dc(index) as indexes values(OS_Type) as OS_Type by dev_ip ``` And this just ensures we have seen the data from both places ``` | where indexes=2 | fields - indexes

A third way to attack this type of problem is using a lookup, where you maintain a list of the src_ips you want to match for in a lookup table.

Which one you end up with, will depend on your data and its volume as they will have different performance characteristics.

Hope this helps

Re: Subsearch

bigll — Tue, 26 Mar 2024 17:35:04 GMT

Thank you for all updates.
Due to large number of devices I decided to use method #2 from the last post.
My SPL looks like
-------

index=index2 OR (index=index1 sourcetype="metadata" "health.severity"!=NULL) | eval IP_ADDRESS=if(index=index1, interfaces.address, PRIMARY_IP_ADDRESS) ```PRIMARY_IP_ADDRESS is from index2 to match interfaces.address from index1111 | stats dc(index) as indexes values(DISCOVERED_OS) as DISCOVERED_OS by interfaces.address | where indexes=2 | table IP_ADDRESS

________

Query runs with no errors, but produced 0(zero) events 🙂

Thank you, Leon

Re: Subsearch

bowesmana — Wed, 27 Mar 2024 22:24:20 GMT

This statement

| eval IP_ADDRESS=if(index=index1, interfaces.address, PRIMARY_IP_ADDRESS)

will need to have single quotes round the interfaces.address, as eval statements need fields with non-simple characters to be single quoted, in this case the full-stop (.)

| eval IP_ADDRESS=if(index=index1, 'interfaces.address', PRIMARY_IP_ADDRESS)

Note also that index=index1 would need to be index="index1" as you are looking for the value of index to be the string index1 rather than comparing field index to field index1.

As for debugging queries, if you just remove the 'where' clause, you can see what you are getting and what the value of indexes is.

Hope this helps