Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Union in analytics query

Sitarasmi
Regular Contributor
Regular Contributor

Hi Team,

We are using below query to fetch account data from different entitlements and trying use union query. Its giving error. 

 

Can anyone please check & guide what is wrong with this.

 

select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE 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 entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory', 'Active Directory Admin Accounts') and ev.ENTITLEMENT_VALUE in ( 'Group1', 'Group2', ) and a.STATUS in (1, 'Active')
union
select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE 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 entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory Admin Accounts') and a.DISPLANAME in ('Acc1', 'Acc2', 'Acc3') and a.STATUS in (1, 'Active')

 

Thanks,

Sitarasmi

4 REPLIES 4

dgandhi
All-Star
All-Star

Please try below:

select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE 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 entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory', 'Active Directory Admin Accounts') and ev.ENTITLEMENT_VALUE in ( 'Group1', 'Group2') and a.STATUS in (1, 'Active')
union
select a.NAME, a.DISPLAYNAME, a.COMMENTS as 'DN', a.STATUS as 'Account_Status', ev.ENTITLEMENT_VALUE 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 entitlement_types et on ev.ENTITLEMENTTYPEKEY = et.ENTITLEMENTTYPEKEY left join endpoints e on et.ENDPOINTKEY = e.ENDPOINTKEY where e.ENDPOINTNAME in ( 'Active Directory Admin Accounts') and a.DISPLANAME in ('Acc1', 'Acc2', 'Acc3') and a.STATUS in (1, 'Active')

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

Sitarasmi
Regular Contributor
Regular Contributor

Thank you so much for quick support.

Can i just ask one more query - the second query (after Union) returns the accounts with all entitlements, We just want to fetch the acocunt name through second one.

Please suggest if this is possible.

Thanks,

Sitarasmi

Try something like below:

dgandhi_0-1714147103595.png

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

Sitarasmi
Regular Contributor
Regular Contributor

Thank you for you quick support, its working now.