Splunk Search

How to merge rows with a common field?

ndaniel88
Explorer

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.

0 Karma
1 Solution

HiroshiSatoh
Champion

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

View solution in original post

0 Karma

HiroshiSatoh
Champion

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
0 Karma

ndaniel88
Explorer

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.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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?

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

ndaniel88
Explorer

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.

0 Karma

moonmk
New Member

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

0 Karma

ndaniel88
Explorer

I prefer not to use join due performance issues. Thanks

0 Karma
Get Updates on the Splunk Community!

New Case Study Shows the Value of Partnering with Splunk Academic Alliance

The University of Nevada, Las Vegas (UNLV) is another premier research institution helping to shape the next ...

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

October 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with this ...