Splunk Search

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

tomtomFR
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

tomtomFR
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

tomtomFR
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

richgalloway
SplunkTrust
SplunkTrust

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

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

richgalloway
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, Karma would be appreciated.
0 Karma

tomtomFR
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

tomtomFR
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

tomtomFR
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

richgalloway
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, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...