and more in a single search tool across platforms. Read the announcement here. |
03/28/2023 04:25 AM
Hi Team,
We want to fetch data related to request history and request history details using tables, that we get from fetchRequestHistory and fetchRequestHistoryDetails API. I found some tables like ars_requests and request_access but it doesn't contain some information.
We are expecting the data from table like the below APIs returning.
Please find below response structure of these two API:
API: {{url}}/ECM/{{path}}/fetchRequestHistory
Response:
Thanks,
Randhir Kumar
03/28/2023 04:32 AM
ARS_REQUESTS
REQUEST_ACCESS
ACCESS_APPROVERS
REQUEST_ACCESS_ATTRS
03/28/2023 11:30 PM
Hi,
We are not able to see ACCESS_APPROVERS and REQUEST_ACCESS_ATTRS tables in Saviynt UI.
Thanks.
03/29/2023 01:16 AM
Use analytics/Intelligence tab
04/02/2023 11:28 PM
Hi Rushikesh,
Yes, it's working with analytics/Intelligence tab.
Is there any table to fetch below highlighted columns?
{
"msg": "Successful",
"totalcount": 1,
"count": 1,
"errorCode": "0",
"requests": [
{
"requestid": "26482",
"reqkey": "270",
"requesttype": "Grant Access",
"requestor": "Dipali Sathe (dsathe)",
"requestedfor": "Abel Tutor (271103)",
"requestsubmittedon": "2022-09-06 09:10:57",
"duedate": "2022-09-16 09:10:57",
"status": "Completed ",
"assignee": "",
"endpoints": "ByodDS",
"endpointname": "ByodDS",
"requestandtaskstatus": "Request completed, Task pending"
}
]
}
Thanks.
04/05/2023 04:30 AM
Below query should help you to get the details:
select
`REQUEST ID` as 'Request ID',
`REQUEST TYPE` as 'Request Type',
`REQUEST SUBMIT DATE` as 'Request Submission Date',
`REQUESTED FOR` as 'Requested For',
`REQUESTEE NAME` as 'Request Name',
APPLICATION as 'Application',
`REQUESTED BY` as 'Requested By',
`APPROVAL TYPE` as 'Approval Type',
`ASSIGNEE ID` as 'Assignee ID',
`ASSIGNEE NAME` as 'Assignee Name',
case
when `RequestAccessStatus` = 3 then max(APPROVEDATE)
else null
end as `Approval Date`,
`REQUEST STATUS` as 'Request Status',
case
when `RequestAccessStatus` = 3 then max(APPROVEDATE)
else null
end `Request Completion Date`,
case
when `TASK STATUS` = 'Completed' then (taskupdatedate)
else null
end `Task Completion Date`,
`TASK STATUS` as 'Task Status'
from
(select
distinct SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, length(jbpmprocessinstanceid)) as 'REQUEST ID',
case
when (ar.requesttype = 1
or ar.requesttype = 3) then 'Grant Access'
when ar.requesttype = 2 then 'Revoke Access'
when ar.requesttype = 12 then 'Update Account'
when ar.requesttype = 11 then 'Emergency Access Request'
end as 'REQUEST TYPE',
ar.REQUESTDATE as 'REQUEST SUBMIT DATE',
u2.username as 'REQUESTED FOR',
CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME) as 'REQUESTEE NAME',
ENDPOINTASCSV as 'APPLICATION',
(
select
username
from
users t
where
t.userkey = ar.requestor) as 'REQUESTED BY',
aa.JBPM_ACTIVITY_NAME as 'APPROVAL TYPE',
u.username as 'ASSIGNEE ID',
CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) as 'ASSIGNEE NAME' ,
case
when aa.status = 1 then 'Pending Approval'
when aa.STATUS = 2 then 'Approved'
when aa.STATUS = 3 then 'Rejected'
when aa.status = 4 then 'Escalated'
when aa.STATUS = 6 then 'Discontinued'
end 'REQUEST STATUS',
ra.status as RequestAccessStatus,
aa.APPROVEDATE,
a.updatedate as taskupdatedate,
case
when a.status = 1 then 'Open'
when a.status = 2 then 'InProcess'
when a.status = 3 then 'Completed'
when a.status = 4 then 'Discontinued'
end as 'TASK STATUS'
from
ars_requests ar
inner join request_access ra on
ar.REQUESTKEY = ra.REQUESTKEY
inner join access_approvers aa on
ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
inner join users u on
aa.APPROVERKEY = u.USERKEY
inner join arstasks a on
ar.REQUESTKEY = a.REQUESTKEY
inner join users u2 on
ra.USERKEY = u2.USERKEY
order by
jbpmprocessinstanceid ,
JBPM_ACTIVITY_NAME ,
u.username) t1
group by
`REQUEST ID`,
`REQUEST TYPE`,
`REQUEST SUBMIT DATE`,
`REQUESTED FOR`,
`REQUESTEE NAME`,
`REQUESTED BY`,
`APPROVAL TYPE`,
`ASSIGNEE ID`,
`ASSIGNEE NAME`,
`TASK STATUS`;
04/06/2023 06:53 AM
Hi,
Thank you sharing the query.
Need some more information like "taskType" mapping and
04/11/2023 12:11 PM - edited 04/11/2023 12:12 PM
Please go through documentation https://docs.saviyntcloud.com/bundle/SSM-DB-Schema-Reference-v55x/page/Content/Identity-Repository-S...
ADD | 1 | Task created when the user requests for new access and the request approval is completed. |
REMOVE ACCESS | 2 | Task created when the user requests for removing access and the request approval is completed. |
NEWACCOUNT | 3 | Task created when the user requests for a new account and the request approval is completed. |
ROLE REQUEST | 4 | Task created when the user requests for a new role and the request approval is completed. |
CHANGEPASSWORD | 5 | Tasks created when the user requests for changing the account password, changing the account password for others, and changing the service account password and the request approval is completed. |
ENABLE ACCOUNT | 6 | Tasks created when the user request for enabling the account and the request approval is completed. |
PROPOSED ACCOUNT OWNERS | 7 |
|
DELETE ACCOUNT | 8 | Tasks created when the user request for deleting an account and the request approval is completed. |
UPDATE USER | 9 | Tasks created when the user request for updating the user information and the request approval is completed. |
UPDATE ACCOUNT | 12 | Tasks created when the user request for updating the account information and the request approval is completed. |
PROPOSED Entitlement OWNERS | 13 |
|
DISABLE ACCOUNT | 14 | Tasks created when the user request for disabling the account and the request approval is completed. |
MODIFY PRIVILEGE | 23 | Tasks created when the user request for modifying the existing privileges and the request approval is completed. |
CREATE ENTITLEMENT | 24 | Tasks created when the user request for creating entitlement and the request approval is completed. |
UPDATE ENTITLEMENT ACCESS ADD | 25 |
|
UPDATE ENTITLEMENT ACCESS ADD | 26 |
|
UPDATE ENTITLEMENT | 27 | Tasks created when the user request for updating the entitlement and the request approval is completed. |
DELETE ENTITLEMENT | 28 | Tasks created when the user request for deleting an entitlement and the request approval is completed. |
FIREFIGHTER ID GRANT ACCESS | 29 |
|
FIREFIGHTER ID REVOKE ACCESS | 30 | Tasks created when the user request for revoking the firefighter access and the request approval is completed. |
UPDATE ACCESS END DATE | 31 | Tasks created when the user request for updating the access end date and the request approval is completed. |
LOCK ACCOUNT | 32 | Tasks created when the user request for locking the account and the request approval is completed. |
UNLOCK ACCOUNT | 33 | Tasks created when the user request for unlocking the account and the request approval is completed. |
FIREFIGHTER INSTANCE GRANT ACCESS | 34 |
|
FIREFIGHTER INSTANCE REVOKE ACCESS | 35 |
|
FIREFIGHTER ACCESS ALERT | 36 |
|
CREATE ORGANIZATION | 37 | Tasks created when the user request for creating the organization and the request approval is completed. |
UPDATE ORGANIZATION | 38 | Tasks created when the user requests for updating the organization and the request approval is completed. |
NEW | 1 | The tasks created for the requests whose approvals are completed. |
IN PROGRESS | 2 |
|
COMPLETE | 3 | The tasks are successfully provisioned. |
DISCONTINUED | 4 | The tasks are discontinued |
PENDING CREATE | 5 |
|
PENDING PROVISIONING | 6 |
|
PROVISIONING FAILED | 7 |
|
ERROR | 8 | An error has occurred from the target system while provisioning the tasks. |
NO_ACTION_REQUIRED | 9 | The tasks require no action to complete the provision. |
04/12/2023 03:28 AM
Hi Rushikesh,
We are preparing an Analytics Query with respect to fetchRequestHistoryDetails API.
Query:
select taskkey,accountname,ownerkey,ownertype,(select username from users where userkey=task1.userkey) as user,case
when task1.tasktype = 1 then 'ADD'
when task1.tasktype = 2 then 'Remove Access / Account'
when task1.tasktype = 3 then 'NEW ACCOUNT'
when task1.tasktype = 4 then 'ROLE REQUEST'
when task1.tasktype = 5 then 'CREATE ROLE'
when task1.tasktype = 6 then 'MODIFY ROLE'
when task1.tasktype = 11 then 'UPDATE ACCOUNT'
when task1.tasktype = 17 then 'CREATE USER'
when task1.tasktype = 18 then 'UPDATE USER'
end as 'tasktype',task1.comments as comment1,requestaccesskey,(select endpointname from endpoints where endpointkey=task1.endpoint)endpoint,task1.startdate,task1.enddate,task1.updatedate,taskdate as creationDate,provisioningmetadata,provisioningcomments,case
when task1.status = 1 then 'New'
when task1.status = 2 then 'InProcess'
when task1.status = 3 then 'Completed'
when task1.status = 4 then 'Discontinued'
end as 'TASK STATUS',(select entitlement_value from entitlement_values where entitlement_valuekey=task1.entitlement_valuekey)entitlement_valuekey,(select entitlement_value from entitlement_values where entitlement_valuekey=task1.entitlement_valuekey) access,(select entitlementname from entitlement_types where entitlementtypekey in (select entitlementtypekey from entitlement_values where entitlement_valuekey=task1.entitlement_valuekey)) entitlementType,parenttask,(select displayname from securitysystems where systemkey=task1.securitysystem)securitysystem,upadteuser,ar.comments as reqComment,ar.requestdate,case
when (ar.requesttype = 1
or ar.requesttype = 3) then 'Grant Access'
when ar.requesttype = 2 then 'Revoke Access'
when ar.requesttype = 12 then 'Update Account'
when ar.requesttype = 11 then 'Emergency Access Request'
end as 'REQUEST TYPE',ar.status as reqStatus,u.username as 'ASSIGNEE ID',
CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) as 'ASSIGNEE NAME',aa.approvedate,aa.approvertype,(select username from users where userkey=aa.approverkey) as approver,(select username from users where userkey=aa.approveby) as approvedBy,aa.JBPM_ACTIVITY_NAME as 'APPROVAL TYPE',case
when aa.status = 1 then 'Pending Approval'
when aa.STATUS = 2 then 'Approved'
when aa.STATUS = 3 then 'Rejected'
when aa.status = 4 then 'Escalated'
when aa.STATUS = 6 then 'Discontinued'
end 'REQUEST STATUS',(select GROUP_CONCAT(raa.attribute_name SEPARATOR ',') from request_access_attrs raa where raa.request_access_key=aa.REQUEST_ACCESS_KEY) as attribute_name from arstasks task1 inner join ars_requests ar on task1.requestkey=ar.requestkey inner join request_access ra on
ar.REQUESTKEY = ra.REQUESTKEY
inner join access_approvers aa on
ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
inner join users u on
aa.APPROVERKEY = u.USERKEY where task1.requestkey IN (198)
But few columns are missing. Like for
Kindly guide me on this.
Thanks.
Randhir Kumar.
04/11/2023 10:03 AM
tasttype will be from arstasks
dynamic attributes will be in request_access_attrs
04/13/2023 01:07 AM
try adding filter as attributelable as not null.