Monitoring OTK Maintenance DB scripts using Logging

Products

• CA API Gateway

Issue/Introduction

We are working on OTK and wanted to know how we can see the what DB scripts are running/executing from the APIG server and how to print those logs in SSG or Audit logs.

Specifically we are looking for maintenance scripts on what scripts are executing and how many records are deleted in each run.

We have 5 non-clustered nodes and enabled in one of the node (let's say node1) to execute Scheduler Jobs to clean up DB and in all other 4 nodes jobs are in a disabled state.

If the node1 is not serving any incoming traffic, or for some other reasons the Scheduler configurations are disabled in node1, we want to make sure those scripts are enabled and running other nodes.

We have a global monitoring setup based on server syslogs, which will look for specific logs on the system and alerts required teams.

In our case, we want to print/push the OTK DB logs to syslogs, if the logs are not present then alerts us to check the system status.

 

Environment

Release : 10.0

Component : OTK INSTALLER

Resolution

The "OTK Database Maintenance" policy fragment are Read-Only but you could duplicate them and create your own custom policy to audit OTK queries as needed.

Example :
1. From Policy Manager , export the  "OTK Database Maintenance - id_token" and save it as "OTK_DB_Maintenance_Custom-id_token"
2. Create a new Policy fragment "OTK_DB_Maintenance_Custom-id_token" and customize the logging as your needs

  Note : see "Create a policy or policy fragment" , documentation link below for additional details

3. From Manage Scheduled Taks, You will need change the scheduled task "Policy Name" so it calls your custom policy.
this will require you customized code be tested to achieve the expected logging resuls

You could audit the number of rows deleted after the DELETE statement. 
Executing the DELETE statement would populate the <prefix>.queryresult.count with the affected rows count.

4. Customizing  the policy :

After the JDBC Query:  Add a new Audit Details like :

   Expired Tokens Count : ${jdbcQuery.queryresult.count} to print into log files
   All Entries Expired Count : ${jdbcQuery.queryresult.count}
   Expired Access Tokens Count : ${jdbcQuery.queryresult.count}

 

5. From Global Settings ->Manage Scheduled Tasks , modify the existing Scheduled tasks to run the new Policy Fragment created above

 

6. Do the same for each Policy Fragment do you need to audit , and Add a new Audit Details after each JDBC query you need to login  :

OTK Database Maintenance - Client  
OTK Database Maintenance - id_token
OTK Database Maintenance - Sessions

OTK Database Maintenance - Client  : use the following query
DELETE FROM oauth_client_key WHERE expiration > 0 AND expiration < ${now.seconds}

OTK Database Maintenance - id_token
DELETE FROM oauth_id_token WHERE expiration > 0 AND expiration < ${now.seconds}

OTK Database Maintenance - Sessions
DELETE FROM oauth_session WHERE expiration <= ${now.seconds}

OTK Database Maintenance - Token: use the following queries
DELETE FROM oauth_initiate

DELETE FROM oauth_token
                WHERE rtoken IS NOT NULL
                AND rexpiration < ${now.seconds}
                WHERE expiration < ${now.seconds}

DELETE FROM oauth_initiate
                WHERE expiration < ${now.seconds}

Additional Information

Create a policy or policy fragment