Multivalue fields difference on multiple records

[Viorel]

Hello folks,

I am having a hard time getting the difference between two fields of the same record, where the search query returns multiple record set.

The query uses streamstat to bring the "previous" field into the current record, here's a dummy that shows the same results

| makeresults
| eval RoleContents = "a;b;c"
| eval _time = now()
| append [| makeresults | eval RoleContents="b;c;d" | eval _time=now()-10]
| append [| makeresults | eval RoleContents="a;d" | eval _time =now()-20]
| streamstats current=f window=1 first(RoleContents) as LastRoleContents
| sort _time
| streamstats current=f window=1 first(RoleContents) as PrevRoleContents
| sort - _time
| makemv delim=";" RoleContents
| makemv delim=";" PrevRoleContents
| table RoleContents, PrevRoleContents

What i am looking to acheive is within the row, to show the difference between those two fields, which will show , for each record returned, what changed in comparison to the previous record.

Any help would be appreciated.

Thanks

[Viorel]

Awesome, can you also include the removed contents ? 🙂

[ITWhisperer]

Essentially, you do the same thing with the fields swapped, however, I had to add extra logic to deal with null values since the mvexpand will remove these rows. You may need to do the same for the NewContents depending on your data.

| makeresults
| eval RoleContents = "a;b;c"
| eval _time = now()
| append [| makeresults | eval RoleContents="b;c;d" | eval _time=now()-10]
| append [| makeresults | eval RoleContents="a;d" | eval _time =now()-20]
| streamstats current=f window=1 first(RoleContents) as LastRoleContents
| sort _time
| streamstats current=f window=1 first(RoleContents) as PrevRoleContents
| sort - _time
| makemv delim=";" RoleContents
| makemv delim=";" PrevRoleContents
| fields RoleContents PrevRoleContents
| streamstats count as row
| mvexpand RoleContents
| eval NewContents=if(isnull(mvfind(PrevRoleContents, RoleContents)),RoleContents,null)
| stats values(PrevRoleContents) as PrevRoleContents list(RoleContents) as RoleContents list(NewContents) as NewContents by row
| fillnull value="not available" PrevRoleContents
| mvexpand PrevRoleContents
| eval PrevRoleContents=if(PrevRoleContents="not available",null,PrevRoleContents)
| eval RemovedContents=if(isnull(mvfind(RoleContents, PrevRoleContents)),PrevRoleContents,null)
| stats list(PrevRoleContents) as PrevRoleContents values(RoleContents) as RoleContents values(NewContents) as NewContents list(RemovedContents) as RemovedContents by row
| table RoleContents, PrevRoleContents, NewContents, RemovedContents

[Viorel]

Thanks, unfortunately i'm stuck with splunk 7.2 for the moment 😞

[ITWhisperer]

| makeresults
| eval RoleContents = "a;b;c"
| eval _time = now()
| append [| makeresults | eval RoleContents="b;c;d" | eval _time=now()-10]
| append [| makeresults | eval RoleContents="a;d" | eval _time =now()-20]
| streamstats current=f window=1 first(RoleContents) as LastRoleContents
| sort _time
| streamstats current=f window=1 first(RoleContents) as PrevRoleContents
| sort - _time
| makemv delim=";" RoleContents
| makemv delim=";" PrevRoleContents
| eval AdditionalContents=mvmap(RoleContents,if(isnull(mvfind(PrevRoleContents,RoleContents)),RoleContents,null()))
| eval RemovedContents=mvmap(PrevRoleContents,if(isnull(mvfind(RoleContents,PrevRoleContents)),PrevRoleContents,null()))
| table RoleContents, PrevRoleContents, AdditionalContents, RemovedContents

mvmap is available in splunk 8.0

[ITWhisperer · Accepted solution]

| makeresults
| eval RoleContents = "a;b;c"
| eval _time = now()
| append [| makeresults | eval RoleContents="b;c;d" | eval _time=now()-10]
| append [| makeresults | eval RoleContents="a;d" | eval _time =now()-20]
| streamstats current=f window=1 first(RoleContents) as LastRoleContents
| sort _time
| streamstats current=f window=1 first(RoleContents) as PrevRoleContents
| sort - _time
| makemv delim=";" RoleContents
| makemv delim=";" PrevRoleContents
| fields RoleContents PrevRoleContents
| streamstats count as row
| mvexpand RoleContents
| eval NewContents=if(isnull(mvfind(PrevRoleContents, RoleContents)),RoleContents,null)
| stats values(PrevRoleContents) as PrevRoleContents list(RoleContents) as RoleContents list(NewContents) as NewContents by row
| table RoleContents, PrevRoleContents, NewContents