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,
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.
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.
Hi @Manasa_401
The query worked. Thank you so much!
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