Subsearch

[bigll]

I have two SPL

#1 

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

#2

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.

[bowesmana]

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

[bigll]

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

[bowesmana]

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

[isoutamo]

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