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

[phamxuantung]

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.

[richgalloway · Accepted solution]

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

[phamxuantung]

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.

[richgalloway]

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.