Custom report to extract DML with DPA

Hi,

I would like to extract the insert, delete and update queries that are performed against a single table of an Oracle database monitored via DPA.

I would like to schedule this to receive it once a day, and I want it to be dynamic. For this reason, the TopSQL report is not suitable because it uses hashes and would not record new queries performed during the day.


Could someone help me create a query to get this information directly from the MySQL repository so I can bypass DPA and program it in another way?


Thank you very much,
froana

Parents
  • Hi Froana, are you thinking to use this for some kind of auditing capability or change data capture capability? We employ a sampling algorithm (once per second) and can miss some less frequent, quick running DML (think like a statement that runs in 20ms between our polls). Just want to call out that DPA is not an auditing tool.

    Having said that, try this query against our SQL Text table as collected from your Oracle instance (run a select id, name from cond in your repo and use the ID of the Oracle instance to replace <ID> and swap out the real table name for <TABLE_NAME>:

    select H, ST from const_<ID> where (ST like '%DELETE%' or ST like '%INSERT%' or ST like '%UPDATE%') and ST like '%<TABLE_NAME>%' 

    It won't be the most efficient query, but if you only run it once a day, shouldn't be too much overhead. You will also want to add in the where clause a value for the D column (that will be the date you want to search on (like a subselect for curdate and make sure the formatting lines up with how we store the date).

Reply
  • Hi Froana, are you thinking to use this for some kind of auditing capability or change data capture capability? We employ a sampling algorithm (once per second) and can miss some less frequent, quick running DML (think like a statement that runs in 20ms between our polls). Just want to call out that DPA is not an auditing tool.

    Having said that, try this query against our SQL Text table as collected from your Oracle instance (run a select id, name from cond in your repo and use the ID of the Oracle instance to replace <ID> and swap out the real table name for <TABLE_NAME>:

    select H, ST from const_<ID> where (ST like '%DELETE%' or ST like '%INSERT%' or ST like '%UPDATE%') and ST like '%<TABLE_NAME>%' 

    It won't be the most efficient query, but if you only run it once a day, shouldn't be too much overhead. You will also want to add in the where clause a value for the D column (that will be the date you want to search on (like a subselect for curdate and make sure the formatting lines up with how we store the date).

Children
  • Hello Mandevil,

    Thank you very much for your help. I will definitely try this query.

    To answer your question, I will use it at this time as auditing tool. However, this is not a permanent solution, but temporary until we restart the database and enable unified audit. Since this instance is critical, we have to plan the restart long before and during this time, we will use what we already have, i.e. DPA, as auditing.

    Thanks again for your help and for the fast response!