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.
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.
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.
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.
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.
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.
We can start with Dave Harrington from the previous example.
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.
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.
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.
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.
Next, what we do is we want to find all the segregation duty 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.
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.
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.
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.
We’ve gone through a lot of steps, but let’s recap.
We have discussed the following:
I hope this is helpful, and we look forward to the next session with you. Have a great day!