topic Re: How to create a table with fields form two different indexes in Splunk Search

How to create a table with fields form two different indexes

maryamchar — Wed, 08 May 2019 20:42:18 GMT

I want to create a table with all fields from two different indexes.

Index=A |rename fieldA as field1 |table field1 fieldAA fieldAB field AC
index= B |rename fieldB as field1 | table field1 fieldBB fieldBC

I want a table shows all fields including common field which is filed1

|table field1 fieldAA fieldAB field AC fieldBB fieldBC

Re: How to create a table with fields form two different indexes

Vijeta — Wed, 08 May 2019 21:01:15 GMT

Try something like-

index=A or index=B| eval field1=coalesce(fieldA,fieldB)| stats values(*) as * by field1

Re: How to create a table with fields form two different indexes

koshyk — Wed, 08 May 2019 21:11:37 GMT

Another option is to do using transaction, so the events can be clubbed together

index=A or index=B
| rename fieldA as field1 
| rename fieldB as field1
| transaction field1 
| table field1 fieldAA fieldAB field AC fieldBB fieldBC

Re: How to create a table with fields form two different indexes

maryamchar — Wed, 08 May 2019 21:27:59 GMT

Thank you! It worked!

Re: How to create a table with fields form two different indexes

maryamchar — Wed, 08 May 2019 21:28:12 GMT

Thank you! It worked!!

Re: How to create a table with fields form two different indexes

maryamchar — Wed, 08 May 2019 21:41:22 GMT

one more question realted to same query above. I want to show the number of count by field1 ? How would I do. that

|stats count by field 1

Re: How to create a table with fields form two different indexes

koshyk — Wed, 08 May 2019 21:46:35 GMT

thanks. Please upvote if it helped. cheers

Re: How to create a table with fields form two different indexes

koshyk — Wed, 30 Sep 2020 00:27:16 GMT

if you do
|stats count by field 1

This will show just count for field1 after clubbing the transaction

if you want to retain the original table and then count, better to do
index=A or index=B
| rename fieldA as field1
| rename fieldB as field1
| transaction field1
| streamstats count as field_count by field
| table field1 field_count fieldAA fieldAB field AC fieldBB fieldBC

Re: How to create a table with fields form two different indexes

maryamchar — Wed, 08 May 2019 23:11:50 GMT

Sorry but I ran the search now, the table only shows certain values from one index only and ignore the other indexes. Is there a reason for that ? why the table has empty fields even though it exists on data

Re: How to create a table with fields form two different indexes

maryamchar — Wed, 08 May 2019 23:24:54 GMT

if I want to have stats values for certain fields only from both indexes, how would I do that ? Thank you!!