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

View solution in original post

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, an upvote 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
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!