Volume of Access requests not completed due to rejection

[AshirvadhN]

Hi Team,

Any of you generated these type of report.

Am selecting the user who has access already and in request access for other tile am selecting that user and for the existing account am doing the modify account.

For this user am adding new access and removing existing access.

In this screenshot we have one add access and one remove access.

Next am going to request approval and rejecting the both add access an remove access.

I want to fetch these rejected requests. am using this query but getting 0 record.

SELECT distinct u.employeetype,
case when u.employeetype='BeSEE' and u.customproperty63='RO' then 'Romania'
when u.employeetype='BeSEE' and u.customproperty63='BE' then 'Belgium'
when u.employeetype='BeSEE' and u.customproperty63='GR' then 'Greece'
when u.employeetype='BeSEE' and u.customproperty63='RS' then 'Serbia'
when u.employeetype='US Vendor' then null
when u.employeetype='DA Vendor' then null
else u.customproperty63
end as Region,
case when ar.REQUESTTYPE='1' then 'Add Access'
when ar.REQUESTTYPE='2' then 'Remove Access'
else ar.REQUESTTYPE
end as request_type,
count(distinct (substring_index(ar.JBPMPROCESSINSTANCEID,'.',-1))) as count

FROM access_approvers aa INNER JOIN request_access ra ON aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY
inner join ars_requests ar on ra.requestkey = ar.requestkey inner join users u on u.userkey = ra.userkey

where ((u.employeetype='BeSEE'and u.customproperty63 in ('RO','BE','GR','RS')) OR (U.EMPLOYEETYPE IN ('DA Vendor','US Vendor'))) AND aa.status = 3 AND ar.requesttype in ('1','2','3','12') and DATE(aa.approvedate) = DATE(CURDATE()) group by u.employeetype,Region,request_type

[nimitdave]

I can see that correct tables are used.

Correct status is also checked. May be the where clause conditions are not matching. I tried to remove where clause and kept only status=3 and go the correct response. Below query i used.

SELECT distinct u.employeetype,
case when u.employeetype='BeSEE' and u.customproperty63='RO' then 'Romania'
when u.employeetype='BeSEE' and u.customproperty63='BE' then 'Belgium'
when u.employeetype='BeSEE' and u.customproperty63='GR' then 'Greece'
when u.employeetype='BeSEE' and u.customproperty63='RS' then 'Serbia'
when u.employeetype='US Vendor' then null
when u.employeetype='DA Vendor' then null
else u.customproperty63
end as Region,
case when ar.REQUESTTYPE='1' then 'Add Access'
when ar.REQUESTTYPE='2' then 'Remove Access'
else ar.REQUESTTYPE
end as request_type,
count(distinct (substring_index(ar.JBPMPROCESSINSTANCEID,'.',-1))) as count
FROM access_approvers aa INNER JOIN request_access ra ON aa.REQUEST_ACCESS_KEY = ra.REQUEST_ACCESSKEY
inner join ars_requests ar on ra.requestkey = ar.requestkey inner join users u on u.userkey = ra.userkey
where
-- ((
-- u.employeetype='BeSEE'
-- and u.customproperty63 in ('RO','BE','GR','RS')) OR (U.EMPLOYEETYPE IN ('DA Vendor','US Vendor'))) AND
aa.status = 3
-- AND ar.requesttype in ('1','2','3','12') and DATE(aa.approvedate) = DATE(CURDATE())
group by u.employeetype,Region,request_type;