Following SQL present usage of permissiosn accross all Apps by Identity attributes,you can findquery how permissiosn are spread accross business functions (employee job) simply by chossing the right attributes of the identities such as department, job code etc.
Note: it is higly recommended to add in WHERE specific apps at a time for large enterprise to avoid overload on DB.
in this example we use only one attribue name department, however you can add more such as jobCode, for adding a JobCod you need to add it in the select and also in the GroupBy section. see yellow marked parts.
with all_users as
(SELECT
[identityiq].[identityiq].[spt_identity].extended1,
count(*) TOTALUSERS
FROM [identityiq].[identityiq].[spt_identity] group by extended1),
ent_users as
(SELECT
[identityiq].[identityiq].[spt_identity].extended1,
count(*) HASENTITLEMET,
[identityiq].[identityiq].[spt_identity_entitlement].value,
[identityiq].[identityiq].[spt_Application].name
FROM [identityiq].[identityiq].[spt_identity],[identityiq].[identityiq].[spt_Application], [identityiq].[identityiq].[spt_identity_entitlement]
where
[identityiq].[identityiq].[spt_identity].id=[identityiq].[identityiq].[spt_identity_entitlement].identity_id
and [identityiq].[identityiq].[spt_Application].id=[identityiq].[identityiq].[spt_identity_entitlement].application
group by [identityiq].[identityiq].[spt_identity].extended1,[identityiq].[identityiq].[spt_identity_entitlement].value,[identityiq].[identityiq].[spt_Application].name
)
select
all_users.extended1 department,
all_users.TOTALUSERS,
ent_users.name application,
ent_users.value entitlment,
ent_users.HASENTITLEMET,
ent_users.HASENTITLEMET/all_users.TOTALUSERS percentage
from all_users,ent_users
where
all_users.extended1=ent_users.extended1
