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.
| 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
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.
Thank you !!
| 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
It worked for me. Thank you.
The other answer should be significantly more efficient.