Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

DPA tables and how to query DPA data

Hello guys, I have few questions related to DPA module. A DPA trial is installed and integrated with SAM.

From the DPA UI we can see the history of the configured Alarms - so in which tables this alert history is recorded? (its not shown in Orion.Alerthistory table)

Also for the Resources - how and which tables I need to query to get DPA metrics/statistics and are they availalble in time slices by 10 minutes for example.

Also I just found out that there is DPA API but seesm to be limited and this kind of information can not be retrieved from the API.

Appreciate all your comments and thoughts.

Thanks in advance

Find more posts tagged with

Accepted answers

All comments

cpriddy

The Orion DPA Integration Module (DPAIM) does not "collect" data from DPA Servers and store it redundantly in Orion. Instead, DPAIM interacts with the DPA Server(s) and retrieves data on-the-fly to present in Orion. This is true for the DPAIM specific dialogs showing wait time bar charts as well as DPA resource metrics you choose to present in PerfStack dashboards for instance. None of the raw DPA information is stored in Orion. The DPAIM just remote fetches the data from the DPA Servers instead of from the Orion DB repository.

In the Support-Utilities section of the DPA Options page, you can view all the REST APIs and execute them by in Swagger interface by clicking on the "Management API Documentation" link. The DPA repository schema is setup to enable storage of information it collects from each DB every second and for Data Warehouse type queries. It is not easy to query directly. You can view the repository schema architecture. But my suggestion is to go to the THWACK Product Forum for DPA and look for articles there related to the information you are trying to retrieve. Either reuse the queries or adjust similar ones. Some of the Custom Alert, Custom Metrics (against the repository), and Custom Report articles are useful to provide SQL statements.

KMSigma.SWI

There's a THWACK Tuesday Tip video about getting started with the API.

stefan.hristov

Thanks, I already got to know with the DPA API and seems good and interesting. Although I do not see any calls for gettig the Alert history records (only the configured alerts) and neither to retrieve metrics through the API.

stefan.hristov

so is that mean that the metrics shown in the DPA UI can not be retrieved from a DPA or Orion table? From the DPA API seems that there are no calls for getting any metrics and also alert history records.

KMSigma.SWI

If this data isn't already available in the API (I'm not the best authority on how deep the DPA API goes), then this sounds like something we'd want/need in our Feature Requests for DPA. First search in that space to see if a similar FR exists, but if not, provide some details on what you'd like to see in the product. Include screenshots, mock-ups, or anything you think would help the product development teams know more about the specificity of your request. More information and some best practices about Feature Requests on THWACK is in our documentation.

If you build an FR, be sure to come back here and post the link. I'd like to upvote it.

cpriddy

The DPA REST API is primarily around configuration and management automation, not really for data retrieval. No metric values can be retrieved from Orion repository tables. Metrics are stored and can be retrieved from tables in DPA. The tricky part is that the DPA schema is dynamic in that new tables are generated to handle metrics and you have to look up which table contains the metric you want. Review the DPA Database Metrics Architecture article for more details.

Searching inside the Database Management->Database Performance Analyzer Forum for "Custom Metric", "Custom Alert", or "Custom Report" along with other qualifying details of which metric or alert you want will usually turn up an article for or very close to what you are wanting. The queries are a bit complicated because they first have to look up the metric and see what table relates to that metric and second because the DPA repository is essentially a star schema data warehouse that requires you to join fact and dimension tables to return the information you want.

A good general purpose example that allows you to input a metric name and have it return information is "Custom Report - Metric Values" note the reply in that blog regarding custom metric names and how to convert the presentation custom metric name into the internally generated name "CustomMetric-#" needed to use in the query. The queries in these articles can be specific to the repository type (e.g., different syntax for Oracle vs. SQL Server DB repositories). Some of the blogs have multiple SQL options for different repo types. Others you may have to tweak the SQL to match your DPA Server's repository type.

Other example custom reports you may be interested in:

Example custom alerts you can create against metrics

Custom Alert - SQL Server Database Growth
Custom Alert - CPU Utilization
Custom Alert - Abnormal Wait Times
Custom Alert - Replication Alert based on UndistributedCommands count - note: This is an example pattern of leveraging 2 alerts, one to collect info into alert log, the second to query the alert log and generate alert notifications

Example custom metrics to capture and display graphs for

stefan.hristov

ok and thanks for the detailed info for the metrics. Do oyu know about the DPA Alerts and more precisely the history records for a specific alert - where they are stored and can they be fetched somehow?