Query Audit event data in associated log tab

[Laurent LANDREAU]

Hello 

I need to troubleshoot an integration API that runs at a very high frequency.

One element written in the audit event logs appears as a string in the in the Associated Logs for the Audit Event entries.

How can I run a SQL query searching for that string? 

1) In which SQL table is that data stored ? In which SQL column?

2) Can that column be queried with a LIKE 'value%' statement?

Thank you for your response.

------------------------------
Laurent LANDREAU
Integration Architect, Brisbane, Australia
------------------------------

[oaygun]

Hi,

 this is the sql script for a service's last 1 hour audit logs(with associated logs) which we use may help,

we will be pleased for performance tips :)

SELECT ssg.goidToString(m.goid) goid_str,
         m.time,
         DATE_FORMAT(FROM_UNIXTIME(m.time / 1000), '%d.%m.%Y %H:%i:%s') AS 'time_formatted',
         service.routing_uri,
               msg.operation_name,
               m.name as full_service_name,
               service.name service_name,
                uncompress(msg.request_zipxml) as request_xml,
                 uncompress(msg.response_zipxml) as response_xml,
         msg.status,
         msg.response_status ,
         (select json_arrayagg(json_object ("pos", adp.position,"val",adp.value)) from audit_detail_params adp ,audit_detail ad where  ad.audit_goid=m.goid and adp.audit_detail_goid=ad.goid and adp.value is not null group by ad.audit_goid)
         FROM audit_main m
         LEFT JOIN audit_message msg ON m.goid = msg.goid
         LEFT JOIN published_service service ON service.goid = msg.service_goid
         WHERE  m.time > unix_timestamp(now() -Interval 1 Hour)*1000
         and service.routing_uri='/routing_uri_of_service'
         ORDER BY m.time DESC limit 100

[Laurent LANDREAU]

Thanks - I will give that a go, and provide some feedback.

[Bhimrao Raut]

Hello All,

Facing a similar kind of challenge in fetching the associated logs for a particular hit from the database directly.

We have a external audit database configured which has audit_main & audit_detail wherein audits are stored, we need to fetch the associated logs for a particular hit from the DB directly but unable to find where those are getting stored.

As per the above shared query seems there is some linkage of audit_detail_params,audit_message tables while viewing the audits from audit viewer but in external audit sink & lookup policies we don't see any other tables than audit_main & audit_detail in the insert & select queries in perform jdbc assertions.

Note : We are having huge amount of data near about 10TB and searching via policy manager takes a lot of time & sometimes gets timed out and no results are returned, hence we are looking a way around where in we can directly query the database and see the same kind of data that we get to see from the audit viewer