以下SQL为查询上月之前扫描失败的服务器资产信息报表，可以根据实际需求再做内容调整。

 

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) "Aduit 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 where  LASTSUCCESSAUDIT.AUDITTIME<=<to_lastmonth>  and AuditHistory.AUDITSTATUS = 'FAILED' GROUP BY resource.RESOURCENAME