Volume of Access Requests Not completed due to no approval provided

[AshirvadhN]

Hi Team,

I want to generate a report to fetch the access requests which are Not completed due to no approval provided.

I have used this query but it is giving me 0 records.

Can you please the query is corret or not.

SELECT u.employeetype, case when ra.requesttype='1' then 'Add Access' end as request_type, count(ra.requesttype) as total_re FROM access_approvers aa, users u, request_access ra, ars_requests ar WHERE ra.request_accesskey = aa.request_access_key AND ra.requestkey = ar.requestkey AND u.userkey = aa.approverkey AND aa.status = 1 and u.employeetype = 'BeSEE' and u.customproperty63 in ('BO','RO') and DATE(aa.submitdate) BETWEEN DATE(curdate())-4 and DATE(curdate()) group by ra.requesttype

[sudeshjaiswal · Accepted solution]

Hello @AshirvadhN ,

There seems to be some mistake with the join. 
Can you please try the below query,

SELECT u.employeetype, case when ra.requesttype='1' then 'Add Access' end as request_type, count(ra.requesttype) as total_re FROM access_approvers aa, users u, request_access ra, ars_requests ar WHERE ra.request_accesskey = aa.request_access_key AND ra.requestkey = ar.requestkey AND u.userkey = ar.requestor AND aa.status = 1 and u.employeetype = 'BeSEE' and u.customproperty63 in ('BO','RO') and DATE(aa.submitdate) BETWEEN DATE(curdate())-4 and DATE(curdate()) group by ra.requesttype

Thanks,

[AshirvadhN]

But still this query is giving 0 records

if i use interval 1000 days also

[sudeshjaiswal]

Hello @AshirvadhN ,

The Above query works fine,
SELECT u.employeetype, case when ra.requesttype='1' then 'Add Access' end as request_type, count(ra.requesttype) as total_re FROM access_approvers aa, users u, request_access ra, ars_requests ar WHERE ra.request_accesskey = aa.request_access_key AND ra.requestkey = ar.requestkey AND u.userkey = ar.requestor AND aa.status = 1 and DATE(aa.submitdate) BETWEEN DATE(curdate())-100 and DATE(curdate()) group by ra.requesttype
I just used the same query I have only removed the employee type and cp63 condition.

Thanks,

[AshirvadhN]

Hi Sudesh.

I tried the below query but still am getting 0 records.

This below queiry should give the  result as below , because in request history for today's date New Account awaiting for approval.

SELECT u.employeetype, case when ra.requesttype='1' then 'Add Access' end as request_type, count(ra.requesttype) as total_re FROM access_approvers aa, users u, request_access ra, ars_requests ar WHERE ra.request_accesskey = aa.request_access_key AND ra.requestkey = ar.requestkey AND u.userkey = ar.requestor AND aa.status = 1 and u.employeetype='US Vendor' and DATE(aa.submitdate)=CURDATE() group by ra.requesttype

[AshirvadhN]

Please find the screenshot below for the day i have new account task awaiting for the approval.

[sudeshjaiswal]

Hello @AshirvadhN ,

Please follow the below steps:

1. Create a new analytics with the following query and select the option to save to Elastic Search:
SELECT u.username, u.employeetype,
case when ar.requesttype='1' then 'Add Access'
when ar.requesttype = 3 then 'new account'
else ar.requesttype end as request_type,
count(ra.requesttype) as total_re
FROM access_approvers aa, users u, request_access ra, ars_requests ar
WHERE ra.request_accesskey = aa.request_access_key
AND ra.requestkey = ar.requestkey
AND u.userkey = ra.userkey
AND aa.status = 1
and u.employeetype is not null
and DATE(aa.submitdate) like '%2023-04-05%'
group by ra.requesttype
2.Perform a dry run to see if it returns any results.
3. If the dry run returns results, run the query and check the results in the run history.
4. If the dry run shows results but the actual run returns zero results, check the logs for any issues.

Note: Make sure that the condition “aa.status = 1” is included in the query to check if the request is in Open status. The status of that request in the request history should be Open.

Thanks,