Asset Ownership History Report
Please use the below query to get the report on Asset Ownership History.
SELECT r.resourcename AS "Workstation Name",
ct.componenttypename AS "Product type",
rs.displaystate AS "Asset State",
aa.first_name AS "Assigned User",
LONGTODATE(sh.starttime) AS "Changed Date",
usr.first_name AS "Changed By"
FROM resources r
LEFT JOIN resources res ON res.RESOURCEID = r.RESOURCEID
LEFT JOIN componentdefinition cd ON r.componentid = cd.componentid
LEFT JOIN componenttype ct ON cd.componenttypeid = ct.componenttypeid
LEFT JOIN resourcestatehistory sh ON r.resourceid = sh.resourceid
LEFT JOIN resourceownerhistory his ON sh.statehistoryid = his.statehistoryid
LEFT JOIN resourcestate rs ON sh.resourcestateid = rs.resourcestateid
LEFT JOIN aaauser aa ON his.userid = aa.user_id
LEFT JOIN aaauser usr ON sh.userid = usr.user_id
WHERE sh.prevresourcestateid IS NOT NULL and extract(epoch from(now()::TIMESTAMP - to_timestamp(sh.starttime/1000)::TIMESTAMP))/3600/24 < 90 ORDER BY 1,5 desc;
New to ADSelfService Plus?
Related Articles
Query to show Asset state history ( MSSQL)
Tested in MSSQL build (14306) SELECT res.resourcename 'Asset Name', res.assettag 'Asset Tag',res.serialno 'Asset Serial No.', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + ...Query to retrieve users login and logout history and find their current status
REQUIREMENT: To generate a report that contains the login and logout history of users and to determine the current user status. TESTED IN: Builds 14503 (Postgres) QUERY 1: The following query provides the login and logout history of users. SELECT ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Migrate Asset History from OP to OD as an Attachment.
Currently, there is no option to transfer asset history from the OP instance to the OD instance. As a workaround, a Python script retrieves history data, generates a readable PDF file, and uploads it as an attachment to the corresponding asset in the ...Changing the ownership of Report
In order to change the ownership of the existing Reports, please follow the below steps, 1. connect to your database. Incase, if your connecting database is postgresql, please refer the below link to connect to it. viz. ...