Query to find asset history
The below will provide a report on the updates made on an asset
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')
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 ...