topic Re: Why is the query with 3 sub search returning several items? in Splunk Search

Why is the query with 3 sub search returning several items?

tomtomFR — Fri, 29 Jun 2018 20:04:40 GMT

Hello there !

This is my first post here 🙂
I've already read a lot of query/answer, try a lot of things, but .... i'm still not getting something good 😞 😞

I'd need to mix 3 differents queries in order to get my final result.
I would like to be able to run only ONE query instead of doing step by step the 3 of them.

A. the first query : get the all transactionId
Extract all fields called "transactionId" for one source where the word 'ERROR' is seen

* "] ERROR" source=*exp* | table transactionId | dedup transactionId

For example, this will return 2 lines :

dd2ff560-7bcd-11e8-8ac7-005056ac4954  
db846840-7bcd-11e8-8ac7-005056ac4954

B. based on the transactionId found in query A, found the correlationId :

* source=*mb05* HTTPHeaderHandler.InboundHeaders ( transactionId from query A ) | rename message_id as correlationId | table correlationId

My query in a step by step mode looks like

* source=*mb05* HTTPHeaderHandler.InboundHeaders (dd2ff560-7bcd-11e8-8ac7-005056ac4954  OR db846840-7bcd-11e8-8ac7-005056ac4954) | rename message_id as correlationId | table correlationId

The result is 2 lines also :

zz31ca20-7bcd-11e8-8ac7-005056ac4954  
zz863d00-7bcd-11e8-8ac7-005056ac4954

_C. with the correlationId found on B get all the lines with Exception _ :

* source=*mb05* ExceptionHandler.HandledException  ( correlationID from query B)  | fields _raw

In my step by step mode :

* source=*mb05* ExceptionHandler.HandledException  (  zz31ca20-7bcd-11e8-8ac7-005056ac4954 OR zz863d00-7bcd-11e8-8ac7-005056ac4954 )  | fields _raw

That gives me the log that I'm looking for.
A bit annoying to do it step by step.

So I'd like to get something like :

* source=*mb05* ExceptionHandler.HandledException  [ search source=*mb05* HTTPHeaderHandler.InboundHeaders [ search * "] ERROR" source=*exp* | table transactionId | dedup transactionId ] | rename message_id as correlationId | table correlationId ] | fields _raw

If anybody has some clue to help me I will be more than happy ! 😄

Thanks in advance for your help!

Re: Why is the query with 3 sub search returning several items?

richgalloway — Fri, 29 Jun 2018 20:52:37 GMT

Your final query is close to what I suggest. What do you get when you run it? BTW, fields is more efficient than table as an interim command.

source=*mb05* ExceptionHandler.HandledException  [ search source=*mb05* HTTPHeaderHandler.InboundHeaders [ search * "] ERROR" source=*exp* | stats list(transactionId) as transactionId | mvexpand | format ] | rename message_id as correlationId | fields correlationId | format ] | fields _raw

Re: Why is the query with 3 sub search returning several items?

tomtomFR — Fri, 29 Jun 2018 21:15:58 GMT

thanks for your answer @Rich

I try to run your command, and just made a small change with mvexpand as it first failed :
(with error : "Error in 'mvexpand' command: A field name is expected." )

source=*mb05* ExceptionHandler.HandledException  [ search source=*mb05* HTTPHeaderHandler.InboundHeaders [ search * "] ERROR" source=*exp* | stats list(transactionId) as transactionId | mvexpand transactionId | format ] | rename message_id as correlationId | fields correlationId | format ] | fields _raw

The result is "no data found"

I try to make it step by step
1. the first part to get all transactionId => found the right ID
2. then to get the correlationId => no more data

I ran :

* source=*mb05* HTTPHeaderHandler.InboundHeaders [ search * "] ERROR" source=*exp* | stats list(transactionId) as transactionId | mvexpand transactionId | format ] | rename message_id as correlationId | fields correlationId | format

and I get the following table/result :

correlationId | search

<> | NOT()

for the 1st part

[ search * "] ERROR" source=*exp* | stats list(transactionId) as transactionId | mvexpand transactionId | format ]

it's creating something like :

( ( transactionId="dd2ff560-7bcd-11e8-8ac7-005056ac4954" ) OR ( transactionId="db846840-7bcd-11e8-8ac7-005056ac4954" ) )

this is why it failed !
is it possible to get only something like :

( ( "dd2ff560-7bcd-11e8-8ac7-005056ac4954" ) OR ( "db846840-7bcd-11e8-8ac7-005056ac4954" ) )

Re: Why is the query with 3 sub search returning several items?

tomtomFR — Fri, 29 Jun 2018 21:25:08 GMT

@Richgalloway you're my heroe !! (l)

using this link : https://answers.splunk.com/answers/1212/can-a-subsearch-return-only-the-value-without-the-fieldname.html

I found how to reply to my last question.

So I try something new with your reply, and now that's working perfectly !!!

source=*mb05* ExceptionHandler.HandledException  [ search source=*mb05* HTTPHeaderHandler.InboundHeaders [ search * "] ERROR" source=*exp* | stats list(transactionId) as search | mvexpand search | format ] | stats list(message_id) as search | format ] | fields _raw

Thanks a lot !!!!!

Re: Why is the query with 3 sub search returning several items?

tomtomFR — Fri, 29 Jun 2018 21:29:11 GMT

one more question still 🙂

where should I put the earliest and latest command to make the query(ies) more efficient ?
on the 3 of them ? only on the one to get the transactionId ? ...?

Re: Why is the query with 3 sub search returning several items?

richgalloway — Sat, 30 Jun 2018 18:58:21 GMT

All three of the searches should have the same earliest and latest settings.

If you problem is resolved, please accept the answer to help future readers.

Re: Why is the query with 3 sub search returning several items?

tomtomFR — Sat, 30 Jun 2018 20:10:00 GMT

thanks to @richgalloway
and a quick look at this link https://answers.splunk.com/answers/1212/can-a-subsearch-return-only-the-value-without-the-fieldname.html

the final answer is :

source=*mb05* ExceptionHandler.HandledException  [ search source=*mb05* HTTPHeaderHandler.InboundHeaders [ search * "] ERROR" source=*exp* | stats list(transactionId) as search | mvexpand search | format ] | stats list(message_id) as search | format ] | fields _raw

Re: Why is the query with 3 sub search returning several items?

richgalloway — Sun, 01 Jul 2018 12:26:12 GMT

@tomtomFR If your problem is resolved, please accept an answer to help future readers.