topic Re: Generate values for IN search in Splunk Search

Generate values for IN search

makelovenotwar — Thu, 31 Aug 2023 20:08:56 GMT

How do I use a search to generate values to use inside of an IN search? For example:

index=syslog src_ip IN ( | tstats count from datamodel=Random by ips | stats values(ips) as IP | eval IP = mvjoin(IP, ",")

I tried the method above but it's not working. Thank you!

Re: Generate values for IN search

richgalloway — Thu, 31 Aug 2023 20:21:56 GMT

Don't bother. IN optimizes to a series of ORs so just start with that.

index=syslog [ | tstats count from datamodel=Random by ips | rename ips as src_ip | fields src_ip | format ]

The subsearch will run first and use the format command to produce a string like "(src_ip=1.2.3.4 OR src_ip=2.3.4.5)" which will become part of the main search.

Re: Generate values for IN search

bowesmana — Fri, 01 Sep 2023 06:57:44 GMT

What @richgalloway is correct, but technically it's possible to format the return value so it can be used in the IN statement - your problem is that you are not crafting a subsearch - you're missing the [] subsearch brackets - but you could do it like this - but you wouldn't really want to...

index=syslog src_ip IN ( [ | tstats count from datamodel=Random by ips | stats values(ips) as IP ``` You could technically do this, but it's not necessary | eval IP = mvjoin(IP, ",")``` ``` Use this return $ statement to return a space separated string but you could technically use the mvjoin and have a comma separated one``` | return $IP ] )

Re: Generate values for IN search

makelovenotwar — Fri, 01 Sep 2023 09:53:30 GMT

Thank you @richgalloway and @bowesmana - I'd accept both as the solution if I could as I learned about the return and format commands from you both. I accepted return as the solution since I wanted to use the IN search, and couldn't format the format command to remove the column names from the generated string. Not sure this is right, but I ended up having to use an eval command to append quotesa and commas to my values, prior to the return statement. In the end, it was something like...

index=syslog src_ip IN ( [ | tstats count from datamodel=Random by ips | stats values(ips) as IP | eval IP = "\"".IP."\"," | return $IP ] )

Thanks again!