Compare Multivalue Fields With Lookup

[cquinney]

Greetings Splunkers,

I've been banging my head against the keyboard to try and resolve this comparison issue, I know there's a way to do it I just can't seem to figure it out.

The issue I'm trying to resolve is determining if a user has a conflict of interest with regard to roles their user has been assigned, and what management deems inappropriate, segregation of duties basically.  

I get my list of conflicting roles from a lookup.  

- lookup SoD.csv Department as Department OUTPUTNEW Conflicting_Roles, Justification

So if USER_A is within the Finance department and assigned roles A, B, and C   OR    A, B, and Z for example it would be a conflict because they still have A & B.  I can get a proper result when I manually input the roles within a case statement.

Conflict=case(Assigned_Role=Role_B AND Assigned_Role=Role_C, "Conflict",1=1,"No Conflict")

However, given the conflicting roles change based on the department, it will be easier, in the long run, to maintain a lookup for conflicting roles than continuously updating the query. 

I've also tried the following but none seem to work:

• Conflict=case(in(Roles,Conflicting_Roles), "True",1=1, "False")
• Conflict=if(isnotnull(mvfind(Roles,Conflicting_Roles)),"Matched","Not Matched")
• Conflict=if(match(Roles,Conflicting_Roles),"Conflict","No Conflict")

None seem to work as I need though, any thoughts or suggestions are greatly appreciated. Thank you

[theChain]

Try something made to give you a comparison of multivalue fields and can return a multivalue field of the intersecting values.

MVCompare | Splunkbase

[manjunathmeti]

@cquinney ,

You can sort multi values and then compare. Check if this works for you.

| makeresults 
| eval _raw="User,Role,Department,Conflicting_Roles,Justification
A,ZSRM_PROCESSINBOX;ZSRM_PURCHASING_BUYER;ZSRM_CENTRAL_SHOPPER,Tec Team,ZSRM_CENTRAL_SHOPPER;ZSRM_PROCESSINBOX,Management Said so.
B,ZSRM_PROCESSINBOX;ZSRM_PURCHASING_BUYER,HR,ZSRM_PROCESSINBOX;ZSRM_PURCHASING_BUYER,Management Said so.
C,ZSRM_CENTRAL_SHOPPER;ZSRM_PROCESSINBOX,HR,ZSRM_PROCESSINBOX;ZSRM_PURCHASING_BUYER,Management Said so.
D,ZSRM_PURCHASING_BUYER;ZSRM_CENTRAL_SHOPPER,Tec Team,ZSRM_CENTRAL_SHOPPER;ZSRM_PROCESSINBOX,Management Said so." 
| multikv forceheader=1 
| eval Role=split(Role,";"), Conflicting_Roles=split(Conflicting_Roles,";") 
| table User Role Department Conflicting_Roles Justification 
| eval nRole=mvjoin(mvsort(Role), ","), nConflicting_Roles=mvjoin(mvsort(Conflicting_Roles), ",") 
| eval result=if(match(nRole, nConflicting_Roles), "Conflict", "No Conflict") | fields - nRole, nConflicting_Roles

[to4kawa]

>match(multivalue_field,single_value) can work.

your query result is both multi-values. please separate to single value.

[cquinney]

Unfortunately, I require both values to be compared in their multi-valued state.  Otherwise, I continue to get false positives when a user has one of the conflicting roles but not a second or third which makes it "conflicting". 

A user can have a combination of the following roles and not be considered a conflict:

1.  ZSRM_CENTRAL_SHOPPER & ZSRM_PROCESSINBOX 

OR

2. ZSRM_PROCESSINBOX & ZSRM_PURCHASING_BUYER

However, a user cannot have all three (ZSRM_CENTRAL_SHOPPER, ZSRM_PROCESSINBOX & ZSRM_PURCHASING_BUYER). 

Therein lies the conflict that I'm trying to detect.  When I separate either the conflicting roles combination or the assigned user roles into a single value I get false positives for users that only have one of the conflicting roles but not both.  

[to4kawa]

| makeresults 
| eval _raw="User Role Department
A ZSRM_PROCESSINBOX\nZSRM_PURCHASING_BUYER\nZSRM_CENTRAL_SHOPPER Tec Team
B ZSRM_PROCESSINBOX\nZSRM_PURCHASING_BUYER HR" 
| multikv forceheader=1
| eval Role=split(Role,"\n") 
| table User Role Department 
| join Department 
    [| makeresults 
    | eval _raw="Department  Conflicting_Roles  Justification
Tec Team   ZSRM_CENTRAL_SHOPPER\nZSRM_PROCESSINBOX   Management Said so.
HR        ZSRM_PROCESSINBOX\nZSRM_PURCHASING_BUYER  Management Said so.
Financial ZSRM_PROCESSINBO\nZSRM_PURCHASING_BUYER  Management Said so." 
    | multikv forceheader=1 
    | eval Conflicting_Roles=split(Conflicting_Roles,"\n") 
    | table Department Conflicting_Roles Justification]
| rename COMMENT as "this is sample, from here, check sequence."
| mvexpand Role
| streamstats count(Role) as Role_count by User
| eval check_role=if(match(Conflicting_Roles,Role),"True","False")
| eventstats max(Role_count) as count count(eval(check_role="True")) as checked by User
| eval Conflict=if(count > checked ,"Conflict" ,"None")
```
| fields - Role_count check_role checked count
| stats values(*) as * by User
```

[to4kawa]

match(multivalue_field,single_value) can work.

mvfind'usage is  mvfind(MVFIELD,"REGEX")

https://docs.splunk.com/Documentation/Splunk/8.1.1/SearchReference/MultivalueEvalFunctions

sample:

|makeresults
| eval _raw="User Role Department
A Role_B Tec Team
B Role_B HR"
| multikv forceheader=1 
| table User Role Department
| join Department [| makeresults
| eval _raw="Department  Conflicting_Roles  Justification
Tec Team  Role_B\nRole_C  Management Said so.
HR        Role_A\nRole_C  Management Said so.
Financial Role_A\nRole_B  Management Said so."
| multikv forceheader=1
| eval Conflicting_Roles=split(Conflicting_Roles,"\n")
| table Department  Conflicting_Roles  Justification]
| eval result=if(match(Conflicting_Roles,Role),"True","False")

[cquinney]

Hi to4kawa,

Thank you for the help, unfortunately, I continue to get false readings when a user has more than two roles assigned.  I've attached a screenshot showing what I mean.  Any additional thoughts?