accounts and their associated entitlement count

[Harsha]

Hi,

We are trying to get the list of accounts from particular endpoint and count of their associated entitlements to see how many entitlements are tagged on an average to accounts.Can anyone help where the query is wrong I am able to retrieve only 1 user.Below are queries I tried.

PS: we want count not list of entitlements wrt account
1.select distinct u.username, a.name,e.endpointname, count(ae1.entitlement_valuekey),et.entitlementname from users u, user_accounts ua, accounts a, endpoints e, account_entitlements1 ae1,entitlement_types et,entitlement_values ev where u.userkey = ua.userkey and ua.accountkey = a.accountkey and a.accountkey = ae1.accountkey and e.endpointkey = a.endpointkey and et.ENTITLEMENTTYPEKEY =ev.ENTITLEMENTTYPEKEY and ae1.entitlement_valuekey=ev.entitlement_valuekey and e.endpointname like '%SAP%'

this is taking more than 10 secs to return

2.select u.username,a.name,e.endpointname,et.entitlementname,entl.Entitlements_Count as Entitlements_Count from (select count(ae1.entitlement_valuekey) Entitlements_Count,e.endpointname,e.endpointkey from endpoints e, entitlement_types et, entitlement_values ev,account_entitlements1 ae1 where e.endpointkey= et.endpointkey and et.ENTITLEMENTTYPEKEY =ev.ENTITLEMENTTYPEKEY and ae1.entitlement_valuekey=ev.entitlement_valuekey and e.endpointname='%SAPIDMDB%' group by e.endpointkey) entl,users u,accounts a,user_accounts ua,account_entitlements1 ae,endpoints e,entitlement_values ev,entitlement_types et where e.endpointkey= et.endpointkey

PS: Tried adding join didnt work as below .

3.SELECT
u.username,
a.name,
e.endpointname,
COUNT(ae1.entitlement_valuekey) AS Entitlements_Count,
et.entitlementname
FROM
users u
JOIN user_accounts ua ON u.userkey = ua.userkey
JOIN accounts a ON ua.accountkey = a.accountkey
JOIN endpoints e ON e.endpointkey = a.endpointkey
JOIN account_entitlements1 ae1 ON a.accountkey = ae1.accountkey
JOIN entitlement_values ev ON ae1.entitlement_valuekey = ev.entitlement_valuekey
JOIN entitlement_types et ON ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY
WHERE
e.endpointname LIKE '%SAP%'
AND et.entitlementname = 'Privilege'
GROUP BY
u.username,
a.name,
e.endpointname,
et.entitlementname

Thanks,

Harsha

[naveenss]

Can you try with the below query? It is hardly taking me a sec to get the result

SELECT DISTINCT
    u.username,
COUNT(ae1.entitlement_valuekey),
    a.name,
    e.endpointname,
    et.entitlementname
FROM
    users u,
    user_accounts ua,
    accounts a,
    endpoints e,
    account_entitlements1 ae1,
    entitlement_types et,
    entitlement_values ev
WHERE
    u.userkey = ua.userkey
        AND ua.accountkey = a.accountkey
        AND a.accountkey = ae1.accountkey
        AND e.endpointkey = a.endpointkey
        AND et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
        AND ae1.entitlement_valuekey = ev.entitlement_valuekey
        AND e.endpointname like '%SAP%'
group by a.name,et.entitlementname

[Harsha]

Hey,

Thank you for the quick response,in the analyzer I can see list but in analytics its giving popup error as shown below:

Thanks,

Harsha

[naveenss]

Hi @Harsha ,

This error is because the count(ae1.entitlement_valuekey) doesn't have an alias present. Can you please try with the below?

SELECT DISTINCT
    u.username,
COUNT(ae1.entitlement_valuekey) as 'Entitlement Count',
    a.name,
    e.endpointname,
    et.entitlementname
FROM
    users u,
    user_accounts ua,
    accounts a,
    endpoints e,
    account_entitlements1 ae1,
    entitlement_types et,
    entitlement_values ev
WHERE
    u.userkey = ua.userkey
        AND ua.accountkey = a.accountkey
        AND a.accountkey = ae1.accountkey
        AND e.endpointkey = a.endpointkey
        AND et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
        AND ae1.entitlement_valuekey = ev.entitlement_valuekey
        AND e.endpointname like '%SAP%'
group by a.name,et.entitlementname

Please let me know if this helps and in case of any further clarifications. 

[pmahalle]

Add group by ae1.accountkey at the end of the query

select distinct u.username, a.name,e.endpointname, count(ae1.entitlement_valuekey),et.entitlementname from users u, user_accounts ua, accounts a, endpoints e, account_entitlements1 ae1,entitlement_types et,entitlement_values ev where u.userkey = ua.userkey and ua.accountkey = a.accountkey and a.accountkey = ae1.accountkey and e.endpointkey = a.endpointkey and et.ENTITLEMENTTYPEKEY =ev.ENTITLEMENTTYPEKEY and ae1.entitlement_valuekey=ev.entitlement_valuekey and e.endpointname like '%SAP%' group by ae1.accountkey

[pmahalle]

Hi @Harsha ,

Did you tried the above one? Are you facing the same issue?