Disposed Asset Report
Hello. We are trying to create a report that can run every month that shows what assets have been changed to disposed status. When we dispose of the assets we change the product type to a Non-IT category called "Discarded IT Equipment". We do this so the systems will not clutter up the main workstation list and will not get scanned. We also then change the Resource type to "Disposed"
The report would need to consist of:
Asset Name
Serial Number
Asset Tag number
Asset Notes
Date of Resource type change
Person who changed the type.
I had taken the code from another post, but it is pointing to the main resource section, not the Non-IT section.
SELECT res.resourcename 'Asset Name',res.resourcename 'Asset Name',res.assettag 'Asset Tag',res.serialno 'Asset Serial No.',sysinfo.model 'Product Name',sysinfo.servicetag 'Service Tag', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (roh.STARTTIME/1000),'1970-01-01 00:00:00') 'Owner From Date', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (roh.ENDTIME/1000),'1970-01-01 00:00:00') 'Owner To Date',auo.first_name 'Owner Change Done by', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + (rsh.STARTTIME/1000),'1970-01-01 00:00:00') 'State Change From date', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (rsh.ENDTIME/1000),'1970-01-01 00:00:00') 'State Change To Date',AUS.FIRST_NAME 'State Change Done by' FROM Resources res LEFT JOIN resourceownerhistory roh ON roh.resourceid=res.resourceid LEFT JOIN Aaauser au ON au.user_id=roh.userid LEFT JOIN DepartmentDefinition ddef ON ddef.DEPTID=roh.DEPTID LEFT JOIN resourcestatehistory rsh ON rsh.STATEHISTORYID=roh.STATEHISTORYID LEFT JOIN ResourceState rs1 ON rs1.resourcestateid=rsh.resourcestateid LEFT JOIN ResourceState rs2 ON rs2.resourcestateid=rsh.prevresourcestateid LEFT JOIN AaaUser aus ON aus.user_id=rsh.userid LEFT JOIN AaaUser auo ON auo.user_id=roh.taskuserid LEFT JOIN SystemInfo sysinfo ON sysinfo.workstationid=res.resourceid ORDER BY 1
This is something that I would like to get to automatically email our Finance Department.
Any help would be greatly appreciated.
The report would need to consist of:
Asset Name
Serial Number
Asset Tag number
Asset Notes
Date of Resource type change
Person who changed the type.
I had taken the code from another post, but it is pointing to the main resource section, not the Non-IT section.
SELECT res.resourcename 'Asset Name',res.resourcename 'Asset Name',res.assettag 'Asset Tag',res.serialno 'Asset Serial No.',sysinfo.model 'Product Name',sysinfo.servicetag 'Service Tag', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (roh.STARTTIME/1000),'1970-01-01 00:00:00') 'Owner From Date', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (roh.ENDTIME/1000),'1970-01-01 00:00:00') 'Owner To Date',auo.first_name 'Owner Change Done by', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + (rsh.STARTTIME/1000),'1970-01-01 00:00:00') 'State Change From date', DATEADD(s,DATEDIFF(s,GETUTCDATE() ,GETDATE()) + (rsh.ENDTIME/1000),'1970-01-01 00:00:00') 'State Change To Date',AUS.FIRST_NAME 'State Change Done by' FROM Resources res LEFT JOIN resourceownerhistory roh ON roh.resourceid=res.resourceid LEFT JOIN Aaauser au ON au.user_id=roh.userid LEFT JOIN DepartmentDefinition ddef ON ddef.DEPTID=roh.DEPTID LEFT JOIN resourcestatehistory rsh ON rsh.STATEHISTORYID=roh.STATEHISTORYID LEFT JOIN ResourceState rs1 ON rs1.resourcestateid=rsh.resourcestateid LEFT JOIN ResourceState rs2 ON rs2.resourcestateid=rsh.prevresourcestateid LEFT JOIN AaaUser aus ON aus.user_id=rsh.userid LEFT JOIN AaaUser auo ON auo.user_id=roh.taskuserid LEFT JOIN SystemInfo sysinfo ON sysinfo.workstationid=res.resourceid ORDER BY 1
This is something that I would like to get to automatically email our Finance Department.
Any help would be greatly appreciated.