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

[diskioinferno]

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

[dtburrows3 · Accepted solution]

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()))
            )

[diskioinferno]

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

[ITWhisperer]

| 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