Layer7 API Management

Expand all | Collapse all

Search string in gateway audit request(blob)

  • 1.  Search string in gateway audit request(blob)

    Posted Oct 19, 2018 02:56 AM

    Dear community,

     

    I would like to make a string search inside the request or response xml,  which are stored in audit_main table.

     

    However when I look up into the blob columns, all I can see is the following lines.

     

    Is there a way to convert or search inside this blob column?

     

     

    Kind regards.



  • 2.  Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Oct 19, 2018 05:41 AM

    Hello,

     

    I couldn't find such blob column in the "audit_main" table in the "ssg" database.

    Could you please tell us where did you find it?

     

    Best regards,

    Seiji



  • 3.  Re: Search string in gateway audit request(blob)

    Posted Oct 22, 2018 10:22 AM

    Dear Seiji_Moriyama,

     

    You can check the following screenshot for audit_main table.

     



  • 4.  Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Oct 19, 2018 10:37 PM

    It looks like you are looking at the audit_message table that has the 2 columns request_zipxml and response_zipxml stored in medium_blob format. The values stored in these tables are in a zipped format to conserve space and are unzipped when viewed in the Audit Event Viewer.

     

    You would need to extract the data and look to use the Encode/decode assertion (Encode/Decode Data Assertion - CA API Gateway - 9.2 - CA Technologies Documentation ) to unzip the contents.

     

    Sincerely,

     

    Stephen Hughes

    Director, CA Support



  • 5.  Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Oct 21, 2018 06:08 PM

    In addition to Stephen, if you are running sql query, not via a gateway policy, you can use uncompress() function,

    For example:

    mysql> select uncompress(response_zipxml) from audit_message where response_zipxml <> 'NULL' limit 1;



  • 6.  Re: Search string in gateway audit request(blob)

    Posted Oct 22, 2018 10:25 AM

    Dear Zhijun,

     

    • Unfortunately I do not have the audit_message table . 

            You can check the table details from the image I have posted above.

     

    • I only have audit_detail and audit_main tables

     

    Regards



  • 7.  Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Oct 22, 2018 12:16 PM

    From the screen capture, you appear to be using the Audit Sink policy writing to a database. The location may differ to the audit_main table as we only use 2 tables in this scenario. You should be able to use the uncompress function against the columns outlined in the screen capture.

     

    Sincerely,

     

    Stephen Hughes

    CA Support



  • 8.  Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Oct 22, 2018 06:34 PM

    I see. It seems you are using external database to store the audits, the schema is different with the internal mysql database, it only has 2 audit tables.

    If your external database is also mysql database, you can still use the uncompress fuction,

     

    select uncompress(request_xml), uncompress(response_xml) fromaudit_main;

     

    If your external database is not mysql database, you may need to check with your DBA or relevant document to see if there is similar function.

     

    Regards,

    Mark



  • 9.  Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Dec 10, 2018 03:41 PM

    Good afternoon,

     

    Were you able to resolve the issue? What was the final result?

     

    Sincerely,


    Stephen Hughes
    Broadcom Support



  • 10.  RE: Re: Search string in gateway audit request(blob)

    Posted Oct 28, 2023 12:26 PM

    Hi Stephen,

    We are using external database to store audits. We are trying to fetch associated logs but unable to find the same in audit database. Can you tell us where associated logs are stored in audit database.

    Thanks,

    B




  • 11.  RE: Re: Search string in gateway audit request(blob)

    Broadcom Employee
    Posted Oct 31, 2023 03:56 PM

    Hello @Bhimrao Raut,

    We don't document, recommend or support direct access to the gateway's configuration and audit database tables, and depending on your use case, there may be a better way that does not require such.

    However, the relationship between message type audit records and their associated logs can be seen here:



    Regards,



    ------------------------------
    Ben Urbanski
    Product Manager, API Gateway
    Layer7 API Management
    ------------------------------