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 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", ...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 ...Hide Asset
Use case If Technicians want to hide Asset field during template loading, then the same can be achieved using Execute Script option in Field and Form Rules. Solution Edit the incident/service template from which the asset needs to be hidden, choose ...