Indication of SoD conflicts

[slyfox]

Hello,

The topic of Segregation of Duties (SoD) monitoring comes up frequently and now I am wondering how to make an analytical report that can show conflicts in SoD
I have two tables, one shows a list of users and a list of security groups that are assigned and the second table shows which security groups are in conflict with each other.

Does anyone have experience with such reports? and how to find users who are members of conflicting security groups.

[johnt75 · Accepted solution]

I'm making some assumptions about your model. Firstly, the list of users and security groups has multiple rows for each user, one row per security group they are a member of. Secondly, the conflicting security groups table has a primary group column and a conflicting group column, such that if groups A and B were conflicting there would be 2 rows in the table - one with A as primary and B as conflict and a second row with B as primary and A as conflict.

Given that, you could create a user dimension table like

Users = ALLNOBLANKROW('User security group'[User])

and then create a one-to-many relationship from the new dimension table to the user groups table. Add a new column to the dimension table like

Is in conflicting groups =
VAR UserGroups =
    VALUES ( 'User security group'[group] )
VAR ConflictingGroups =
    CALCULATETABLE (
        VALUES ( 'Conflicting groups'[conflicting group] ),
        TREATAS ( UserGroups, 'Conflicting groups'[Primary group] )
    )
VAR Result =
    NOT ISEMPTY ( INTERSECT ( UserGroups, ConflictingGroups ) )
RETURN
    Result

[johnt75 · Accepted solution]

I'm making some assumptions about your model. Firstly, the list of users and security groups has multiple rows for each user, one row per security group they are a member of. Secondly, the conflicting security groups table has a primary group column and a conflicting group column, such that if groups A and B were conflicting there would be 2 rows in the table - one with A as primary and B as conflict and a second row with B as primary and A as conflict.

Given that, you could create a user dimension table like

Users = ALLNOBLANKROW('User security group'[User])

and then create a one-to-many relationship from the new dimension table to the user groups table. Add a new column to the dimension table like

Is in conflicting groups =
VAR UserGroups =
    VALUES ( 'User security group'[group] )
VAR ConflictingGroups =
    CALCULATETABLE (
        VALUES ( 'Conflicting groups'[conflicting group] ),
        TREATAS ( UserGroups, 'Conflicting groups'[Primary group] )
    )
VAR Result =
    NOT ISEMPTY ( INTERSECT ( UserGroups, ConflictingGroups ) )
RETURN
    Result

[slyfox]

@johnt75  In the Matrix table, in the rows - the main group, in the column - the conflicting group, how to show in the Values the number of users?

[johnt75 · Accepted solution]

would a simple COUNTROWS on the user table work ?

[slyfox]

-

[Ashish_Mathur]

Hi,

Share some data, describe the question and show the expected result.