Query report to get the successfully scanned assets (MSSQL & PGSQL)

Tested in builds from PGSQL (14300) or MSSQL (14306)

Query report to get the successfully scanned assets with audit status, state , Serial no and site.

SELECT max(resource.resourcename) "Asset Name",

    max(resource.SERIALNO) AS "Org Serial Number",
    max(state.DISPLAYSTATE) AS "Asset State",
    LongtoDate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date",
    (Max(audithistory.auditstatus)) "Aduit Status",
    max(aaov.NAME) AS "Site"
FROM
    SystemInfo workstation
    LEFT JOIN resources resource on workstation.workstationid = resource.resourceid
    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 ResourceState state ON resource.RESOURCESTATEID = state.RESOURCESTATEID
    LEFT JOIN ResourceLocation resLocation ON resource.RESOURCEID = resLocation.RESOURCEID
    LEFT JOIN SiteDefinition siteDef ON resLocation.SITEID = siteDef.SITEID
    LEFT JOIN SDOrganization aaov ON siteDef.SITEID = aaov.ORG_ID