How can I use a search results table to power another search per line?

[elmiko]

I have a search that returns a table like this:

IPAddress1  StartDate1  EndDate1
IPAddress2  StartDate2  EndDate2
IPAddress3  StartDate3  EndDate3

I'd like to have another search to find data about each IP address between the start and end dates in the table. Is there any way to do this and have the results combined into a single result set?

Put another way, given the table above, I'd like to combine the following three searches into one:

sourcetype=blah src=*IPAddress1* earliest=*StartDate1* latest=*EndDate1*
sourcetype=blah src=*IPAddress2* earliest=*StartDate2* latest=*EndDate2*
sourcetype=blah src=*IPAddress3* earliest=*StartDate3* latest=*EndDate3*

The caveat being that I don't know in advance how many entries the table will have. Does anyone know of a non-manual way of doing this?

Thanks in advance for any advice you can give!

[somesoni2 · Accepted solution]

If the number of rows in the first table is not too high (1K to 5K preferably), they your can use the subsearch to generate that giant OR condition. Not sure the format of the field StartDate and EndDate in your data, so I'm assuming it as %Y-%m-%d %H:%M:%S. Please replace the same with whatever format you've your dates in.

sourcetype=blah [search "your first search giving field IPAddress, StartDate and EndDate | convert mktime(*Date) as *Date timeformat="%Y-%m-%d %H:%M:%S" ]  | rest of second search

[elmiko]

OK, I figured it out! Thanks, somesoni2 for pointing me the in the subsearch direction!

It turns out that if you want to use the subsearch to return parameters like earliest and latest, you have to add a format command at the end, because the default implicit format puts ANDs between each column in the return, so that without format the subsearch returns this:

((src=IPAddress1 AND earliest=StartDate1 AND latest=EndDate1) OR (src=IPAddress2 AND earliest=StartDate2 AND latest=EndDate2) OR ... )

which gives an error.

So, in the context of my original example, here's what ended up working:

sourcetype=blah [ "original search string that returns IPAddress,StartDate, and EndDate" | rename StartDate as earliest, EndDate as latest, IPAddress as src | fields + src,earliest,latest | format "(" "" "" "" "OR" ")" ]

The format command makes the subsearch return

((src=IPAddress1 earliest=StartDate1 latest=EndDate1) OR (src=IPAddress2 earliest=StartDate2 latest=EndDate2) OR ... )

which gives me the results that I was looking for. In this case the StartDate and EndDate in the original query were splunk timestamps already, so I didn't have to use mktime to convert them. Just renaming them worked like a charm.

Hopefully this answer will help someone else having a similar problem!

[elmiko]

First, thanks for the answer! It's helped me to get a bit closer, but I'm still not getting what I'm looking for. Here's what happens:

If I run your search as written, I get 0 results. I can get results if I modify it to add

| rename IPAddress as src | fields + src ]

to the end of the subsearch, but then it's giving me results for any IPAddress that appears in my initial table, only over the entire time range. I would like the search to only return events that occur between StartDate1 and EndDate1 for IPAddress1, between StartDate2 and EndDate2 for IPAddress2, and so on.

I tried adding

|rename IPAddress as src, StartDate as earliest, EndDate as latest | fields + src,earliest,latest ]

to the end, but then I get an error: Error in 'search' command: Unable to parse the search: Invalid search: AND AND.

This makes sense, since earliest and latest are search parameters and not fields, but I can't think of any other way to return the start and end parameters from the subsearch.

Any other ideas on how to parameterize the outer search from within the subsearch, or any other way to do what I'm looking for?