Need to filter out events based on each value of a multi-valued field.

[sajeeshpn]

Hi,

I basically need help in modifying a 'where' clause of a search string based on single valued to a multi-valued variable. In order to filter out events based on each value in the multi-valued variable.

Here is what I require based on an example:-

Say each event has 'TEST_QNAME' field, which has values like "aaa.test1.com", "bbb.test1.com", "ccc.test2.com", "ddd.test3.com".

This is Splunk search string snippet to be modified:-
| join D_VIEW [ | inputlookup test_lookup
| stats values(A_QNAME) as A_QNAME by D_VIEW ]
| where like(TEST_QNAME, "%" + A_QNAME)

Note: The 'where' clause worked fine here when A_QNAME is single valued. And it returns the events with matching A_QNAME.

When A_QNAME is returned multi-valued after the lookup, having values say "test1.com test2.com test4.com". This 'where' clause does not work. How should I modify the search, so that the filtering of events happens based on each value of A_QNAME and that finally I should only get events which has 'TEST_QNAME' with values ending 'test1.com' or 'test2.com' or 'test4.com'.

Also I need an additional variable which has the matched values of A_QNAME. Here in my example we do not have events with 'TEST_QNAME' with value ending with 'test4.com', so I need this additional variable (to be multi-valued) to have values with "test1.com test2.com" (because that is the only matching).

Please help me on this. Thanks in advance.

[woodcock]

Like this:

| makeresults 
| eval TEST_QNAME = "aaa.test1.com", D_VIEW="D_VIEW", source="First" 
| makemv TEST_QNAME
| join D_VIEW 
    [| makeresults 
    | eval A_QNAME = "aaa.test1.com bbb.test1.com ccc.test2.com ddd.test3.com", D_VIEW="D_VIEW", source="Second" 
| makemv A_QNAME] 

| rename COMMENT "Everything above generates test data; everything below is your solution"

| where like(TEST_QNAME, "%" + A_QNAME) OR isnotnull(mvfind(A_QNAME, TEST_QNAME . "(?:\s|$)"))

This covers both cases. BTW, ditch the join and use stats values(*) AS * BY D_VIEW instead.

[sajeeshpn]

Thank you !!

[jkat54 · Accepted solution]

| join D_VIEW 
[ 
| inputlookup test_lookup
| stats values(A_QNAME) as A_QNAME by D_VIEW 
]
| mvexpand A_QNAME
| where like(TEST_QNAME, "%" + A_QNAME)

Or maybe this:

| join D_VIEW 
[ 
| inputlookup test_lookup
| stats values(A_QNAME) as A_QNAME by D_VIEW 
]
| mvexpand A_QNAME
| eval match=if(match(TEST_QNAME,".*".A_QNAME.".*"),1,0)
| search match=1

[sajeeshpn]

It worked for me. Thank you.

[woodcock]

The other answer should be significantly more efficient.