Query Report for Asset Change History
Dear Support,
I need report for asset change history for tracking of Asset Movement, I have report which only displays the first user and assigned date. I need the report for complete asset tracking from first user towards current user. (Ex: 1st assigned, 2nd assigned, 3rd assigned etc.)
Regards
Mooez
SELECT res.resourcename "Asset Name", ComponentDefinition.COMPONENTNAME "Product", (ComponentType.COMPONENTTYPENAME) AS "Product type", ( SELECT au.FIRST_NAME "Username" FROM ResourceOwnerHistory roh LEFT JOIN AaaUser au on roh.USERID = au.USER_ID WHERE roh.RESOURCEID = res.RESOURCEID and roh.STARTTIME = (SELECT min(ro.STARTTIME) FROM resourceownerhistory ro WHERE ro.USERID is not null and ro.resourceid = res.RESOURCEID )) "First User", (SELECT RTRIM(CAST(FROM_UNIXTIME(MIN(ro.STARTTIME) / 1000) AS NCHAR)) FROM resourceownerhistory ro WHERE ro.USERID is not null and ro.resourceid = res.RESOURCEID) "Assigned Date" FROM resources res
LEFT JOIN ComponentDefinition ON RES.COMPONENTID=ComponentDefinition.COMPONENTID
LEFT JOIN ComponentType ON ComponentDefinition.COMPONENTTYPEID=ComponentType.COMPONENTTYPEID
LEFT JOIN ResourceType ON ComponentType.RESOURCETYPEID = ResourceType.RESOURCETYPEID
WHERE ResourceType.TYPE NOT IN ('Software')
ORDER BY 1