automating annotation creation in DPA when objects are changed in SQL

I would like to have an annotation automatically created when objects (stored procedures, indexes, etc.) are created or changed.

Does anyone already have a script to do that? Or, just point me to the DPA table(s) I will need to insert, and I can do it myself.

Top Replies

  • tjk15​,

    The table is CON_EVENTS. The table does have a DBID column and you would want to make sure that you fill this in with the ID of the monitored instance you want to insert the annotation on.

    This would…

  • tjk15​,

    The table is CON_EVENTS. The table does have a DBID column and you would want to make sure that you fill this in with the ID of the monitored instance you want to insert the annotation on.

    This would be the ID from the COND table. "SELECT ID, NAME FROM COND;" will give you the ID of your  registered monitored instances.

    CON_EVENTS

    COLUMN

    ORACLE DATA TYPE

    SQL SERVER DATA TYPE

    NULLABLE

    DEFAULT VALUE

    PRIMARY KEY

    DESCRIPTION

    IDNUMBERBIGINTNOYesThe primary key ID. We will need a primary key generator.
    DBIDNUMBERSMALLINTNONoThe ID of the database that this event belongs to from the COND table.
    EVENT_DATEDATEDATETIMENONoThe time of the event down to the minute. This is the DPA server time.
    TITLENVARCHAR2(100)NVARCHAR(100)NONoThe title of the event.
    CREATORNVARCHAR2(50)NVARCHAR(50)YESNoThe user that created the annotation.
    DESCRIPTIONNVARCHAR2(2000)NVARCHAR(2000)YESNoA description of what was done.
  • Thanks for that info I have been searching for that table.

  • If you have already created a script to do this with the table needed.  It would be awesome if you could share. I was about to dig into it and create a script.

  • In order to create annotations automatically and from external scripts or processes, it is risky and you should avoid direct, manual updating of DPA repository tables via SQL. Instead, DPA supports automated annotation management via provided REST APIs created specifically for that purpose and include GET, POST, and DELETE actions.

    Example: When upgrading to new releases of software for an application using a database analyzed by DPA or upgrading the database's software version itself, it may be very useful to have your CI/CD or maintenance processes and scripts to add an annotation in DPA to track that point in time. Doing so allows you to readily see if sudden monitored metric value changes correlate closely in time to the software upgrade. 

    The DPA Administration guide provides a section "Automate tasks with the DPA REST API" that gives a general explanation of how to use and leverage the DPA REST APIs. In the Options page of the DPA browser UI, the "Management API Documentation" link within the Support->Utilities section not only further documents the DPA REST APIs but also provides an interactive Swagger interface for exploring the available API endpoints and try out API calls.  

    You can use scripts to call the API outside of the Swagger interface. See examples of Python and PowerShell scripts that call the DPA API.