Asset Name Serial Number Service Tag OS Name Site Model Resource State Department State Change from date State Change To Date
MYSQL:
SELECT workstation.WORKSTATIONNAME 'Asset Name',resource.SERIALNO 'Serial Number',workstation.SERVICETAG 'Service Tag',osInfo.OSNAME 'OS Name',aao.NAME 'Site',workstation.MODEL 'Model',state.DISPLAYSTATE 'Resource State',deptDef.DEPTNAME 'Department',DATE_FORMAT(FROM_UNIXTIME(rsh.STARTTIME/1000),'%d-%m-%Y %k:%i')'State Change from date',DATE_FORMAT(FROM_UNIXTIME(rsh.ENDTIME/1000),'%d-%m-%Y %k:%i') 'State Change To Date' FROM SystemInfo workstation left join resourceownerhistory roh on roh.resourceid=workstation.workstationid LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID LEFT JOIN DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID LEFT JOIN SiteDefinition siteDef ON deptDef.SITEID=siteDef.SITEID LEFT JOIN AaaOrganization aao ON siteDef.REGIONID=aao.ORG_ID LEFT JOIN OsInfo osInfo ON workstation.WORKSTATIONID=osInfo.WORKSTATIONID 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 order by 1
MS-SQL:
SELECT workstation.WORKSTATIONNAME 'Asset Name',resource.SERIALNO 'Serial Number',workstation.SERVICETAG 'Service Tag',osInfo.OSNAME 'OS Name',aao.NAME 'Site',workstation.MODEL 'Model',state.DISPLAYSTATE 'Resource State',deptDef.DEPTNAME 'Department',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 from date' FROM SystemInfo workstation left join resourceownerhistory roh on roh.resourceid=workstation.workstationid LEFT JOIN Resources resource ON workstation.WORKSTATIONID=resource.RESOURCEID LEFT JOIN ResourceState state ON resource.RESOURCESTATEID=state.RESOURCESTATEID LEFT JOIN ResourceOwner rOwner ON resource.RESOURCEID=rOwner.RESOURCEID LEFT JOIN ResourceAssociation rToAsset ON rOwner.RESOURCEOWNERID=rToAsset.RESOURCEOWNERID LEFT JOIN DepartmentDefinition deptDef ON rOwner.DEPTID=deptDef.DEPTID LEFT JOIN SiteDefinition siteDef ON deptDef.SITEID=siteDef.SITEID LEFT JOIN AaaOrganization aao ON siteDef.REGIONID=aao.ORG_ID LEFT JOIN OsInfo osInfo ON workstation.WORKSTATIONID=osInfo.WORKSTATIONID 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 order by 1
regards,
Rachana