Report on how long an asset is in the field and by which user?

 
Author
Message
SysAider
7
 
Hi

I was wondering if there is a report (or how I can create a report) where I can track (during a selected start and end date) how long an asset has been assigned to an incident.

Kind regards
SysAider
7
 
For those who have a similar issue. This is how I retrieved my report at the end of the day. Only thing I couldnt retrieve which I still want is the serial number.

WITH ##assetreport
AS (
SELECT srh.id AS 'ServiceRequestNumber',
srh.request_user AS 'RequestUser',
srh.title AS 'Title',
cv.value_caption AS '_Status',
branchtype.value_caption AS 'Location',
responsibility AS 'Assignedto',
srh.update_time AS 'Updated',
asset.computer_name AS 'Asset',
LEAD(srh.id) OVER (
ORDER BY srh.id,
srh.update_time
) AS NextSRNumber
FROM service_req_history srh
JOIN (
SELECT value_key,
value_caption
FROM cust_values
WHERE list_name = 'status' AND account_id = 'archsoftware'
) cv ON cv.value_key = srh.[status]
JOIN (
SELECT value_key,
value_caption
FROM cust_values
WHERE list_name = 'location' AND account_id = 'archsoftware'
) branchtype ON branchtype.value_key = srh.location
LEFT JOIN computer asset ON asset.computer_id = srh.computer_id
WHERE srh.id IN (
SELECT srh.id
FROM service_req_history srh
WHERE srh.computer_id <> 'none' AND srh.computer_id <> ''
)
)
SELECT ##assetreport.Servicerequestnumber,
NextSRNumber,
Requestuser,
title,
_status,
Location,
Assignedto,
##assetreport.Asset,
Updated,
t.AssetDispatch,
CASE
WHEN _status NOT IN ('Follow-up', 'Verified Closed', 'Deleted')
THEN datediff(dd, t.assetdispatch, getdate())
WHEN _status IN ('Follow-up', 'Verified Closed', 'Deleted')
THEN datediff(dd, t.assetdispatch, updated)
END AS 'Days Dispatched'
FROM ##assetreport
JOIN (
SELECT servicerequestnumber,
Asset,
min(updated) AS 'AssetDispatch'
FROM ##assetreport
WHERE asset IS NOT NULL OR asset <> ''
GROUP BY servicerequestnumber,
asset
) t ON ##assetreport.servicerequestnumber = t.servicerequestnumber
WHERE CASE
WHEN NextSRNumber IS NULL
THEN '0'
ELSE NextSRNumber
END <> ##assetreport.servicerequestnumber AND updated BETWEEN @startdate
AND @enddate OR CASE
WHEN NextSRNumber IS NULL
THEN '0'
ELSE NextSRNumber
END <> ##assetreport.servicerequestnumber AND t.AssetDispatch BETWEEN @startdate
AND @enddate
GROUP BY ##assetreport.servicerequestnumber,
NextSRNumber,
requestuser,
_STATUS,
title,
location,
assignedto,
##assetreport.updated,
##assetreport.asset,
t.AssetDispatch
ORDER BY ##assetreport.servicerequestnumber,
##assetreport.updated
SysAid Product Manager Community Manager
5260
 
Hi Chris,

Glad to see you managed to pull the information from the database. I can suggest looking for the serial in the computer_attributes table.

Cheers,
Danny
SysAider
7
 
You sir deserve coffee waiting for you every morning on your desk! Thank you so much