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
| 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
Awesome, can you also include the removed contents ? 🙂
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
Thanks, unfortunately i'm stuck with splunk 7.2 for the moment 😞
| 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
| 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