Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

generate a report for the applications which are frequently requested.

AshirvadhN
Regular Contributor
Regular Contributor

Hi Team,

Does this query is correct.

We need generate a report for the applications which are frequently requested.

select now() as 'report run date',ep.endpointkey, ep.endpointname,ss.SYSTEMNAME, count(ep.endpointkey) as 'COUNT' from arstasks at, endpoints ep, securitysystems ss where at.endpoint=ep.endpointkey and at.securitysystem=ss.SYSTEMKEY group by ep.endpointkey

16 REPLIES 16

nimitdave
Saviynt Employee
Saviynt Employee

ars_requests will provide more accurate data as arstasks table will provide data from rules, certifications, apis etc. Also arstasks table will miss the request count if the request is discontinued, pending etc.

Siva
Regular Contributor
Regular Contributor

Hi @nimitdave 
Can you please provide any example if you have ?
That would help us a lot

Thanks 
Siva

AshirvadhN
Regular Contributor
Regular Contributor

Hi Team,

Can someone see my above query is correct or not.

nimitdave
Saviynt Employee
Saviynt Employee

select count(REQUESTKEY) as 'Request Count',ENDPOINTASCSV as 'Application'
from ars_requests ar
group by ENDPOINTASCSV
order by count(REQUESTKEY) desc;

dgandhi
All-Star
All-Star

Hi

Can you try this query?

dgandhi_0-1680746643656.png

Thanks

Devang

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.

Siva
Regular Contributor
Regular Contributor

HI @dgandhi @nimitdave 
Thannks for query. Both queries are working fine, but one discrepancy is there.

select ENDPOINTASCSV as 'Application',count(ar.ENDPOINTASCSV) as 'Request Count' from ars_requests ar,request_access ra where ar.REQUESTKEY = ra.REQUESTKEY AND ar.requesttype='3' group by ar.ENDPOINTASCSV
ORDER BY count(ar.ENDPOINTASCSV) DESC limit 20

Above query results 

Siva_0-1681090329171.png

We trying to limit the records to 20 and count should in DESC order , so that we can the Most requested applications at top.

Here it seems order by DESC is not working.

Could you please suggest alternative to achieve this?

Thanks 
Siva Avula

dgandhi
All-Star
All-Star

The query looks correct to me and it gives me correct output in MySQL workbench.

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.

Siva
Regular Contributor
Regular Contributor

Hi @dgandhi @nimitdave 

Could you please let me know
In Analytics reports ,Do we  have a limitation  while using group by and order by combinaation?
Becuse here I am trying to use the group by function so its showing the records in Alphabetical order but after that I tried to order th count by DESC which is not happening at all.
Could you please check this once.

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.

Siva
Regular Contributor
Regular Contributor

Hi @dgandhi @nimitdave 
I am unable the fetch the record which has bulk application in a single request.
I can fectch only records where one application is requested each time.
Could you help us here, Is there anyway to achive this.

Thanks
Siva Avula

select e.endpointname as application,count(*) requestcount from arstasks at,endpoints e where requestkey is not null and e.endpointkey=at.endpoint group by endpointname


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

Siva
Regular Contributor
Regular Contributor

Hi Rushi
Thanks for the query,
Here we have a problem while fetching the records from arstasks,becuase sometimes task wont be created for rejected applications.
I need to fetch the application from request history only ,irrespective of the task created or not.
I am using the below query

select ENDPOINTASCSV as 'Application',count(ar.REQUESTORIGIN) as 'Count' from ars_requests ar
Join endpoints ep 
on ar.ENDPOINTASCSV=ep.ENDPOINTNAME and ar.requesttype='3'
where ar.ENDPOINTASCSV not in ('Saviynt','Datasets','Organizations')
group by ar.ENDPOINTASCSV

from the above query , I am unable to fetch record which bulk application requests in single request.
Could you pls help me here.

Thanks
Siva Avula


Considering max 4 apps requested in bulk 

SELECT ENDPOINTASCSV AS 'Application',
count(ar.REQUESTORIGIN) AS 'Count'
FROM ars_requests ar
JOIN endpoints ep ON ar.ENDPOINTASCSV=ep.ENDPOINTNAME
WHERE ar.ENDPOINTASCSV not in ('Saviynt',
'Datasets',
'Organizations')
AND ENDPOINTASCSV not like '%,%'
GROUP BY ar.ENDPOINTASCSV
UNION
SELECT substring_index(endpointascsv, ',', 1) AS 'Application',
count(ar.REQUESTORIGIN) AS 'Count'
FROM ars_requests ar
JOIN endpoints ep ON substring_index(endpointascsv, ',', 1)=ep.ENDPOINTNAME
WHERE ar.ENDPOINTASCSV not in ('Saviynt',
'Datasets',
'Organizations')
AND ENDPOINTASCSV like '%,%'
GROUP BY ar.ENDPOINTASCSV
UNION
SELECT substring_index(endpointascsv, ',', 2) AS 'Application',
count(ar.REQUESTORIGIN) AS 'Count'
FROM ars_requests ar
JOIN endpoints ep ON substring_index(endpointascsv, ',', 2)=ep.ENDPOINTNAME
WHERE ar.ENDPOINTASCSV not in ('Saviynt',
'Datasets',
'Organizations')
AND ENDPOINTASCSV like '%,%'
GROUP BY ar.ENDPOINTASCSV
UNION
SELECT substring_index(endpointascsv, ',', 3) AS 'Application',
count(ar.REQUESTORIGIN) AS 'Count'
FROM ars_requests ar
JOIN endpoints ep ON substring_index(endpointascsv, ',', 3)=ep.ENDPOINTNAME
WHERE ar.ENDPOINTASCSV not in ('Saviynt',
'Datasets',
'Organizations')
AND ENDPOINTASCSV like '%,%'
GROUP BY ar.ENDPOINTASCSV
UNION
SELECT substring_index(endpointascsv, ',', 4) AS 'Application',
count(ar.REQUESTORIGIN) AS 'Count'
FROM ars_requests ar
JOIN endpoints ep ON substring_index(endpointascsv, ',', 4)=ep.ENDPOINTNAME
WHERE ar.ENDPOINTASCSV not in ('Saviynt',
'Datasets',
'Organizations')
AND ENDPOINTASCSV like '%,%'
GROUP BY ar.ENDPOINTASCSV
UNION
SELECT substring_index(endpointascsv, ',', -1) AS 'Application',
count(ar.REQUESTORIGIN) AS 'Count'
FROM ars_requests ar
JOIN endpoints ep ON substring_index(endpointascsv, ',', -1)=ep.ENDPOINTNAME
WHERE ar.ENDPOINTASCSV not in ('Saviynt',
'Datasets',
'Organizations')
AND ENDPOINTASCSV like '%,%'
GROUP BY ar.ENDPOINTASCSV


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

Siva
Regular Contributor
Regular Contributor

Thanks the info
Its not giving the right count if its requested for one application giving an extra count  even

Siva
Regular Contributor
Regular Contributor

Hi Rushi
I have tried with 8 as well, But still it showing the same count,
Its showing the same count even if i Put till 4.

Could you please help me here

Thanks
Siva Avula

Siva
Regular Contributor
Regular Contributor

Issue  Is ressolved , pls check the below query

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 and e.endpointname not in ('saviynt','Organizations','Datasets') group by e.endpointname
ORDER BY count(e.endpointname) DESC limit 20