Archive

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

New Member

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.

Tags (1)
0 Karma
1 Solution

SplunkTrust
SplunkTrust
| 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

View solution in original post

0 Karma

Esteemed Legend

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.

New Member

Thank you !!

0 Karma

SplunkTrust
SplunkTrust
| 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

View solution in original post

0 Karma

New Member

It worked for me. Thank you.

0 Karma

Esteemed Legend

The other answer should be significantly more efficient.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!