Splunk Search

How to merge two table without losing data and merge multiple rows with "," between each values?

phamxuantung
Communicator

Hello,

So I have 2 problems

I have an alert that fire emails whenever FILE_NAME=FILE_ERROR, and when that happen, I have to merge it with a list of users from internal with USER_TYPE=Internal . Each table are like so

Main table:

_time FILE_NAME
(time) FILE_ERROR

 

Table that I want to merge with

USER_TYPE USER_EMAIL USER_PHONE
Internal internal1@gmail.com 1234
Internal internal2@gmail.com

5678

 

I want the result to show like this

_time FILE_NAME USER_TYPE USER_EMAIL USER_PHONE
(time) FILE_ERROR Internal internal1@gmail.com 1234
(time) FILE_ERROR Internal internal2@gmail.com

5678

 

So basically I want to fill all rows of FILE_NAME and (time) in the main table with the other table so I can use Alert for each result and send emails using $result.USER_EMAILS$.

I have try append, appendcols and join but it only have 1 row that had value. 

Or I want the result table look like this

_time FILE_NAME USER_TYPE USER_EMAIL USER_PHONE
(time) FILE_ERROR Internal internal1@gmail.com, internal2@gmail.com

1234,5678

 

Does anyone have solution for this.

Labels (3)
0 Karma
1 Solution

richgalloway
SplunkTrust
SplunkTrust

The appendcols command should do part of the job, but for it to work properly, we need to combine all user info into a single row and then use filldown to replicate that row.

<<your search for FILE_ERROR>>
| appendcols [ <<your search for internal users>> 
  | stats values(*) as * by USER_TYPE
  | eval USER_EMAIL = mvjoin(USER_EMAIL, ","), USER_PHONE = mvjoin(USER_PHONE, ",") ]
| filldown USER_TYPE, USER_EMAIL, USER_PHONE

 

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

View solution in original post

0 Karma

richgalloway
SplunkTrust
SplunkTrust

The appendcols command should do part of the job, but for it to work properly, we need to combine all user info into a single row and then use filldown to replicate that row.

<<your search for FILE_ERROR>>
| appendcols [ <<your search for internal users>> 
  | stats values(*) as * by USER_TYPE
  | eval USER_EMAIL = mvjoin(USER_EMAIL, ","), USER_PHONE = mvjoin(USER_PHONE, ",") ]
| filldown USER_TYPE, USER_EMAIL, USER_PHONE

 

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

phamxuantung
Communicator

Thank you, this is the answer I'm looking for.

But If you don't mind I want to ask another question.

What if I want to replicate the above result but with a common field value like COMPANY_CODE so I can lookup and send multiple emails to external company.

If I use

join COMPANY_CODE type=left

It's also only return 1 row.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Tell us more about how you used join.  When you do that, you'll want to omit the code that reduces the results to a single row.

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...