display columns from 2 splunk queries based on a common column

[akkaraju]

Hi All,

I have a query where I am passing one field from the output( outer query )to the another query  using subsearch based on field_1

index=index_2 sourcetype=sourcetype_2 [search index=index_1 sourcetype=sourcetype_1 | fields field_1]|table field_1 field_2

I could get the required results/events(field_1,field_2 ) from the outer query based on the common field field_1 with the innerquery

NowI want some columns/fields from the subsearch or inner query along with the final result from outer query to be displayed. Please suggest

[gcusello]

Hi @akkaraju,

let me understand: you have two queries with a common field.

You want as results some fields (some from search1 and some from search2) when the common field is present in both the searches, is it correct?

if this is your need you could run something like this:

if:

• field_1 is in both the searches,
• field _2 is only in search1,
• field_3 is only in search2.

(index=index_2 sourcetype=sourcetype_2) OR (index=index_1 sourcetype=sourcetype_1)
| stats values(field_2) AS field_2 values(field_3) AS field_3 BY field_1
| table field_1 field_2 field_3

Ciao.

Giuseppe

[akkaraju]

Hi @gcusello 

I tried this. But I am not getting the common events based on the field 1.

the common field is present in both the searches all the time. I want the values where the common field value is same/equal in both searches. For example,

Search 1 gives 3 columns

C1 C2 C3

A.   a    1

B    b    2 

C   c      3

I need to search where a C3 has the values 1,2 ,3 in another index and source. For each value in search 1 I want corresponding values in search 2 based on the common filed value. That's why I used sub search, which is giving the output.

C1 C2

1.     x

2.     y

3.     z

But  I want

C1 C2 C3

1.     x.    A

2.     y.     B

3.     z.     C

based on a common column, I want values from the search2 and also some corresponding columns in search 1.Hope I am clear. Think of 2 searches with the same column called IdNumber. I want the address of all the persons obtained in search 1(Id,name) from search 2. So I get  a table of Id,Address. But I want a table with Id name, address 

[gcusello]

Hi @akkaraju,

you could try something like this:

(index=index_2 sourcetype=sourcetype_2) OR (index=index_1 sourcetype=sourcetype_1)
| eval key=coalesce(C3,C1)
| stats values(C2) AS C2 values(C3) AS C3 BY key
| rename key AS C1
| table C1 C2 C3

Ciao.

Giuseppe

[akkaraju]

Hi @gcusello 

Thank you for your replies. But this seems to be not working. FYI you said coalsce c1,c3.. but the names are same (ID) I feel the OR condition you are mentioning is not right. I need a subsearch approach where for each value of  ID column in search 1 ,I should get a corresponding row in search 2 (every event in search 2 should  have the same value of ID ). Like I get 136 results in search 1, I should get the corresponding 136 events in search 2 with the common value ID.

This is my query

index=index2 name=name2 [search index=index1 name=name1 | fields ID]|table ID , Address

ID is the common column

Search 2 has the columns  ID and Address, but search 1 has ID and Name... But I am getting only the columns from search 2. I want ID ,name and address in a table. I feel I should be able to append or join the two searches. Please help

[gcusello]

Hi @akkaraju,

If you have few events you can use the join command that's the solution you are searching:

index=index2 name=name2
| join ID [search index=index1 name=name1 ]
| table ID name address

but I usually don't use it because it has the limit of 50,000 results in subsearch, it's slower than the other and it uses more resources (CPU), is this acceptable for you?

I hint to explore still for some minutes my approach:

(index=index2 name=name2) OR (index=index1 name=name1)
| stats values(name) AS name values(address) AS address BY ID

 Ciao.

Giuseppe

[akkaraju · Accepted solution]

Currently this is working for me. but its slow because of append

"query 2" | table ID, Address| eval From=1 | append [search "query 1"| table ID Name | eval From=2 ] | stats values(*) as * by ID | where mvcount(From)=2 |table ID Name Address

@gcusello , thanks for your responses.

Your answer is giving me all the results but not the results where ID is equal only