How to query Transport Import History Transport Details?

[JKeyser]

We would like to design a Runtime Analytics report to query the Transport Details of previous Import requests. Specifically we would like to know which objects failed on a successful import. Is there a database table where we can find the information displayed in the Transport Details section of the request items found under Transport -> Import Package -> View History? 

[rushikeshvartak]

select a.access_url ,a.userlogin_accesskey as userLoginAccessKey,a.query_param as queryParam,a.objectKey as jobId,a.detail as jobComments,a.new_value as jobResponse from Userlogin_access a where a.access_url like '%/transport/exportData'
ORDER BY CASE a.new_value WHEN 'In Progress' THEN 1 WHEN 'In Queue' THEN 2 ELSE 3 END, a.userlogin_accesskey desc

[naveenss · Accepted solution]

Hi @JKeyser  below is the query run in the background when you click on "View History" from the Import package page

SELECT
raa.DESCRIPTION,
ra.REQUEST_ACCESSKEY,
ra.status as requeststatus,
ra.USERKEY,
ar.requestkey,
ar.status AS arsstatus,
ar.JBPMPROCESSINSTANCEID AS proccessinstanceID,
raa.ATTRIBUTELABLE AS savresponse
FROM
request_access_attrs raa
INNER JOIN
request_access ra ON ra.REQUEST_ACCESSKEY = raa.request_access_key
INNER JOIN
ars_requests ar ON ra.requestkey = ar.requestkey
WHERE
ra.status IN (1 , 2, 4)
AND ra.accesstype = 10
AND ra.requesttype = 26
AND ar.JBPMPROCESSINSTANCEID != ''
ORDER BY CASE raa.ATTRIBUTELABLE
WHEN 'In Progress' THEN 1
WHEN 'In Queue' THEN 2
ELSE 3
END , ra.startdate DESC;