topic Re: How do I create a field that contains the differences between 2 other multi value (MV) fields? in Splunk Search

How do I create a field that contains the differences between 2 other multi value (MV) fields?

bkwoka — Thu, 04 Oct 2018 15:06:57 GMT

I have a search that returns two multi value fields. I am looking to create a third field which would contain the differences between the two MV fields. I was hoping to use something like diff() but that only works for comparing events. Ideally the format would be similar to diff() but would compare fields in one event instead of across different events.

    index=sacl_audit NOT (Object_Name="E:\\System Volume Information\\*") 
    | lookup ConvertSDDL sddl_format AS Original_Security_Descriptor OUTPUT human_format AS original 
    | lookup ConvertSDDL sddl_format AS New_Security_Descriptor OUTPUT human_format AS new 
    | makemv original delim="|" 
    | makemv new delim="|"

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

harishalipaka — Thu, 04 Oct 2018 19:00:29 GMT

@bkwoka

If you want column wise deference use |eval=a-b

Or row wise.
Use |delta

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

darrenfuller — Thu, 04 Oct 2018 20:00:01 GMT

I think what he is asking is ...

if MVField1 has values : [ A , B , C ]
and
if MVField2 has values: [ B , C , D ]

you want a return of a new mvfield with values [A , D] , the two values that don't exist in both...

correct?

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

bkwoka — Thu, 04 Oct 2018 23:44:36 GMT

Close. Ideally if MVFieldOld has [A, B, C] and MVFieldNew has [C, D, E] I would like MVDiff to have something like [-A, -B, +D, +E]. Similar to the diff() command.

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

kamlesh_vaghela — Fri, 05 Oct 2018 11:31:09 GMT

@bkwoka

Can you please try below search?

index=sacl_audit NOT (Object_Name="E:\\System Volume Information\\*") 
| lookup ConvertSDDL sddl_format AS Original_Security_Descriptor OUTPUT human_format AS original 
| lookup ConvertSDDL sddl_format AS New_Security_Descriptor OUTPUT human_format AS new 
| makemv original delim="|" 
| makemv new delim="|" 
| table original new
| eval row=1 
| eval originalX=original 
| accum row 
| fillnull original new value=" " 
| mvexpand originalX 
| eval original_A = mvfind(new,originalX) 
| where isnull(original_A) 
| eval originalX="+".originalX 
| stats values(*) as * by row 
| eval newY=new 
| mvexpand newY 
| eval new_A = mvfind(original,newY) 
| where isnull(new_A) 
| eval newY="-".newY 
| fillnull originalX newY value=" " 
| stats values(*) as * by row | eval diff=mvappend(originalX,newY) | table original new diff

My Sample Search:

| makeresults 
| eval original="A,B,C,D", new="B,C,D,E", original=split(original,","),new=split(new,",") 
| table original new 
| append 
    [| makeresults 
    | eval original="W,X,Y,Z", new="Y,Z,A,B", original=split(original,","),new=split(new,",") 
    | table original new 
        ] 
| eval row=1 
| eval originalX=original 
| accum row 
| fillnull original new value=" " 
| mvexpand originalX 
| eval original_A = mvfind(new,originalX) 
| where isnull(original_A) 
| eval originalX="+".originalX 
| stats values(*) as * by row 
| eval newY=new 
| mvexpand newY 
| eval new_A = mvfind(original,newY) 
| where isnull(new_A) 
| eval newY="-".newY 
| fillnull originalX newY value=" " 
| stats values(*) as * by row | eval diff=mvappend(originalX,newY) | table original new diff

Thanks

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

bkwoka — Fri, 05 Oct 2018 15:41:45 GMT

This gave me a lot of insight and direction as to what I need to do. I am now running into an issue where mvfind() isn't returning an index even when the fields match. For example here is the command I am running:

index=sacl_audit NOT (Object_Name="E:\\System Volume Information\\*") 
| lookup ConvertSDDL sddl_format AS Original_Security_Descriptor OUTPUT human_format AS original 
| lookup ConvertSDDL sddl_format AS New_Security_Descriptor OUTPUT human_format AS new 
| makemv original delim="|" 
| makemv new delim="|" 
| eval row=1 
| accum row 
| eval originalTemp = original
| eval newTemp = new
| mvexpand originalTemp
| eval originalS = mvfind(new,originalTemp)
| table row,original,new,originalTemp,originalS

I am not sure if I can attach pictures but in the following screenshot you can see how the first row isn't working but the second row is.

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

kamlesh_vaghela — Fri, 05 Oct 2018 17:19:19 GMT

@bkwoka

Can you please share sample values from original field? S
o I can work on it...

Re: How do I create a field that contains the differences between 2 other multi value (MV) fields?

bkwoka — Mon, 08 Oct 2018 13:42:05 GMT

Here are the first 3 rows

IdentityReference : BUILTIN\Administrators FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow
IdentityReference : CREATOR OWNER FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow
IdentityReference : NT AUTHORITY\SYSTEM FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow 

IdentityReference : BUILTIN\Administrators FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow
IdentityReference : CREATOR OWNER FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow
IdentityReference : NT AUTHORITY\SYSTEM FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow 

IdentityReference : BUILTIN\Administrators FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow
IdentityReference : CREATOR OWNER FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow
IdentityReference : NT AUTHORITY\SYSTEM FileSystemRights : FullControl IsInherited : False InheritanceFlags : ContainerInherit, ObjectInherit PropogationFlags : AccessControlType : Allow