SELECT DAuditNew.DataID, AuditStr, MAX(AuditDate) AS "MaxAuditDate"
-- The JOIN is needed to make the permissions filter (see below) work
-- LEFT OUTER JOIN is needed to still get Purge audit events
FROM DAuditNew LEFT OUTER JOIN DTreeCore DTree ON DAuditNew.DataID = DTree.DataID
WHERE AuditDate >= %1 -- Timestamp including seconds
AND DAuditNew.SubType IN %2 -- List configured in ContentTraversalProperties.typeIds
AND (
    -- Some event names must be split to avoid OTCS' filter of
    -- keywords which can modify the DB
    AuditStr IN ('AddHold',
                 'AddVersion',
                 'AssignCrossReferenceCode',
                 'AttrChange',
                 'CategoryRemoved',
                 'ClassificationAssigned',
                 'ClassificationRemoved',
                 'CLMRoleMembership',
                 'Cre'||'ate',
                 'De'||'lete',
                 'DelVersion',
                 'EmailMetadataChanged',
                 'EssentialChanged',
                 'MajorMinorDisable',
                 'MajorMinorEnable',
                 'Move',
                 'OwnerChange',
                 'ProjectMembership',
                 'RecordDateOrReceivedDateChanged',
                 'RemoveCrossReferenceCode',
                 'RemoveHold',
                 'Ren'||'ame',
                 'Reserve',
                 'Rest'||'ore',
                 'RMClassificationAdded',
                 'RMClassificationRemoved',
                 'scmClearanceChanged',
                 'scmSuppMarkInfoChanged',
                 'StatusChanged',
                 'Unreserve',
                 'XmlImport')
    OR AuditStr = 'PermChange' AND (
        -- Added or deleted permission
        Value1 IS NULL OR Value2 IS NULL
        -- Permission change removing or adding See (130 / 0x82 / bits 1, 7)
        -- or See Contents (36995 / 0x9083 / bits 0, 1, 7, 12, 15)
        OR BITAND(CAST(Value1 AS INTEGER DEFAULT NULL ON CONVERSION ERROR), 36995) = 36995 AND NOT BITAND(CAST(Value2 AS INTEGER DEFAULT NULL ON CONVERSION ERROR), 36995) = 36995
        OR NOT BITAND(CAST(Value1 AS INTEGER DEFAULT NULL ON CONVERSION ERROR), 36995) = 36995 AND BITAND(CAST(Value2 AS INTEGER DEFAULT NULL ON CONVERSION ERROR), 36995) = 36995))
AND (DTree.DataID IS NULL
    OR (
        -- ACL permissions filter for the connector user
        (%3)
        -- security clearance level permission filter for the connector user
        AND NOT EXISTS (SELECT dscl.DataID FROM RMSec_DocClearance dscl
            WHERE dscl.DataID = DTree.DataID AND dscl.CurrentSecurity >
                (SELECT uscl.Clearance FROM RMSec_Clearance uscl
                    WHERE uscl.UserID = %4))
        -- supplemental markings permission filter for the connector user
        AND NOT EXISTS (SELECT dsm.DataID FROM RMSec_DocSuppMark dsm
            WHERE dsm.DataID = DTree.DataID AND dsm.SuppMark NOT IN
                (SELECT usm.SuppMark FROM RMSec_UserSuppMarks usm
                    WHERE usm.UserID = %4))
    ))
GROUP BY DAuditNew.DataID, AuditStr
ORDER BY "MaxAuditDate" ASC