Finding a match in lookup multivalue field?

[boxmetal]

Hi Splunk community,

I have a lookup containing a list of allowed departments as the following

vendor	allowed_departments
F500	SADE/xxx,BTE,RAPH/NE
C99	SADE/xxx,RAPH/MS

I want to have a  field valid check if the field of department matches with any values in allowed_deparments of corresponding vendor. But for allowed_departments value ends with "xxx" will accept the department if department has the same prefix of that value

For example: department of value "SADE/ER" will return Yes if allowed_departments contains "SADE/xxx" 

Currently my SPL is like this

<my_search>
| lookup my_lookup vendor OUTPUT allowed_departments 
| makemv delim="," allowed_departments
| eval valid = if(match(allowed_departments, department), "Yes", "No")
| fields - allowed_departments
 

Please advise if it is possible for Splunk to handle it.

Thanks,

[Manasa_401 · Accepted solution]

Hello @boxmetal 

You can try splitting the department field value until '/' and then match the value with multivalued field as below.

| lookup my_lookup vendor OUTPUT allowed_departments 
| makemv delim="," allowed_departments
| rex field=department "(?<dept_short>[^\/]+)"
| eval dept_short=dept_short+"/"
| eval valid = if(match(allowed_departments, dept_short), "Yes", "No")
| fields - allowed_departments

 If this helps, karma would be appreciated.

[boxmetal]

Hi @Manasa_401 

The query worked. Thank you so much!

[gcusello]

Hi @boxmetal,

let me understand: if the department value is composed by "somethng/somenthing_else" you have to check only "something", is this correct?

if this is your need, you have to add an additional condition:

<my_search>
| lookup my_lookup vendor OUTPUT allowed_departments 
| makemv delim="," allowed_departments
| eval valid = if(allowed_departments=department), "Yes", "No")
| rex field=allowed_departments "^(?<lookup_dept>\w+)\/\w+"
| rex field=department "^(?<search_dept>\w+)\/\w+"
| eval valid=if(lookup_dept=search_dept,"Yes",valid)
| fields - allowed_departments

Ciao.

Giuseppe