Splunk Search

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

Explorer

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!

0 Karma
1 Solution

Explorer

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....

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

View solution in original post

Explorer

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....

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

View solution in original post

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.
0 Karma

SplunkTrust
SplunkTrust

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
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

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" ) )

?

0 Karma

Explorer

@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....

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 !!!!!

0 Karma

Explorer

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 ? ...?

0 Karma

SplunkTrust
SplunkTrust

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.

---
If this reply helps you, an upvote would be appreciated.
0 Karma