Collect App Roles from multiple Columns
Is it possible to collect AppRoles from the DB when there are multiple columns of Roles? Example:
I thought I could possibly create separate ent collectors for each Role column but that doesn't seem to be working. The resolutions fail and everything is rejected.
- app role
- Community Thread
- Data Collection
- entitlement collectors
- Forum Thread
- Identity G&L
- Identity Governance & Lifecycle
- RSA Identity
- RSA Identity G&L
- RSA Identity Governance & Lifecycle
- RSA Identity Governance and Lifecycle
- RSA IGL
The easiest option would be to use UNION for each Role column in your table. It would look something like this:
select account_name, role1 as role from my_table where role1 is not null
select account_name, role2 as role from my_table where role2 is not null
select account_name, role3 as role from my_table where role3 is not null
select account_name, role4 as role from my_table where role4 is not null
select account_name, role5 as role from my_table where role5 is not null
Some databases support transposing columns to rows, but I think that just adds un-necessary complexity to your SQL statement.
Depending on the database type you may also need to add a DISTINCT command to your query.
Not for each individual SELECT but in a single outer SELECT with one DISTINCT:
SELECT DISTINCT account_name, role FROM (
Thanks for that! Now comes the tricky part. What's the best way to assign the roles to accounts (App Roles for Accounts Query). Seems like you can only assign one role per collector per application unless I'm missing something. The only workaround I was able to come up with was to make a separate application for each role with a separate ent collector. Is there a better way?
You can definitely assign multiple application roles to accounts within the same collector.
Is there any way you can provide a sample of the data you're collecting from, obviously ensuring it doesn't contain any sensitive detail.
Thanks again, guys! Think I got it figured out, I needed to change the is not null to <> '' since I was dealing with blank values. Data is looking much cleaner now. Always get top-notch support with RSA, keep up the good work!