Can I use lookup for whitelisting based on 2 columns?

[izzie123]

I have to whitelist fields based on 2 columns in a lookup, but the second column has multiple values.

So we have to whitelist based on the condition that the username and the destinations are in two fields in the same event.

In the event too, we have the field values(dest) so multiple destinations are in one cell.

The condition is that the user with those destinations should be whitelisted.

How can we achieve this?

[yuanliu]

Let me try to illustrate my understanding of the use case. You have a lookup, say whitelist, that contains two fields,

user	destination
user1	dest1,dest2,dest3
user2	dest1,dest3,dest4,dest5

You haven't described how the multiple values are separated, so I pick comma as separator. You haven't described which field to look up; I assume it is user, which also exist in events with the same field name.

If these assumptions are correct, the SPL to implement your requirement would be something like

| lookup whitelist user ``` assuming match field is user ```
| eval destination = split(destination, ",") ``` assuming comma as separator ```
| eval whitelisted = mvmap(destination, if(dest == destination, "yes", null()))
| where isnotnull(whitelisted)

If, on the other hand, multiple values in lookup is not separated by a character, but is from multilisting, like

user	destination
user1	dest1
user2	dest1
user1	dest2
user2	dest3
user1	dest3
user2	dest5

The output from lookup would have already been multivalued. In this case, you won't need that split, i.e.,

| lookup whitelist user ``` assuming match field is user ```
| eval whitelisted = mvmap(destination, if(dest == destination, "yes", null()))
| where isnotnull(whitelisted)

(The moral of the story: Be specific about inputs.)