Splunk Search

How to compare multivalue fields? Union, intersection, membership tests

emottola
Explorer

When comparing multivalue fields, there are a number of relationships one might be interested in.

Equality is easy to check, but what about more complex relationships? 
Are any members of f1 in f2?
What fields do f1 and f2 have in common(intersection)?
What fields are unique to f1?

Labels (1)
0 Karma
1 Solution

emottola
Explorer

To answer this, I've prepared a makeresults with examples of many of these mvfield comparisons.
Hopefully someone finds these useful!

If you came searching for a different usecase, plase leave a comment with the keywords that would have helped you find this post so others will have an easier time

 

| makeresults 
| eval f1="a", f2=split("a,b,c,d", ",") 
| append 
    [| makeresults 
    | eval f1="a", f2=split("b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("a,b",",")] 
| append 
    [| makeresults 
    | eval f1=split("b,a", ","), f2=split("a,b",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("a,b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("c,a", ","), f2=split("a,b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("c,d",",")] 
| fields - _time

| eval intersection=mvmap(f1, if(f1=f2, f1, null()))
| eval f1_not_f2=mvmap(f1, if(f1==f2, null(), f1))
| eval f2_not_f1=mvmap(f2, if(f2==f1, null(), f2))
| eval union = mvsort(mvdedup(mvappend(f1, f2)))

| eval equality = if(f1=f2, 1, 0)
| eval equivalence = if(mvmap(f1, if(f1==f2, 0, f1)) == mvmap(f2, if(f2==f1, 0, f2)), 1, 0)
| eval any_f1_in_f2 = if(mvcount(intersection)>0, 1, 0)
| eval all_f1_in_f2 = if(mvmap(f1, if(f1==intersection, 0, f1)) == mvmap(intersection, if(intersection==f1, 0, intersection)), 1, 0)

| table f1, f2, f1_not_f2, intersection, f2_not_f1, union, equality, equivalence, all_f1_in_f2, any_f1_in_f2

 

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

mvmap is your friend here. See this example which creates 2 random MV fields and then does the comparisons

| makeresults
| fields - _time
| eval f1=mvrange(0,10,(random() % 3 + 1))
| eval f2=mvrange(5,20,(random() % 4 + 1))
| eval f1_exists_in_f2=max(f1_exists_in_f2, mvmap(f1, if(isnotnull(mvfind(f2, f1)), 1, 0)))
| eval f1_values_in_f2=mvmap(f1, if(f1=f2, f1, null()))
| eval f1_values_not_in_f2=mvmap(f1, if(f1!=f2, f1, null()))
| table f1 f2 f1_exists* f1_values*
0 Karma

emottola
Explorer

To answer this, I've prepared a makeresults with examples of many of these mvfield comparisons.
Hopefully someone finds these useful!

If you came searching for a different usecase, plase leave a comment with the keywords that would have helped you find this post so others will have an easier time

 

| makeresults 
| eval f1="a", f2=split("a,b,c,d", ",") 
| append 
    [| makeresults 
    | eval f1="a", f2=split("b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("a,b",",")] 
| append 
    [| makeresults 
    | eval f1=split("b,a", ","), f2=split("a,b",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("a,b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("c,a", ","), f2=split("a,b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("b,c,d",",")] 
| append 
    [| makeresults 
    | eval f1=split("a,b", ","), f2=split("c,d",",")] 
| fields - _time

| eval intersection=mvmap(f1, if(f1=f2, f1, null()))
| eval f1_not_f2=mvmap(f1, if(f1==f2, null(), f1))
| eval f2_not_f1=mvmap(f2, if(f2==f1, null(), f2))
| eval union = mvsort(mvdedup(mvappend(f1, f2)))

| eval equality = if(f1=f2, 1, 0)
| eval equivalence = if(mvmap(f1, if(f1==f2, 0, f1)) == mvmap(f2, if(f2==f1, 0, f2)), 1, 0)
| eval any_f1_in_f2 = if(mvcount(intersection)>0, 1, 0)
| eval all_f1_in_f2 = if(mvmap(f1, if(f1==intersection, 0, f1)) == mvmap(intersection, if(intersection==f1, 0, intersection)), 1, 0)

| table f1, f2, f1_not_f2, intersection, f2_not_f1, union, equality, equivalence, all_f1_in_f2, any_f1_in_f2

 

 

Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Watch Now!   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to leverage ...

More Control Over Your Monitoring Costs with Archived Metrics!

What if there was a way you could keep all the metrics data you need while saving on storage costs?This is now ...

New in Observability Cloud - Explicit Bucket Histograms

Splunk introduces native support for histograms as a metric data type within Observability Cloud with Explicit ...