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()) + ...MSP - Remote Asset Explorer Compatible Versions
Remote Asset Explorer-MSP Compatible Version From MSP 14000 - Compatible RAE version 6970-6985 From MSP 13000 - Compatible AE version (6900-6979). But RAE - DC integration, - If 6900,6901 RAE is used, then latest DC will not work. Bundled DC in RAE ...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 ...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. ...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", ...