We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Need table details for request history data.

randhir_kumar
New Contributor III
New Contributor III

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:

{
    "msg""Successful",
    "totalcount"1,
    "count"1,
    "errorCode""0",
    "requests": [
        {
            "requestid""583128",
            "reqkey""7663",
            "requesttype""Enterprise Role Request",
            "requestor""AMRUTA SAMBARE (amrutasambare)",
            "requestedfor""AMRUTA SAMBARE (amrutasambare)",
            "requestsubmittedon""2023-03-17 05:16:36",
            "duedate""2023-03-27 05:16:36",
            "status""Open ",
            "assignee""PRD USR12 (PRDUSR12)",
            "endpoints""",
            "endpointname""",
            "requestandtaskstatus"""
        }
    ]
}
API: {{url}}/ECM/{{path}}/fetchRequestHistoryDetails
Response:
{
    "requestHistoryDetails": {
        "comments""Created by WS Calls <br/>[03/17/23AMRUTA SAMBARE(amrutasambare)] test",
        "ManagerApproval": [
            {
                "Entitlement Type""",
                "Assignee""PRD USR12 (PRDUSR12)",
                "Start Date""2023-03-17 05:16:36",
                "Approve Date""",
                "Endpoint""",
                "State""Pending Approval",
                "Business Justification""Created by WS Calls ",
                "endpointname""",
                "Access""IG-13584_Role",
                "Request Type""Enterprise Role Request"
            }
        ],
        "dynamicattributes": [],
        "requestor""amrutasambare",
        "requestedfor""amrutasambare",
        "requestId""583128"
    },
    "msg""SUCCESS",
    "tabList": [
        "comments",
        "ManagerApproval",
        "dynamicattributes",
        "requestor",
        "requestedfor"
    ],
    "count"6,
    "errorCode""0"
}

Thanks,

Randhir Kumar

10 REPLIES 10

nimitdave
Saviynt Employee
Saviynt Employee

ARS_REQUESTS

REQUEST_ACCESS

ACCESS_APPROVERS

REQUEST_ACCESS_ATTRS

randhir_kumar
New Contributor III
New Contributor III

Hi,

We are not able to see ACCESS_APPROVERS and REQUEST_ACCESS_ATTRS tables in Saviynt UI.

Thanks.

Use analytics/Intelligence tab


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

randhir_kumar
New Contributor III
New Contributor III

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.

nimitdave
Saviynt Employee
Saviynt Employee

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`;

randhir_kumar
New Contributor III
New Contributor III

Hi,

Thank you sharing the query.

Need some more information like "taskType" mapping and 

"dynamicattributes": [ ]. related data
Could you please help me with table name from where we will get these data.
 
Thanks.

 

Please go through documentation https://docs.saviyntcloud.com/bundle/SSM-DB-Schema-Reference-v55x/page/Content/Identity-Repository-S...

ource Value Mapping Value Description

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.

Arstasks Status Mapping

Column Name Mapping Value Description

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.


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

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

dynamicattributes  we have request_access_attrs table but when we join this table based on request_access_key it is returning multiple attributes_name along with dynamicAttribute name but for the same requestKey from API it is returning only dynamicattributes like below:
"dynamicattributes": [
            {
                "name""dynTypeOfLeave",
                "value""OL"
            }
        ],
randhir_kumar_1-1681294540140.png

 


 

Kindly guide me on this.

Thanks.

Randhir Kumar.

nimitdave
Saviynt Employee
Saviynt Employee

tasttype will be from arstasks

dynamic attributes will be in request_access_attrs

nimitdave
Saviynt Employee
Saviynt Employee

try adding filter as attributelable as not null.