How to get those users as well who is belonging to endpoint

[gagan94]

Dear All

I have one requirement where we have to capture recon report for all active endpoints, The current report is capturing only those users which is having any of entitlements provisioned from role or individual one.

But we need to capture those users as well who do not have any role but tagged to particular endpoint. I have created one query which is not able to fulfil the above requirement.

select ss.SYSTEMNAME as 'Security_System',ep.endpointname as 'Endpoint_Name',CASE WHEN ev.ENTITLEMENT_VALUE is null THEN(select ev.DISPLAYNAME from entitlement_values ev) ELSE ev.ENTITLEMENT_VALUE end as 'Entitlemenet name',u.username as 'USERNAME',u.displayname as 'DISPLAYNAME',CASE U.STATUSKEY WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS USERSTATUS,acc.NAME as 'AccountName' ,(select updatedate from arstasks where taskkey=ae.ARSTASKKEY) as RoleGrantDate from users u,accounts acc,user_accounts ua,account_entitlements1 ae,entitlement_values ev,endpoints ep,securitysystems ss where acc.accountkey=ua.accountkey and ua.userkey=u.userkey and ae.accountkey=acc.accountkey and ae.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and acc.endpointkey=ep.endpointkey and acc.status=1 and ep.SECURITYSYSTEMKEY=ss.SYSTEMKEY and u.statuskey=1 AND
acc.status IN ( '1', 'Manually Provisioned', 'Active' )

Can Anybody help me in this one what needs to be modified here.

Many Thanks in Advance

Regards,

Gagan

[sk]

If I understood your requirement correctly You need to pull all accounts under a specific endpoint irrespective of whether they have entitlement or not right? In that case you need to do left join with account_entitlements1 table instead of simply joining the table

[gagan94]

Hey Sk,

Yes you understood requirement correctly.

I have used only these joins only for getting the data, Can you please help with the modified query with left join, I believe it would be helpful in creating all other reports as well.

Actually I am not sure for left join properly for fetching these data

[Abhishek]

select
u.username, u.firstname, u.lastname, u.statuskey as status_key, accounts.name as account_name, endpoints.ENDPOINTNAME, accounts.STATUS as account_status, et.displayname as entitlement_type, ev.entitlement_value
from accounts
left join user_accounts on accounts.ACCOUNTKEY=user_accounts.ACCOUNTKEY
left join endpoints on endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
left join users u on u.userkey = user_accounts.userkey
left join account_entitlements1 ae on ae.accountkey = accounts.ACCOUNTKEY
left join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey
left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey
where
endpoints.ENDPOINTNAME in ('xxxxxx') accounts.status in ('1','Active','Manually Provisioned')

see if this works?

[gagan94]

Hey Abhishek,

Thanks for making efforts.

Can you please add role information as well, Role Name or let me know what should be add here, because if entitlements provisioned from role then it is also required to be in report.

do we need to add role grant date using left join?

[rushikeshvartak · Accepted solution]

select
u.username, u.firstname, u.lastname, u.statuskey as status_key, accounts.name as account_name, endpoints.ENDPOINTNAME, accounts.STATUS as account_status, et.displayname as entitlement_type, ev.entitlement_value,r.role_name
from accounts
left join user_accounts on accounts.ACCOUNTKEY=user_accounts.ACCOUNTKEY
left join endpoints on endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
left join users u on u.userkey = user_accounts.userkey
left join account_entitlements1 ae on ae.accountkey = accounts.ACCOUNTKEY
left join roles r on ae.assignedfromrole=r.rolekey
left join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey
left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey
where
accounts.status in ('1','Active','Manually Provisioned')