and more in a single search tool across platforms. Read the announcement here. |
03/15/2023 06:28 AM
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
Solved! Go to Solution.
03/15/2023 06:50 AM
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.
04/05/2023 04:21 AM
Hi @nimitdave
Can you please provide any example if you have ?
That would help us a lot
Thanks
Siva
03/16/2023 08:51 AM
Hi Team,
Can someone see my above query is correct or not.
04/05/2023 04:27 AM
select count(REQUESTKEY) as 'Request Count',ENDPOINTASCSV as 'Application'
from ars_requests ar
group by ENDPOINTASCSV
order by count(REQUESTKEY) desc;
04/05/2023 07:04 PM
Hi
Can you try this query?
Thanks
Devang
04/09/2023 06:34 PM
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
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
04/10/2023 06:06 AM
The query looks correct to me and it gives me correct output in MySQL workbench.
Thanks
04/17/2023 12:29 AM
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
04/17/2023 03:10 PM
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
04/17/2023 04:54 AM
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
04/18/2023 08:53 PM
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
04/18/2023 09:05 PM
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
04/18/2023 09:14 PM
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
04/18/2023 09:30 PM
Thanks the info
Its not giving the right count if its requested for one application giving an extra count even
04/19/2023 12:39 AM
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
04/19/2023 10:53 PM
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