Query to show asset first added date ( MSSQL )
Tested in Build MSSQL (14306)
When a new asset is added it will first set the state as In Store. So that is the time when asset is added and that will be shown in this query.
Go to Reports-New Query Reports and execute this query.
SELECT min(osinfo.OSNAME) AS "OS", min(osinfo.VERSION) AS "Version", min(systeminfo.LOGGEDUSER) AS "Last Logged In User", min(resUser.FIRST_NAME) AS "User", min(ci.CINAME) AS "CI Name", min(citype.TYPENAME) AS "CI Type", min(cires.ASSETTAG) AS "Asset Tag", min(cirest.DISPLAYSTATE) AS "Asset State", min(systeminfo.SERVICETAG) AS "Service Tag", min(systeminfo.MODEL) AS "Model", min(resDept.DEPTNAME) AS "Department",
dateadd(ss,convert(int,left(min(rsh.starttime),10)) ,'1970-01-01') "Asset added date" FROM WorkstationCI workstation
LEFT JOIN Resources cires ON workstation.CIID=cires.CIID
LEFT JOIN BaseElement baseci ON cires.CIID=baseci.CIID
LEFT JOIN CI ci ON baseci.CIID=ci.CIID
LEFT JOIN CIType citype ON ci.CITYPEID=citype.TYPEID
LEFT JOIN ResourceState cirest ON cires.RESOURCESTATEID=cirest.RESOURCESTATEID
LEFT JOIN ResourceOwner resOwn ON cires.RESOURCEID=resOwn.RESOURCEID
LEFT JOIN AaaUser resUser ON resOwn.USERID=resUser.USER_ID
LEFT JOIN DepartmentDefinition resDept ON resOwn.DEPTID=resDept.DEPTID
LEFT JOIN SystemInfo systeminfo ON cires.RESOURCEID=systeminfo.WORKSTATIONID
LEFT JOIN OsInfo osinfo ON cires.RESOURCEID=osinfo.WORKSTATIONID
INNER JOIN resourcestatehistory rsh on rsh.resourceid=cires.resourceid
WHERE (cires.CIID IS NOT NULL) GROUP BY systeminfo.WORKSTATIONID
The following query is applicable for versions above AE 7700 and SDP 15100
SELECT R.RESOURCENAME AS asset_name, R.ASSETTAG AS asset_tag, RS.DISPLAYSTATE AS asset_state, R.CREATEDTIME AS asset_created_time, OS.OSNAME AS os, OS.VERSION AS os_version, SI.LOGGEDUSER AS last_logged_in_user, CT.ComponentTypeName AS product_type, CD.COMPONENTNAME AS product, AU.FIRST_NAME AS user_name, D.DEPTNAME AS department, SI.SERVICETAG AS service_tag FROM Resources R LEFT JOIN SystemInfo SI ON R.RESOURCEID = SI.WORKSTATIONID LEFT JOIN OsInfo OS ON SI.WORKSTATIONID = OS.WORKSTATIONID LEFT JOIN ResourceState RS ON R.RESOURCESTATEID = RS.RESOURCESTATEID LEFT JOIN ResourceOwner RO ON R.RESOURCEID = RO.RESOURCEID LEFT JOIN AaaUser AU ON RO.USERID = AU.USER_ID LEFT JOIN DepartmentDefinition D ON RO.DEPTID = D.DEPTID LEFT JOIN ComponentDefinition CD ON R.COMPONENTID = CD.COMPONENTID LEFT JOIN ComponentType CT ON CD.COMPONENTTYPEID = CT.COMPONENTTYPEID
New to ADSelfService Plus?
Related Articles
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 Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...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 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 to show First response actioned by (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", aau.FIRST_NAME "Requester", ti.FIRST_NAME "Technician", LONGTODATE(wo.CREATEDTIME) "Created Time", LONGTODATE(wo.RESPONDEDTIME) "Responded Date", sdu2.firstname ...