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
New to ADSelfService Plus?
Related Articles
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 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 ...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 tickets first assigned today irrespective of created date
MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request Status", ...