@jordan_violet let me expand a bit on my post above with some visual examples.
My use case here is to show all identity accounts and their associated entitlements in a single table
For the first table, I get accounts associated with an identity using this query against /v3/search
{
"indices": [
"identities"
],
"query": {
"query": "attributes.uid:mcheek"
},
"queryResultFilter": {
"includes": [
"id",
"accounts.source.id",
"accounts.source.name",
"accounts.name",
"accounts.id"
]
},
"sort": [
"id"
]
}
That results in the table you see below. Note I have created a custom column concatenating the identity Id and the account source Id to use as a join key
Next, I’ll pull all my access using the same query but returning different values
{
"indices": [
"identities"
],
"query": {
"query": "attributes.uid:mcheek"
},
"queryResultFilter": {
"includes": [
"id",
"access.displayName",
"access.source.name",
"access.source.id"
]
},
"sort": [
"id"
]
}
That results in this table. Again, I have created a custom column concatenating the identity Id and Source Id of the entitlement
When I tell Power BI that I want to create a join relationship between those two tables, it correctly identifies the cardinality as 1:many between accounts and access
Let’s look at another user who has two accounts with their own entitlements in a single source
You’ll see in his access that he has 3 entitlements in that source, with two duplicate rows because both accounts have a common entitlement
When I attempt to join these two tables, it correctly identifies the cardinality as many:many
The result is that it incorrectly shows the CHENRY account has the entitlement Security
When you look at his accounts in the JSON, that’s not correct
{
"privileged": false,
"accountId": "adm_chenry",
"entitlementAttributes": {
"Role": [
"Security",
" System Administrators Only"
]
},
"created": "2022-07-07T13:39:01.123Z",
"name": "adm_chenry",
"disabled": false,
"id": "2c9180858191a33b0181d8e2a58337e4",
"source": {
"name": "Enertia",
"id": "2c91808481d42b010181d8d5e6656219",
"type": "JDBC"
},
"locked": false,
"manuallyCorrelated": true
},
{
"privileged": false,
"accountId": "CHenry",
"entitlementAttributes": {
"Role": [
" System Administrators Only"
]
},
"created": "2022-07-07T13:39:01.875Z",
"name": "CHenry",
"disabled": false,
"id": "2c9180858191a33b0181d8e2a8732cb5",
"source": {
"name": "Enertia",
"id": "2c91808481d42b010181d8d5e6656219",
"type": "JDBC"
},
"locked": false,
"manuallyCorrelated": false
}
This is due to the fact that the access sub-collection doesn’t have a reference to which account is assigned an entitlement.
{
"privileged": false,
"displayName": "Security",
"name": "Security",
"standalone": true,
"id": "2c9180838191a33a0181d8e2a9ea24bd",
"source": {
"name": "Enertia",
"id": "2c91808481d42b010181d8d5e6656219"
},
"attribute": "Role",
"type": "ENTITLEMENT",
"value": "Security"
}
If the schema could be updated to do something as simple as this
{
"privileged": false,
"displayName": "Security",
"name": "Security",
"standalone": true,
"id": "2c9180838191a33a0181d8e2a9ea24bd",
"source": {
"name": "Enertia",
"id": "2c91808481d42b010181d8d5e6656219"
},
"attribute": "Role",
"type": "ENTITLEMENT",
"value": "Security",
"account": {
"name": "adm_chenry",
"id": "2c9180858191a33b0181d8e2a58337e4"
}
}
That would solve this issue