I've attached a PDF I created a few years ago, before we switched from objectid to goid, of the relationships between the audit tables. Some field names have changed but the relationships are still the same so hopefully this gets the point across. For every audit that is triggered there will be an entry in audit_main. Depending on the type of audit (system, admin or message) there will be a corresponding entry in one of audit_admin, audit_message or audit_system with the same goid. Thus the relationship between those tables keys on goid. The query:
SELECT a.message, m.request_id
FROM audit_main a, audit_message m
WHERE a.goid=m.goid;
will illustrate this (as a silly example).
For every audit there *may* be audit details which are in the audit_detail table. Those have their own goid and map to the audit itself via the audit_goid field. The actual entry of any given audit detail is mapped as parameters which are maintained in the audit_detail_params table. If you look at Audit Detail Codes section in the "References -> Troubleshoot the Gateway" section of the documentation you'll see a list of most of the errors by code. That code corresponds to audit_detail.message_id. For each of those there is a number in {#} that corresponds to audit_detail_params.position. When displaying the audits the query reassembles the parameters in order. So, for example, if a 3017 audit is generated - "Policy evaluation for service {0} resulted in status {1} ({2})" - there will be three entries in audit_detail_params:
SELECT a.message, d.message_id, p.position, p.value
FROM audit_main a, audit_detail d, audit_detail_params p
WHERE d.goid=p.audit_detail_goid AND a.goid=d.audit_goid AND d.message_id=3017;
+----------------------------------------------------------------+------------+----------+--------------------------------------------------------------------+
| message | message_id | position | value |
+----------------------------------------------------------------+------------+----------+--------------------------------------------------------------------+
.
.
.
| Message was not processed: Authentication Failed (402) | 3017 | 0 | Gateway REST Management Service [18cfe0fcd0327d7624e23a828380ab88] |
| Message was not processed: Authentication Failed (402) | 3017 | 1 | 402 |
| Message was not processed: Authentication Failed (402) | 3017 | 2 | Authentication Failed |
+----------------------------------------------------------------+------------+----------+--------------------------------------------------------------------+
That audit detail would then reassemble as "Policy evaluation for service Gateway REST Management Service [18cfe0fcd0327d7624e23a828380ab88] resulted in status 402 (Authentication Failed)"
So, you had it mostly figured out it would seem. To accomplish what you are looking for will need an inner join between the four tables. I also think you will need to retrieve audit_detail.message_id (to know what the text is that wraps the parameters), audit_detail.ordinal (which represents the position of the detail in the sequence) and audit_detail_params.position (to know where to plug the parameter in to the string). Sadly, the text that maps to the message_id (i.e. what is in the Audit Codes of the documentation) is not easily available, so making sense of this becomes a challenge. The following sample SQL may get you most of the way there:
SELECT a.time, a.name,
m.request_id, m.response_status, m.routing_latency,
d.message_id, d.ordinal,
p.position, p.value
FROM audit_main a, audit_message m, audit_detail d, audit_detail_params p
WHERE m.goid=a.goid
AND d.audit_goid=a.goid
AND p.audit_detail_goid=d.goid;
------------------------------
Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
------------------------------
Original Message:
Sent: 11-27-2019 06:21 AM
From: Stefan Klotz
Subject: How are the different audit mySQL tables linked to each other?
For some mySQL-commands I need the information, how the different audit-tables are linked to each other.
As of now I need the following four tables:
- audit_detail
- audit_detail_params
- audit_main
- audit_message
From my understanding I identified the following mappings:
- audit_main.goid = audit_detail.audit_goid
- audit_detail_params.audit_detail_goid = audit_detail.goid
Can you please confirm if this is correct?
And what about the audit_message table? How is this linked to one of the other tables?
As a final result I'm interested in a SELECT-query with the following values:
- audit_main.time
- audit_main.name
- audit_detail_params.value
- audit_message.request_id
- audit_message.response_status
- audit_message.routing_latency
Any idea how I can achieve this?
Thank you!
Ciao Stefan :)