Analytics - Incorrect results after Joining Endpoint table to ARS request table

[AnkitaBamania]

Hello Team,

We have to create the analytics report which will fetch all the ARS requests created within 7 days. In that report we need Application name for which the account/access is requested. In ars_requests table we have ENDPOINTASCSV column which we are trying to join with endpointname in endpoints table.

This join is working fine when we are requesting for single endpoint/application request but in case when multiple application is requested the record is not included in the report because ENDPOINTASCSV contains application names in comma separated format.

eg: application1, application2

I had tried using FIND_IN_SET() function but for application2 access also it is printing application1 endpoint name which is incorrect.

Can anyone help me on this?

[armaanzahir]

Hi @AnkitaBamania ,

Can you share the query you're using? 

[pmahalle]

Hi @AnkitaBamania ,

Can you share your whole query.

[rushikeshvartak · Accepted solution]

select count(e.endpointname) as COUNT,e.endpointname as Application from ars_requests ar,request_access ra,request_access_attrs ras,endpoints e where ar.requestkey=ra.requestkey and ra.request_accesskey=ras.request_access_key and ar.requesttype=3 and ra.accesstype=3 and ras.attribute_name='ENDPOINT' and e.endpointkey=ras.attribute_value  group by e.endpointname
ORDER BY count(e.endpointname) DESC

[AnkitaBamania]

It worked for me !! Thanks @rushikeshvartak