Pulling entitlements& SS of all active accounts

[PA]

I need to generate a report with all active accounts and their associated entitlements and security system. I am using following query which runs fine in data analyzer but when I actually run analytic query the fields are not aligned. Is there something wrong with the query?

select a.name as account_name, ev.entitlement_value, s.systemname from accounts a left join account_entitlements1 ae on a.accountkey=ae.accountkey left join entitlement_values ev on ae.entitlement_valuekey=ev.entitlement_valuekey left join securitysystems s on a.accountkey=s.systemkey where a.status in ('Active',1,'manually provisioned','active')

[Sivagami · Accepted solution]

You can't join securitysystemkey and accountkey

select a.name as account_name, ev.entitlement_value, s.systemname from accounts a left join account_entitlements1 ae on a.accountkey=ae.accountkey left join entitlement_values ev on ae.entitlement_valuekey=ev.entitlement_valuekey left join securitysystems s on a.accountkey=s.systemkey where a.status in ('Active',1,'manually provisioned','active')

Try this query

select a.name as AccountName, ev.entitlement_value as EntitlementName, s.systemname as SecuritySystemName from accounts a left join account_entitlements1 ae on a.accountkey=ae.accountkey left join entitlement_values ev on ae.entitlement_valuekey=ev.entitlement_valuekey left join endpoints e on e.endpointkey=a.endpointkey left join securitysystems s on e.securitysystemkey=s.systemkey where a.status in (1,'Manually Provisioned','Active') 

-Siva

[dgandhi · Accepted solution]

The join that you did is not correct. In accounts table endpoint key is stored and not the securitysystem key

<<

select a.name as AccountName, a.status, ev.entitlement_value as EntitlementName, s.systemname as SecuritySystemName ,e.endpointname as "Endpointname"
from accounts a left join account_entitlements1 ae
on a.accountkey=ae.accountkey
left join entitlement_values ev
on ae.entitlement_valuekey=ev.entitlement_valuekey
left join endpoints e
on e.endpointkey=a.endpointkey
left join securitysystems s on
e.securitysystemkey=s.systemkey
where a.status in (1,'Manually Provisioned','Active')

>>