How to merge rows with a common field?

[ndaniel88]

Hello, I have 1 single table that comes from two different searches/indexes/sourcetypes using append.

I need to join the results using 1 common field (name), in order to have per row all the information that I need.

The table I have is like:

+--------+--------+--------+------+----------+
| Date1 | Date2 | Date3 | name | Doc |
+--------+--------+--------+------+----------+
| Jan 08 |------- | --------- | A1 |----------|
| Jan 08 |------- | --------- | A2 |----------|
| Jan 08 |------- | --------- | A2 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 08 |------- | --------- | A3 |----------|
| Jan 04 |------- | --------- | A4 |----------|
| Jan 09 |------- | --------- | A2 |----------|
|-----------| Jan2 | Jan09 | B1 | KYOM |
|-----------| Jan2 | Jan09 | A4 | UNYT |
|-----------| Jan2 | Jan09 | A1 | AGHY |
|-----------| Jan2 | Jan09 | A3 | POLK |
|-----------| Jan2 | Jan09 | B2 | TYHU |
+--------+--------+--------+------+----------+

And my goal is to end up with something like

+--------+---------+---------+--------+------+
| Date1 | Date2 | Date3 | name | Doc |
+--------+---------+---------+--------+------+
| Jan 08 | Jan2 | Jan09 | A1 | AGHY |
| Jan 08 | Jan2 | Jan09 | A2 |----------|
| Jan 08 | Jan2 | Jan09 | A2 |----------|
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 08 | Jan2 | Jan09 | A3 | POLK |
| Jan 04 | Jan2 | Jan09 | A4 | UNYT |
| Jan 09 | Jan2 | Jan09 | A2 |----------|
|----------| Jan2 | Jan09 | B1 | KYOM |
|----------| Jan2 | Jan09 | B2 | TYHU |
+--------+---------+---------+--------+------+

There is 1 and only 1 common field in the two searches, in the example the date match but is only for testing, it really never match. My search is like:

index=main sourcetype=test | many | many | many | condition
| append [search index=other | many | more | conditions]

I'm not using a single stats because it groups same name in 1 row (multivalue), and I need each result on its own row. Even tho they have same name, they are different events, that I'll evaluate per row after this table is generated.

Thanks.

[HiroshiSatoh · Accepted solution]

I think the example is strange ...

If you concatenate Doc using name to data that Doc does not exist

(your search)
|streamstats count as no|eval no=if(isnull(Doc),0,no)
|stats list(*) as * by name
|fillnull value="" 
|mvexpand Date1
|table Date1,Date2,Date3,name,Doc

[ndaniel88]

Why strange? that's exactly how my data looks, but with real data, lol.

Anyways, your answer works like a charm, Thank you, I appreciate.

[richgalloway]

You say you don't want to use a single stats, but have you tried doing so and then using mvexpand to put each result in its own row?

[ndaniel88]

That's the thing, I don't know how to do so. I've tried m expand, but I guess I didn't do it correctly, I don't know how to use it to achieve my goal.

[moonmk]

Try it. "join" use

index=main
| stats count by Date1 , Date2, Date3 , name , Doc
| join type=outer name [search index=other
| stats count by Date1 , Date2, Date3 , name , Doc ]
| table Date1 , Date2, Date3 , name , Doc

[ndaniel88]

I prefer not to use join due performance issues. Thanks