topic Re: Compare Multivalue Fields With Lookup in Splunk Search

Compare Multivalue Fields With Lookup

cquinney — Fri, 15 Jan 2021 22:52:51 GMT

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

Re: Compare Multivalue Fields With Lookup

to4kawa — Fri, 15 Jan 2021 23:30:55 GMT

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

Re: Compare Multivalue Fields With Lookup

cquinney — Mon, 18 Jan 2021 16:09:42 GMT

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?

Re: Compare Multivalue Fields With Lookup

to4kawa — Tue, 19 Jan 2021 02:23:49 GMT

>match(multivalue_field,single_value) can work.

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

Re: Compare Multivalue Fields With Lookup

cquinney — Tue, 19 Jan 2021 14:33:27 GMT

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

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.

Re: Compare Multivalue Fields With Lookup

to4kawa — Tue, 19 Jan 2021 21:44:28 GMT

| 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 ```

Re: Compare Multivalue Fields With Lookup

manjunathmeti — Wed, 20 Jan 2021 07:31:37 GMT

@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

Re: Compare Multivalue Fields With Lookup

theChain — Mon, 23 Aug 2021 02:26:05 GMT

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

MVCompare | Splunkbase