#58 | Part 6 Conflict List | NetSuite Segregation of Duties

Welcome to part 6, the last part in our Segregation of Duties Analysis for NetSuite. In this last part, we will finally come up with our conflict list. We did steps 1 through 5 to get to this final list.

Remove duplicates
Remove no matches
Remove false positives

I suggest you watch the video. It’s easier to understand if you are a visual/audio learner. The content below is the same as the video. It’s for those who learn by reading.

In this session, we’re going to show you how to remove duplicates, remove no matches, and how to identify potential false positives and remove those. This session is the mechanics of the process.

In the first step we will create a new sheet, and we’ll call the sheet “Final List for SOD.” We’ll copy and paste the filtered list of employees onto the new sheet; this is so we can keep the integrity of the original information.

Final list for Segregation of Duties — Final List for SOD

Final list for Segregation of Duties in excel — Fig. 1 – Employees with potential SOD

The list above is of the employees that are potentially affected by the segregation of duty rules. We want to reduce this list even further.

Remove Duplicates

The way we are going to do that is we are going to select column A and add a conditional format. We’re going to highlight all the duplicate values and then we’ll sort it.

Identify Duplicate values to Remove — Identify Duplicates to Remove

The names that don’t have duplicates mean they don’t have conflicts from our SOD rule set, so we can delete them. We’ll work with those that have permission conflicts, which are the highlighted ones.

Remove No Matches

If you have a larger rule set, meaning you have more rules besides the basic four, you’ll have a longer list, but in our case, we wanted to keep it simple. With this small list, you can easily see who has permission conflicts like Edison and Randy. What you want to do is find all the possible combinations between all of their permissions.

How do we do that when you have too many rows of data? You can use Microsoft access or other programs like ACL or audit command language, but right now, we’ll just use excel.

Here’s our formula that gives us all the possible pairs between the two lists found in column A and column B. We are giving you the formula.

Formula for possible combinations between permissions — Formula to Find Name + Permission

We can start with Dave Harrington from the previous example.

Name and permission list — Fig. 3 – Name and Permission excel sheet

We’ll copy his name and permission, and paste in a sheet where we have our formula. We’ll paste his permission from column A and column B, so that it will give you all the possible combinations, which is column D where our formula is.

Fig. 4 – Employee and Permission Combinations in excel sheet

Column A and B are just concatenate of all the possible solutions. Then we do the same for the rest of employees to get the possible combinations. As you notice on this screenshot, the list is getting longer and longer because for Edison, it turns out that he has many possible combinations. You do the same for the rest of employees.

Then we copy and paste all the combinations in column J, which is the permission column.

list with the possible combinations — Fig. 5 – Name and Permissions Results

If we have a bigger segregation of duties rule set, meaning more than four, we’ll be dealing with the larger data set. But the concept is the same, you want get all the possible pairs for the permission that each person has. Now, we have that list with the possible combinations.

We then create a new tab, and we call it all employee pairs, and we’ll copy and paste the list above into a new tab, which is now called employee all pairs.

Concatenate SOD rule permissions — Fig. 6 – Concatenate X and Y Permissions

Next, we want to concatenate our SOD rule set permissions from earlier in the previous sessions part 1, 2, or 3, where we have the rule set from X and Y. Then, we get a list (see box). I will also show you the formula.

result of concatenate SOD — Fig. 7 – Concatenate Results

Next, what we do is we want to find all the segregation duty conflicts.

SOD conflicts using this formula — Formula to find conflicts

The beauty of this report is we don’t just find conflicts in one rule, but we can find the conflicts between several rules for each person. We will use this formula to find all the different conflicts.

Remove False Positives

We went to index column A, where our permission matches with our segregation of duties rule set. In column F, we want an exact match and index, and filter removes all the NA’s. That’s the overall process, will show you what that looks like.

Filtering by removing the N/A — Process of removing the false positives

Close match and close the index, and hit enter. We filter out and remove all of the NA’s in column C.

Then we find that we have all four conflicts and they’re related to Randy.

Identify conflicts — Fig. 9 – Identity conflicts in excel

Now we can expand our spreadsheet. We can add more formulas to show what the conflicts are and what they mean. From our rule set, we can pull what the risk or the description of conflicts are.