Why subsearch fails with error "Unable to parse the search: Invalid search: AND AND" if it has to return endtime?

[gregnsk]

search returns valid results, but fails with Invalid search: AND AND if defined as subsearch:

1. Search works ok:

source=nbstatus requestinghost=icsl2492 User=viefhwd starttime="12/09/2014:16:44:40" endtime="12/09/2014:16:45:40" | convert timeformat="%m/%d/%Y %H:%M:%S" mktime(StartTime) AS stime | convert timeformat="%m/%d/%Y:%H:%M:%S" ctime(stime) AS starttime | convert timeformat="%m/%d/%Y %H:%M:%S" mktime(FinishTime) AS entime | convert timeformat="%m/%d/%Y:%H:%M:%S" ctime(entime) AS endtime |table requestinghost User starttime endtime

requestinghost User starttime endtime
icsl2492 viefhwd 12/09/2014:16:45:37 12/09/2014:16:54:50

2. If I define the above search as a subsearch to join with a different source, it fails:

src=license [search source=nbstatus requestinghost=icsl2492 User=viefhwd starttime="12/09/2014:16:44:40" endtime="12/09/2014:16:45:40" | convert timeformat="%m/%d/%Y %H:%M:%S" mktime(StartTime) AS stime | convert timeformat="%m/%d/%Y:%H:%M:%S" ctime(stime) AS starttime | convert timeformat="%m/%d/%Y %H:%M:%S" mktime(FinishTime) AS entime | convert timeformat="%m/%d/%Y:%H:%M:%S" ctime(entime) AS endtime |table requestinghost User starttime endtime]
Error in 'search' command: Unable to parse the search: Invalid search: AND AND.
The search job has failed due to an error. You may be able view the job in the Job Inspector.

3. If I remove endtime only in the previous query – it works:

src=license [search source=nbstatus requestinghost=icsl2492 User=viefhwd starttime="12/09/2014:16:44:40" endtime="12/09/2014:16:45:40" | convert timeformat="%m/%d/%Y %H:%M:%S" mktime(StartTime) AS stime | convert timeformat="%m/%d/%Y:%H:%M:%S" ctime(stime) AS starttime | convert timeformat="%m/%d/%Y %H:%M:%S" mktime(FinishTime) AS entime | convert timeformat="%m/%d/%Y:%H:%M:%S" ctime(entime) AS endtime |table requestinghost User starttime]
12,004 events (before 12/12/14 3:26:47.781 PM)

4. If I replace a subsearch from #2 with hard coded results of #1 I get the right results:

src=license requestinghost=icsl2492 User=viefhwd starttime="12/09/2014:16:45:37" endtime="12/09/2014:16:54:50" | chart count by result
result
count
DENIED 20

So what is wrong with my query #2?

Thank you,
Gregory

[aromanauskas]

I ran into this exact issue today. I was looking to pass starttimeu and endtimeu values from a subsearch to the parent. The values were based on the results of the subsearch so there was no way to add them to the parent at the start. The solution was the format command and removing AND from the colseperator. To correct the original search add | format mvsep="mvseparator" "(" "(" " " ")" "OR" ")" before the end of the subsearch.

This works since splunk implies AND to all search values.

[aweitzman · Accepted solution]

First thing to do is to check what your subsearch actually is.

Take your search #1 and add | format to the end of it. This will show you the syntax of what you're getting when you use it as a subsearch. If it doesn't look right, you can tweak it by passing parameters to format to get the subsearch you want - see docs for details: http://docs.splunk.com/Documentation/Splunk/6.2.1/SearchReference/format

Once you figure out how to make it what you really want, you can add that format clause to your actual subsearch.

[gregnsk]

thank you. if I add format statement to search #1 I get:
( ( User="viefhwd" AND endtime="12/09/2014:16:54:50" AND requestinghost="icsl2492" AND starttime="12/09/2014:16:45:37" ) )

And indeed if I use it as a search parameters for src=license I get an error:
src=license User="viefhwd" AND requestinghost="icsl2492" AND starttime="12/09/2014:16:45:37" AND endtime="12/09/2014:16:54:50"
Error in 'search' command: Unable to parse the search: Invalid search: AND AND.

However, if I remove all AND statements from the above, everything works:
src=license User="viefhwd" requestinghost="icsl2492" starttime="12/09/2014:16:45:37" endtime="12/09/2014:16:54:50"

What's wrong with the statement with several ANDs? what would be the right way to fix it?

[aweitzman]

I'm not sure what's wrong with the statement with several ANDs. It seems like that ought to work.

However, since it's not, try working around it by adding a format statement with six empty strings to your subsearch:

... [search... | format "" "" "" "" "" ""] ...

That should remove all of the parentheses and conditionals, and you should just be left with your terms. (Try testing this by running #1 with the proposed format statement and seeing what you get.)

[gregnsk]

Thank you! This fixed the issue.
I still wonder why multiple AND statements didn't work - but I have a solution now!

[bandit]

Had a similar issue where the format looked like this.
( ( accountNumber="xxxxxxxx" AND earliest="1441148065.377" AND latest="1441148665.377" ) )

which produced the error:
Error in 'search' command: Unable to parse the search: Invalid search: AND AND.

adding | format "" "" "" "" "" ""
corrected the search format to:
accountNumber="xxxxxxxx" earliest="1441148065.377" latest="1441148665.377"

Hope Splunk will fix this in a future release. Seems like a common thing to want to modify the time constraints for earliest and latest passed back from the subsearch without doing advanced programming.

[gregnsk]

I do this formatting to meet time format setting used at the second source.

I have 2 sources: nbstatus and license. I want to get list of all events recorded from "license" input which happened on the same "requestinghost" for the same "user" and within the same "starttime-endtime" window as particular events in nbstatus source

Search #1 returns nbstatus events I'm looking for. if I just apply it as a copy to "license" - I get what I need (item #4)
But if I try to use it as a dynamic query, using subsearch - it fails (#2). If I loose the query (removing endtime) - this dynamic query works.

[Runals]

Granted I haven't had caffeine this morning but it appears you are taking a time, converting it to epoch, then back to a string and passing that up to your main search. If you want to use epoch you could just name the fields starttimeu and endtimeu and pass those up to your main search.