Set Account query - successfully aggregate 7 accounts:
SELECT
u.UserId,
u.UserName,
m.Email, -- Added Email column from aspnet_Membership
FROM
dbo.vw_aspnet_UsersInRoles ur
LEFT JOIN dbo.vw_aspnet_Roles r ON r.RoleId=ur.RoleId
LEFT JOIN dbo.vw_aspnet_Applications a ON a.ApplicationId=r.ApplicationId
LEFT JOIN dbo.vw_aspnet_Users u ON u.UserId=ur.UserId
LEFT JOIN dbo.aspnet_Membership m ON m.UserId=ur.UserId
WHERE
a.ApplicationName='/DWISE'
;
Create new entitlement type called “RoleName” and add it to Account Schema and set it as “Entiltement” and “multi-valued”
Crate group SQL query for RoleName
SELECT
r.RoleName
FROM
dbo.vw_aspnet_UsersInRoles ur
LEFT JOIN dbo.vw_aspnet_Roles r ON r.RoleId=ur.RoleId
LEFT JOIN dbo.vw_aspnet_Applications a ON a.ApplicationId=r.ApplicationId
LEFT JOIN dbo.vw_aspnet_Users u ON u.UserId=ur.UserId
WHERE
a.ApplicationName='/DWISE'
;
Result for Account Aggregation for both queries is following: i’m able to aggregate accounts and assigned roleName but only one value.
QUESTION:
Some of the users have two roles (for example JXIN is having RISK and VIEWER roles.
What am i missing in my query or configuration?
Many thanks for support
From what I can tell, it looks like your Entitlement Config is not completely set up. You need to add at least one attribute under your Entitlement Type for the correlation between Account/Entitlement to work.
In your case, it looks like you need to add an attribute called “RoleName”, since the SQL query for your Entitlement Type only selects that:
SELECT
r.RoleName
FROM
dbo.vw_aspnet_UsersInRoles ur
LEFT JOIN dbo.vw_aspnet_Roles r ON r.RoleId=ur.RoleId
LEFT JOIN dbo.vw_aspnet_Applications a ON a.ApplicationId=r.ApplicationId
LEFT JOIN dbo.vw_aspnet_Users u ON u.UserId=ur.UserId
WHERE
a.ApplicationName='/DWISE'
;
So follow these steps to fix:
Add the “RoleName” attribute to your Entitlement Type. (Needs to match the exact name from the SQL Query.)
Make sure you set that new attribute to “Entitlement Name” and “Entitlement ID” (If you don’t want the “RoleName” to be both the “Entitlement Name” and “Entitlement ID” then you need to SELECT an ID column in your SQL QUERY. (Remember: if you do that, you have to add that ID name to the attributes on your Entitlement Types and it has to match the ID name in the SQL query exactly.))
No errors during account aggregation, no duplicated accounts
However:
as a Account Query i have to use following to concatenate all roles to single account:
SELECT
u.UserId,
u.UserName,
MAX(m.Email) AS Email,
STRING_AGG(r.RoleName, ', ') AS RoleName
FROM
dbo.vw_aspnet_UsersInRoles ur
LEFT JOIN dbo.vw_aspnet_Roles r ON r.RoleId=ur.RoleId
LEFT JOIN dbo.vw_aspnet_Applications a ON a.ApplicationId=r.ApplicationId
LEFT JOIN dbo.vw_aspnet_Users u ON u.UserId=ur.UserId
LEFT JOIN dbo.aspnet_Membership m ON m.UserId=ur.UserId
WHERE
a.ApplicationName='/DWISE'
GROUP BY
u.UserId,
u.UserName;
Group SQL query look like below:
SELECT
r.RoleName
FROM
dbo.vw_aspnet_UsersInRoles ur
LEFT JOIN dbo.vw_aspnet_Roles r ON r.RoleId=ur.RoleId
LEFT JOIN dbo.vw_aspnet_Applications a ON a.ApplicationId=r.ApplicationId
LEFT JOIN dbo.vw_aspnet_Users u ON u.UserId=ur.UserId
WHERE
a.ApplicationName='/DWISE'
;
What this means is that your Account SQL Query successfully aggregated all users and RoleNames from the database.
It also means your Group/Entitlement SQL Query successfully aggregated all entitlements from the database.
However, there was an issue with tying the Entitlements to the Accounts in ISC.
There are several reason why this might happen, but instead of guessing at the answer, you’ll want to walk through triage/troubleshooting steps first. I would start with Resetting Accounts and Entitlements again…
Then Aggregate Accounts first this time.
Then Aggregate Entitlements next.
Test again and let me know.
Maybe if you are expecting something like this where you want the roles to be treated as individual entities rather than single entity separated by comma, then can you try using this account query if you are allowed to change: (Make sure the RoleName is marked multi-valued in account schema).
SELECT
u.UserId,
u.UserName,
m.Email,
r.RoleName
FROM
dbo.vw_aspnet_UsersInRoles ur
LEFT JOIN dbo.vw_aspnet_Roles r ON r.RoleId=ur.RoleId
LEFT JOIN dbo.vw_aspnet_Applications a ON a.ApplicationId=r.ApplicationId
LEFT JOIN dbo.vw_aspnet_Users u ON u.UserId=ur.UserId
LEFT JOIN dbo.aspnet_Membership m ON m.UserId=ur.UserId
WHERE
a.ApplicationName='/DWISE'
ORDER BY u.UserId
;
Though this query will give you multiple entries for a user with RoleNome being unique in each entry, something like
I guess SailPoint itself should be able to handle multiple group assignment to the accounts with no accounts being duplicated. It should work for any sources using SQL like JDBC or SQL Loader.
If this is what you expect, then you can make your group query simple too by just selecting all from the dbo.vw_aspnet_Roles table.
