Query to find asset history

Query to find asset history

Use Case 

The below will provide a report on the updates made on an asset 

Query 

select resources.resourcename as "Asset Name" ,auditoperation.operationstring as "Operation Performed", cihistorydetails.attribute as "Altered Attribute",cihistorydetails.oldvalue as "Old value", cihistorydetails.newvalue as "New Value",  longtodate(cihistory.operationtime) as "Time of operation",ci2.ciname as "Technician" from resources inner join componentdefinition on resources.componentid=componentdefinition.componentid inner join componenttype on componentdefinition.componenttypeid=componenttype.componenttypeid inner join resourcetype on componenttype.resourcetypeid=resourcetype.resourcetypeid inner join ci on resources.ciid=ci.ciid inner join cihistory on ci.ciid=cihistory.ciid inner join cihistorydetails on cihistory.historyid=cihistorydetails.historyid inner join auditoperation on cihistorydetails.operation=auditoperation.operation inner join sduser on cihistory.userid=sduser.userid inner join ci as "ci2" on sduser.ciid=ci2.ciid inner join citype on ci2.citypeid=citype.typeid where (resourcetype.type='Asset' and citype.typename='Technician')
      • Related Articles

      • Query to show Asset state history

        MSSQL: 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 ...
      • How Assets data will be imported / overwritten ?

        While performing a scan in ServiceDesk Plus, the criteria that helps to identify the uniqueness of the workstations is in the following order, Agent ID ( only for Agent-based Scan) Service Tag Workstation Name MAC Address With the help of these ...
      • Query to find out who created Accounts

        Execute the below queries under  Reports->New  Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down the Org_IDs of the above query from the result ...
      • Query to show when the asset was placed in Repair state

        Use Case The query will pull the on what date and time an asset was placed on repair and the user who user who made the changes Sample Table  DB: MSSQL Query  For 10.5 and below SELECT "aaov"."NAME" AS "Site",  "workstation"."WORKSTATIONNAME" AS ...
      • Query on current asset value

        PGSQL: SELECT productType.COMPONENTTYPENAME AS "Product Type", resource.RESOURCENAME AS "Asset Name1", resource.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", LONGTODATE(resource.ACQUISITIONDATE) AS "Acquisition Date", aaov.NAME AS ...