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()) + (rsh.STARTTIME/1000),'1970-01-01 00:00:00') 'State Change From date', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (rsh.ENDTIME/1000),'1970-01-01 00:00:00') 'State Change To Date',AUS.FIRST_NAME 'State Change Done by' FROM Resources res LEFT JOIN resourcestatehistory rsh ON rsh.resourceid=res.resourceid LEFT JOIN ResourceState rs1 ON rs1.resourcestateid=rsh.resourcestateid LEFT JOIN ResourceState rs2 ON rs2.resourcestateid=rsh.prevresourcestateid LEFT JOIN AaaUser aus ON aus.user_id=rsh.userid LEFT JOIN SystemInfo sysinfo ON sysinfo.workstationid=res.resourceid ORDER BY 1
New to ADSelfService Plus?
Related Articles
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) ...Query to show all asset details ( MSSQL )
Tested in build MSSQL (14306) SELECT MAX("resource"."RESOURCENAME") AS "Asset Name", MAX("resource"."ASSETTAG") AS "Asset Tag",MAX(resource.SERIALNO) AS "Org Serial Number", MAX("product"."COMPONENTNAME") AS "Product", ...Query to show Asset details with Warranty Expiry Date (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", productType.COMPONENTTYPENAME AS "Product Type", state.DISPLAYSTATE AS ...Query on current asset value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name1", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", ...Query to show workstations that has a scan status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: Below query that will show the Assets that has a scan status. select systeminfo.workstationname "Workstation Name", LONGTODATE(audithistory.audittime) "Last Scanned on", ...