Asset Management - report to find the last scanned time of assets and their scan status
SELECT resource.resourcename "Asset Name",
Max(net.ipaddress) "IP Address",
Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",
Longtodate(Max(audithistory.audittime)) "Last Scan Date",
Max(audithistory.auditstatus) "Audit Status",
Max(aaauser.first_name) "User" FROM resources resource
LEFT JOIN resourceowner rOwner
ON resource.resourceid = rOwner.resourceid
LEFT JOIN resourceassociation rToAsset
ON rOwner.resourceownerid = rToAsset.resourceownerid
LEFT JOIN sduser sdUser
ON rOwner.userid = sduser.userid
LEFT JOIN aaauser aaaUser
ON sduser.userid = aaauser.user_id
LEFT JOIN departmentdefinition deptDef
ON rOwner.deptid = deptDef.deptid
LEFT JOIN resourcelocation resLocation
ON resource.resourceid = resLocation.resourceid
LEFT JOIN networkinfo net
ON resource.resourceid = net.workstationid
LEFT JOIN lastauditinfo
ON resource.resourceid = lastauditinfo.workstationid
LEFT JOIN audithistory LASTSUCCESSAUDIT
ON lastauditinfo.last_success_auditid = LASTSUCCESSAUDIT.auditid
LEFT JOIN audithistory
ON lastauditinfo.last_auditid = audithistory.auditid
LEFT JOIN ComponentDefinition product
ON resource.COMPONENTID=product.COMPONENTID
LEFT JOIN ComponentType productType
ON product.COMPONENTTYPEID=productType.COMPONENTTYPEID
WHERE productType.COMPONENTTYPENAME !='software'
GROUP BY resource.resourcename
Click this link to navigate to the next report.