Working on a POC that would create a custom report that would query DPA data.
is there an ER diagram, or other info on how DPA stores data available to customers?
Working on a POC that would create a custom report that would query DPA data.
is there an ER diagram, or other info on how DPA stores data available to customers?
I made some notes in Confluence, let me see if I can cut and paste them here. This is not complete, but it was the tables I needed, so I am going to mark it as an answer.
In the event you need to query the DPA Repository, here are some notes that might be helpful.
Location
Database: dpa_repository
Schema: ignite (all tables are in this schema)
Table COND
This contains a list of monitored instances
The ID column is the instance ID.
Tables ending in _#
Each monitored instance has its own set of tables to store data.
All tables ending in _# (number) are instance data storage
In these notes, database instance storage will be referred to as _x
Table |
Notes |
---|---|
CON_STATS_TEN_MINUTE_x |
4 days of history with data broken down into 10 minute increments. Data includes SQLHASH, Physical Reads, Logical Reads, Row Count, Total Time in Seconds |
CON_STATS_SUM_x |
Long term history summarized by hour. Data includes SQLHASH, Physical Reads, Logical Reads, Row Count, Total Time in Seconds |
CON_STATS_DAY_SUM_x |
Query data summarized by day. SQLHASH, DATE, TIMESECS, EXECS |
CON_SQL_TEN_MINUTE_x |
4 days history of query wait time data broken down in 10 minute increments. Data includes SQLHASH, EVENTID, Time in Seconds. |
CON_SQL_SUM_x |
Long term history summarized by hour. Data includes SQLHASH, EVENTID, Time in Seconds. |
CONST_x |
Query text. Queries are broken up into 4000 character chunks and stored in this table. SQLHASH identifies the query, P is the sequence column for ordering the chunks. ST is the SQL Text. |
CONPR_x |
Client program names |
CONM_x |
Client host/machine names |
CONO_x |
Database Names |
CONU_x |
User names |
CONSPT_x |
XML plan data for each plan hash |
CONSPH_x |
Plan handle to Plan hash mapping |
CON_PLAN_SUM_x |
Time for each query plan broken down by hour, sqlhash. |
CON_PLAN_TEN_MINUTE_x |
Time for each query plan broken down by 10 minutes, limited to ~4 days |
CON_SQL_NAME |
Custom names given to SQL Queries. |
I made some notes in Confluence, let me see if I can cut and paste them here. This is not complete, but it was the tables I needed, so I am going to mark it as an answer.
In the event you need to query the DPA Repository, here are some notes that might be helpful.
Location
Database: dpa_repository
Schema: ignite (all tables are in this schema)
Table COND
This contains a list of monitored instances
The ID column is the instance ID.
Tables ending in _#
Each monitored instance has its own set of tables to store data.
All tables ending in _# (number) are instance data storage
In these notes, database instance storage will be referred to as _x
Table |
Notes |
---|---|
CON_STATS_TEN_MINUTE_x |
4 days of history with data broken down into 10 minute increments. Data includes SQLHASH, Physical Reads, Logical Reads, Row Count, Total Time in Seconds |
CON_STATS_SUM_x |
Long term history summarized by hour. Data includes SQLHASH, Physical Reads, Logical Reads, Row Count, Total Time in Seconds |
CON_STATS_DAY_SUM_x |
Query data summarized by day. SQLHASH, DATE, TIMESECS, EXECS |
CON_SQL_TEN_MINUTE_x |
4 days history of query wait time data broken down in 10 minute increments. Data includes SQLHASH, EVENTID, Time in Seconds. |
CON_SQL_SUM_x |
Long term history summarized by hour. Data includes SQLHASH, EVENTID, Time in Seconds. |
CONST_x |
Query text. Queries are broken up into 4000 character chunks and stored in this table. SQLHASH identifies the query, P is the sequence column for ordering the chunks. ST is the SQL Text. |
CONPR_x |
Client program names |
CONM_x |
Client host/machine names |
CONO_x |
Database Names |
CONU_x |
User names |
CONSPT_x |
XML plan data for each plan hash |
CONSPH_x |
Plan handle to Plan hash mapping |
CON_PLAN_SUM_x |
Time for each query plan broken down by hour, sqlhash. |
CON_PLAN_TEN_MINUTE_x |
Time for each query plan broken down by 10 minutes, limited to ~4 days |
CON_SQL_NAME |
Custom names given to SQL Queries. |
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.