Layer7 API Management

 View Only
  • 1.  How are the different audit mySQL tables linked to each other?

    Posted Nov 27, 2019 06:22 AM
    ​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 :)


  • 2.  RE: How are the different audit mySQL tables linked to each other?

    Broadcom Employee
    Posted Nov 27, 2019 05:12 PM
    Sounds like you're trying to find out the ER between audit tables, you can run mysql command,
    mysql> show create table <tablename>\G

    to find out the reference between tables. (your DBA should be helpful on this)

    Regards,
    Mark



  • 3.  RE: How are the different audit mySQL tables linked to each other?
    Best Answer

    Broadcom Employee
    Posted Nov 27, 2019 07:43 PM
      |   view attached
    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)
    ------------------------------

    Attachment(s)

    pdf
    Audit_Tables.pdf   24 KB 1 version


  • 4.  RE: How are the different audit mySQL tables linked to each other?

    Posted Nov 28, 2019 02:39 AM
    Edited by Stefan Klotz Nov 28, 2019 02:52 AM
    Hi Mark and Jay,
    many thanks for your very helpful answers, especially the one from Jay. It's one of the best and detailed answer I saw in the last years.
    At the moment I have one question left,​ are the "Audit Detail Codes" also stored in a mySQL-table or where are they located? I'm looking for the severity column (as described here), because I'm only interested in "message_id" with at least WARNING. And even more complicated, what about codes which are changed to another severity via the CWP "audit.setDetailLevel"?
    Thank you!

    Ciao Stefan :)


  • 5.  RE: How are the different audit mySQL tables linked to each other?

    Broadcom Employee
    Posted Nov 28, 2019 01:27 PM
    Hi Stefan,

      Unfortunately the audit detail information is not maintained in a table but is hardcoded in the Gateway, so I *suspect* you will need to copy it out of the documentation. Unfortunately I don't think that the tables in the docs are complete (i.e. they do not include all detail errors), so there *may* be a way to pull it out of the binaries. I'm not a java person so I don't know the details about how that would be done, but I'm the type who would likely hack at it to see if it is possible.

    Cheers!

    JayMac

    ------------------------------
    Jay MacDonald - Adoption Architect - Broadcom API Management (Layer 7)
    ------------------------------



  • 6.  RE: How are the different audit mySQL tables linked to each other?

    Posted Nov 29, 2019 03:25 AM
    Hello Stefan,
    as Jay mentioned (Hi Jay :)) there is a document with the list of codes, is it complete and exhaustive? I am not sure, but I like to think that it will encompass at least 95% of them

    https://techdocs.broadcom.com/content/broadcom/techdocs/us/en/ca-enterprise-software/layer7-api-management/api-gateway/9-4/audit-detail-codes.html
    I hope it helps
    Thanks

    ------------------------------
    Maurizio Garzelli
    APIIDA
    APIIDA Principal Consultant
    https://apiida.com
    ------------------------------