Layer7 API Management

Expand all | Collapse all

Search string in gateway audit request(blob)

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

    Posted 10-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)

    Posted 10-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 10-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)

    Posted 10-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)

    Posted 10-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 10-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)

    Posted 10-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)

    Posted 10-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)

    Posted 12-10-2018 03:41 PM

    Good afternoon,

     

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

     

    Sincerely,


    Stephen Hughes
    Broadcom Support