topic Re: compare query result with lookup result in Splunk Search

How do I compare query result with lookup result?

sarit_s — Mon, 19 Sep 2022 15:45:40 GMT

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]

Re: compare query result with lookup result

richgalloway — Mon, 19 Sep 2022 14:11:17 GMT

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]

Re: compare query result with lookup result

sarit_s — Tue, 20 Sep 2022 12:12:57 GMT

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 ?

Re: compare query result with lookup result

bowesmana — Tue, 20 Sep 2022 23:40:38 GMT

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

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.

Re: compare query result with lookup result

sarit_s — Wed, 21 Sep 2022 07:44:51 GMT

Hello

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

Re: compare query result with lookup result

bowesmana — Thu, 22 Sep 2022 06:32:26 GMT

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

Re: compare query result with lookup result

sarit_s — Thu, 22 Sep 2022 10:18:10 GMT

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

Re: compare query result with lookup result

yuanliu — Thu, 22 Sep 2022 17:21:41 GMT

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]