Courion Tip – Querying the Role Mining Master

Using the “Role Mining Master” view <proposed in one of my other posts>, it’s pretty easy to query it for what Group Memberships / Roles a set of users has on which targets, and to sort that by how many of those users have that attribute:

SELECT GroupName,TargetID, COUNT(GroupName) as 'UserCount'
FROM Role_Mining_Master where profileuid IN
('ProfileUID_1','ProfileUID_2','ProfileUID_3')   <-UserID's to query
GROUP BY GroupName, Targetid order by UserCount desc

(And, yes, Role Courier does present a nice graphical interface of similar information)  The “order by UserCount desc” puts the attributes that all (or almost all) users have at the top, and the attributes that the fewest number of users have at the bottom. In other words, the stuff that you would most likely want to use to define a role based on those people will be at the top, and the stuff you probably don’t want will be at the bottom.

You can, of course, put an “INSERT INTO <tablename>” at the front of this, and it will save it into your (previously prepared) SQL Server table. Then you can simply delete the rows that you have decieded should not be part of that role.  Or export it to an Excel spreadsheet and email it around for approval…


Posted August 6, 2010 by mmdmurphy in Courion Tip

Tagged with

%d bloggers like this: