Nested query

[vihshah]

Hi,

So my task is to extract a field from a query and search for that field. That query will give an object value as a string and want to extract data from there. 

In summary, I need 3 things
1.  plain query to get the data and extract a particular field.
2. Use that field as an input for the second query.
3. Get object data as a string as a result, extract fields from there, and generate a report from it in tabular format.

I was able to reach till 1st step and extract the field from it.  but I am unable to search for it.
below is the query I tried.

sourcetype="mykube.source" "failed request"  | rex "failed request:(?<request_id>[\w-]+)" | table request_id | head 1 | eval req_query = request_id | search req_query

if I try till `head 1` I get first request_id but after that result is empty for me.

[gcusello]

Hi @vihshah,

if you only need to search in a main search using a secondary search fiel, you have to use a subsearch, putting attention that the field name must be the same in both the searches (field names are case sensitive) if they are different, you have to rename the one i the subsearch, so something like this:

index=index1 [ search index=index2 | fields my_field ]
| ...

If instead you need to use also fields from the second seacrh you have to use a different approach.

Ciao.

Giuseppe

[vihshah]

Hi @gcusello ,

sorry I did not get you exactly..what should I do?

[gcusello]

Hi @vihshah ,

in few words, you have to use the secondary search as a subsearch of the main.

to be more detailed, please share you main search and the second search that you want to use to find the field for filtering, in your question you shared only one search:

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| table request_id 
| head 1 
| eval req_query = request_id 
| search req_query

it's the main search or the secondary?

Ciao.

Giuseppe

[vihshah]

Hi @gcusello ,

thanks for clarification, the one I posted is my main search, I am stuck at second search

[PickleRick]

Ok. Two things.

1. You can use the map command to iterate over results of one search and spawn searches based on those results. But this is usually not the proper way to go. The map command should only be used if there is really no other way. Normally you use subsearches, which are executed before the main search and results from which are rendered as conditions to the main search.

2. But the main question is not how to use two searches here but what do you want to get from your data because often there is a completely different, more "splunky" and better performing solution to the problem.

[gcusello]

Hi @vihshah,

the search you shared produces only one event, so I suppose it isn't your main search.

In addition, the eval and search in rows 5 and 6 haven't any sense.

I suppose that your main search is :

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| table request_id 

and that you want to filter your results for a secondary search, what's this search?

Ciao.

Giuseppe

[vihshah]

Hi @gcusello @PickleRick ,

yes, you are correct, I just did `head 1` just to see if my query works fine or not.
so my second search is whatever  request_id I received, I want to search that request_id itself in Splunk logs. when I searched with hard-coded request id in Splunk, I saw the whole Java object as a string, my main goal is to extract data from that object

I hope this make sense

[gcusello]

Hi @vihshah ,

are you able to create the secondary search, please share it and I'll show you how to use it to filter results of the main search.

Ciao.

Giuseppe

[PickleRick]

OK. So this search

sourcetype="mykube.source" "failed request" 
| rex "failed request:(?<request_id>[\w-]+)"
| table request_id
| head 1

Will give you a single result with a single field.

Now if you do something like this:

index=some_other_index sourcetype="whatever" [ sourcetype="mykube.source" "failed request" 
| rex "failed request:(?<request_id>[\w-]+)"
| table request_id
| head 1 ]

Splunk will look in the some_other_index for events with sourcetype of whatever and this request_id value returned from the subsearch.

[vihshah]

Hi  @gcusello ,
 
No, I am not able to do a secondary search, that is where I am stuck.

@PickleRick ,

I don't have any other index and source type right now, my task is to have secondary search based on the request-id I retrieved

[PickleRick]

Noone said that the indexes and sourcetypes have to be different. The outer search can be whatever you want. It's that the subsearch returns its results as additional conditions to the outer search is what is important in this example.

[gcusello]

Hi @vihshah ,

where does the request_id to use in the search come from?

what are the conditions to use in the filter?

please describe them with words.

Ciao.

Giuseppe

[vihshah]

HI @gcusello ,

request_id extracted from my first search.

| rex "failed request:(?<request_id>[\w-]+)" 

and I don't have any filter criteria on that request_id.  I just want to trace the flow of that request id. and later, I want to extract few details from once I get the whole trace. but that is third part. right now I just want to search that request id to trace the flow.

[gcusello]

Hi @vihshah ,

in this case, you have to define what you need to see, e.g. the occurrences of the values of this field in a time period:

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| stats count BY request_id 

or a time distribution by request_id:

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| timechart count BY request_id 

if you wan the list of all events with outher information you can use the table command:

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| table _time request_id field1, field2 field3

you could also create a simple search to select the request_id in a panel and with a drilldown filter all the results wi this field in a different panel.

As I said, you should define your requirements before approaching a search.

Ciao.

Giuseppe

[vihshah]

Hi @gcusello ,

this would not give me the entire details of what I require.
I need to generate recurring reports based on the following details.
1. get all failed request IDs
2. iterate all the request IDs to get more details
3. extract require fields from those details and show them in tabular form and generate an email.

I hope now my requirements I clear

[gcusello]

Hi @PickleRick ,

my first search answers to your first requirement: yo have to save it as an alert that sends results in attachment.

My second search answers to your first requirement: yo have to save it as an alert that sends results in attachment.

Third requirement isn't clear: what's the difference with the second?

Do you know SPL? did you followed the Splunk Search Tutorial (https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchTutorial/WelcometotheSearchTutorial)? 

Ciao.

Giuseppe

[vihshah]

Hi @gcusello ,

sorry if I misinformed, 

I need to generate an alert after the third step.

difference between the 2nd and 3rd is, that I need to search the details of the fail request id (which I will get from the first step), extract data from that field, and send it via email. 

And yes, I am following that tutorial my 2nd step did not work correctly

[gcusello]

Hi @vihshah ,

I don't see differenes between 2nd and 3rd requirement: using my second search you can have all the details you need grouped by request_id.

You have only to save this search as an alert or a report and you'll have them by eMail.

Ciao.

Giuseppe

[vihshah]

Hi @gcusello ,

thank you for the answer, can you please let me know how can I rephrase my query?

[gcusello]

Hi @vihshah ,

aswering to your requirements:

1. get all failed request IDs
using the following search you have all the failed request_ids and the count of each one.

you can save them in an alert and send the results as an attachement (csv or pdf) by eMail:

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| stats count BY request_id 

 2. iterate all the request IDs to get more details:

You can have the list of all events with other information using the table command:

I don't know which fields you have but you can complete the search:

sourcetype="mykube.source" "failed request"  
| rex "failed request:(?<request_id>[\w-]+)" 
| table _time request_id field1, field2 field3

3. extract require fields from those details and show them in tabular form and generate an email.

Using the previous search, you can create an alert sending the results as an attachement (csv or pdf) by eMail.

Ciao.

Giuseppe