How do I compare query result with lookup result?

[sarit_s]

Hello

I have a query that running a rest command, one of the fields is "action.email.to"
also i have a lookup table with action.email.to list and team name for east email in the list
I want to compare the action.email.to from the query with the one from the lookup and add another column with the team name.

I tried with append but the team name column is empty

this is my query :

|rest /servicesNS/admin/search/alerts/fired_alerts/- |fields eai:acl.owner  savedsearch_name triggered_alert_count | join savedsearch_name [| rest splunk_server=local count=0 /services/saved/searches | rename title as savedsearch_name |append [inputlookup mailingList.csv ] | table action.email.to savedsearch_name teamName]

[richgalloway]

inputlookup is an oft-misused command.  To map an email address to a team name in a lookup file, use the lookup command.

|rest /servicesNS/admin/search/alerts/fired_alerts/- 
|fields eai:acl.owner  savedsearch_name triggered_alert_count 
| join savedsearch_name [
  | rest splunk_server=local count=0 /services/saved/searches 
  | rename title as savedsearch_name 
  | lookup mailingList.csv "action.email.to" OUTPUT teamName
  | table action.email.to savedsearch_name teamName]

[sarit_s]

Thanks

it looks good
But i have one issue

i get some of the results in action.email.to with more than one value so it looks like 
example@gmail.com,example2@gmail.com

and in such cases there is no mailing mapping so the teamName fields comes up empty

Is there a way to split the values in this column to separate values so it will find the correct key in the lookup ?

[bowesmana]

In the join subsearch, 'expand' the events like this

| join savedsearch_name [
  | rest splunk_server=local count=0 /services/saved/searches 
  | rename title as savedsearch_name 
  | mvexpand "action.email.to"
  | lookup mailingList.csv "action.email.to" OUTPUT teamName
  | table action.email.to savedsearch_name teamName]

the mvexpand will split out the multi-value field and create a new event with all the other fields intact for each value of the multi-value field.

[sarit_s]

Hello

Thanks for your reply but looks like it haven't change anything 😞 
the results are still not splitted

[bowesmana]

Can you give an example of what is returned for the subsearch query

| rest splunk_server=local count=0 /services/saved/searches 
| eval count=mvcount('action.email.to')
| table title "action.email.to" count

please mask the email addresses

[sarit_s]

Hello

We are receiving title and under action.email.to some of the rows returns with one email and some of them  with more than one
when it is more than one then it returns in more than one pattern, for example:
example@gmail.com,example1@gmail.com

example@gmail.com  ,example1@gmail.com,  example2@gmail.com

also I see that the join return duplication so i have the same result more than once

[yuanliu]

You need to split before mvexpand.  Then, as you have varying text patterns, you also need to clean up action.email.to before split.  Rex is an easier choice to combine the two.

Use the same formula @bowesmana gave:

| join savedsearch_name [
  | rest splunk_server=local count=0 /services/saved/searches 
  | rename title as savedsearch_name, action.email.to as emailto
  | rex field=emailto max_match=0 "\b(?<emailto>[^\s,]+)"
  | mvexpand emailto
  | rename emailto as action.email.to
  | lookup mailingList.csv "action.email.to" OUTPUT teamName
  | table action.email.to savedsearch_name teamName]