unable to sun subsearch

[kushagra9120]

index="_internal" user!=admin | [search index="_internal" | stats count by user]

I am trying to run above query but it fails with an error that "Error in 'SearchParser': Subsearches are only valid as arguments to commands. "

Somewhere on community i read that if a run it this way it will run:-
"index="_internal" user!=admin | search [search index="_internal" | stats count by user]" .It does run but it doesnt give any events, where as if i just run "index="_internal" user!=admin | search index="_internal" | stats count by user" it works fine and gives an output.

Both are same query but i was learning about subsearch and couldnt figure it out. If i directly run search after a pipe it gives error and if i pass it with search command it doesnt give any events.

[DalJeanis · Accepted solution]

Since you are learning about subsearches, you need to start with this knowledge:

A subsearch is going to either return a set of results to be appended into the current search, a set of results to be joined into the current search, OR it is going to return a specialized field that can be used to limit another search. This last is the way you are apparently trying to use this subsearch.

There are two mistakes in how you are trying to put that search together. At the end of a subsearch, when used this way, the system will implicitly put a command to reformat the results. The command is called format, and you can take just the subsearch, in a different search tab, and pipe your results to | format, to see exactly what will be dropped back into your search wherever your subsearch was.

This part of your search...

 [search index="_internal" | stats count by user]

will hit that implicit format and then return some search language after that pipe, in the form...

( ( user="firstuser" AND count="37" ) OR ( user="seconduser"  AND count="55") OR ...  )

...which doesn't really make much sense by itself. Even when you properly add the key word | search, there are going to be no records in that _internal index which, other than by accident, have a user field and a count field that happens to match your subsearch results. So the first mistake is that you did not limit the subsearch to the specific fields that you wanted to use as a search filter. The second, of course, is that the formatted test language will only work when it is returned to a place in the code where it will make sense, thus you need the verb search in order not to get a parsing error.

Try this...

 search index="_internal"  user!=admin earliest=-2h@h latest=-1h@h 
| stats count by user | sort 5 - count | table user | format

Then try this

 index="_internal" earliest=-2h@h latest=-1h@h 
| search [
     search index="_internal"  user!=admin earliest=-2h@h latest=-1h@h 
    | stats count by user | sort 5 - count | table user 
    ]
 | head 100

And try this

 index="_internal" user!=admin  [
     search index="_internal"  user!=admin earliest=-2h@h latest=-1h@h 
    | stats count by user | sort 5 - count | table user 
    ]
 | head 100

And, just for fun, try this...

 | makeresults 
 | eval myreturnedvalue= [
     search index="_internal"  user!=admin earliest=-2h@h latest=-1h@h 
    | stats count by user | sort 5 - count | table user 
    ]

And this...

 | makeresults 
 | eval user = "put one of the five users here"
 | join user  [
     search index="_internal"  user!=admin earliest=-2h@h latest=-1h@h 
    | stats count by user 
    ]

That shows you a trivial example of each way a subsearch could be used.

[oda]

What kind of results do you want?