topic Re: Removing duplicates from 2 multivalue field (leaving distinct values only) in Splunk Search

Removing duplicates from 2 multivalue field (leaving distinct values only)

diskioinferno — Wed, 13 Dec 2023 10:21:55 GMT

I have 2 multivalue fields (old and new) containing group lists for 1 or more users. The new values is the list of groups that replace the old groups

For example:

user 1 has an old value of group1, group2, group3
user 1 has a new value of group1, group2, group3, group4, and group5
user 2 has an old value of group3, group4, group5
user 1 has a new value of group4, group5, group6, group7, and group8

I'm trying to return group4 and group5 for user and group7 and group8 for user2

Re: Removing duplicates from 2 multivalue field (leaving distinct values only)

ITWhisperer — Wed, 13 Dec 2023 14:16:37 GMT

| eval groups=mvappend(old, old, new) | stats count by user groups

Where count=3, the group exists in both old and new

Where count=2, the group exists just in old

Where count=1, the group exists just in new

Re: Removing duplicates from 2 multivalue field (leaving distinct values only)

dtburrows3 — Wed, 13 Dec 2023 15:05:20 GMT

I think you should be able to do this by using the mvmap() function.

Here is the eval to return the resulting table above.

``` Eval to perform set operation against Splunk multivalue fields ``` | eval new_remove_old_set_operation=case( isnull(new_groups), null(), mvcount(new_groups)==1, if(NOT 'new_groups'=='old_groups', 'new_groups', null()), mvcount(new_groups)>1, mvmap(new_groups, if(NOT 'new_groups'=='old_groups', 'new_groups', null())) )

Full SPL snippet used to replicate your scenario.

| makeresults | eval user="user1", old_groups=mvappend( "group1", "group2", "group3" ), new_groups=mvappend( "group1", "group2", "group3", "group4", "group5" ) | append [ | makeresults | eval user="user2", old_groups=mvappend( "group3", "group4", "group5" ), new_groups=mvappend( "group4", "group5", "group6", "group7", "group8" ) ] | fields - _time | fields + user, old_groups, new_groups ``` Eval to perform set operation against Splunk multivalue fields ``` | eval new_remove_old_set_operation=case( isnull(new_groups), null(), mvcount(new_groups)==1, if(NOT 'new_groups'=='old_groups', 'new_groups', null()), mvcount(new_groups)>1, mvmap(new_groups, if(NOT 'new_groups'=='old_groups', 'new_groups', null())) )

Re: Removing duplicates from 2 multivalue field (leaving distinct values only)

diskioinferno — Mon, 18 Dec 2023 02:42:07 GMT

This worked, thank you! I had looked at mvmap but this was not the how I was trying to use it - thanks for your help