Need to get all the Entitlement/Role Owners

[MarcoApollo]

Dear All,

We are using the below query in analytics and sending all the information about

1. Application Name

2. Entitlements + Description

3. Entitlements Owner1, 2...

Now there is an issue where we are not getting the information from the entitlements if the customproperty5 contains only manager as approver " Manager " . and the Owner table is blank. See attached.

If customproperty5 contains " Owner1 " or " Owner2" this query listed below provides all the information.

See below the query we are using:

select ep.endpointname,ev.customproperty12 as 'RoleName', ev.description, ev.customproperty5, ev.customproperty6, ev.customproperty9, ev.customproperty8, ev.customproperty7, ev.customproperty10, ev.customproperty12, ev.customproperty13, u.username,u.email from entitlement_values ev, entitlement_owners eo, users u, entitlement_types et, endpoints ep where ev.entitlement_valuekey=eo.entitlement_valuekey and eo.userkey=u.userkey and et.entitlementtypekey=ev.entitlementtypekey and et.endpointkey=ep.endpointkey and ev.entitlementtypekey not in (1,2)

Many thanks

Kind Regards

Marco

[Raghu]

@MarcoApollo  try below sample query

SELECT
ep.endpointname,
ev.customproperty12 AS 'RoleName',
ev.description,
ev.customproperty5,
ev.customproperty6,
ev.customproperty9,
ev.customproperty8,
ev.customproperty7,
ev.customproperty10,
ev.customproperty12,
ev.customproperty13,
u.username,
u.email
FROM
entitlement_values ev
LEFT JOIN
entitlement_owners eo ON ev.entitlement_valuekey = eo.entitlement_valuekey
LEFT JOIN
users u ON eo.userkey = u.userkey
JOIN
entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN
endpoints ep ON et.endpointkey = ep.endpointkey
WHERE
ev.entitlementtypekey NOT IN (1, 2)
AND (ev.customproperty5 = 'Manager' OR ev.customproperty5 IN ('Owner1', 'Owner2'));

[rushikeshvartak]

SELECT ep.endpointname,
       ev.customproperty12 AS 'RoleName',
       ev.description,
       ev.customproperty5,
       ev.customproperty6,
       ev.customproperty9,
       ev.customproperty8,
       ev.customproperty7,
       ev.customproperty10,
       ev.customproperty12,
       ev.customproperty13,
       u.username,
       u.email
FROM   entitlement_values ev
       LEFT JOIN entitlement_owners eo
              ON ev.entitlement_valuekey = eo.entitlement_valuekey
       LEFT JOIN users u
              ON eo.userkey = u.userkey
       JOIN entitlement_types et
         ON et.entitlementtypekey = ev.entitlementtypekey
       JOIN endpoints ep
         ON et.endpointkey = ep.endpointkey
WHERE  ev.entitlementtypekey NOT IN ( 1, 2 );