Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Order by function is not working in Saviynt

Siva
Regular Contributor
Regular Contributor

Hi experts,

We are creating a report that should fetch count of most frequently requested entitlements in Desceding order, we are able to fetch the entitlements, but even if we are using order by function  count is not getting sorted by count automatically.

 SELECT
distinct count(distinct (substring_index(ar.JBPMPROCESSINSTANCEID,'.',-1))) as 'count',
ev.Displayname AS 'Entitlement Value',e.displayname as 'Endpoint' FROM ars_requests ar, request_access ra, request_access_attrs ras, entitlement_values ev, endpoints e where ar.requestkey=ra.requestkey and ra.request_accesskey=ras.request_access_key and ar.requesttype='1'and ras.attribute_name='ENDPOINT' and ev.entitlement_Valuekey= ra.ACCESSKEY and e.endpointkey= ras.attribute_value and e.endpointname not in ('saviynt','Organizations','Datasets') group by ev.Displayname ORDER BY count(ev.Displayname) DESC LIMIT 20;

Could you please help me here, if any way to achieve this?

Thanks
Siva Avula

6 REPLIES 6

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Siva,

You may try the below query to achieve your use case,

SELECT distinct count(distinct (substring_index(ar.JBPMPROCESSINSTANCEID,'.',-1))) as 'count',
ev.Displayname AS 'Entitlement Value',e.displayname as 'Endpoint' FROM ars_requests ar, request_access ra, request_access_attrs ras, entitlement_values ev, endpoints e where ar.requestkey=ra.requestkey and ra.request_accesskey=ras.request_access_key and ar.requesttype='1'and ras.attribute_name='ENDPOINT' and ev.entitlement_Valuekey= ra.ACCESSKEY and e.endpointkey= ras.attribute_value and e.endpointname not in ('saviynt','Organizations','Datasets') group by ev.Displayname ORDER BY count DESC LIMIT 20;

Thanks,

If you find the above response useful, Kindly Mark it as "Accept As Solution".

Siva
Regular Contributor
Regular Contributor

Hello @sudeshjaiswal 

Thanks for the query, Still the count is coming in Ascending order only.
Is there any limitation from saviynt side like first coulmn in the analytic report is automatically arranged in ascending order?

Thanks
Siva Avula

Hi @Siva 

I think in analytics , by default it sorted into ascending order based on first column in your query.

If first column is count then the output will be sorted into ascending order based on count.

If first column is application name then the output will be sorted into ascending order based on application name.

Hope this helps!!

Thanks

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.

Darshanjain
Saviynt Employee
Saviynt Employee

Hi @Siva 

If you are using the query in Analytics the first column is always taken into consideration and order by is used and  displayed in ascending order. So please construct your query accordingly.

 

Thanks

Darshan

Siva
Regular Contributor
Regular Contributor

Hi @Darshanjain 

Thanks the info,
Can you please help me , What if, If I want to make any column in Descending order in analytic query?How can i do that?
Is it doable?

Thanks
Siva Avula


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.