How to query DPA database to get same metrics as Perfstack through DPAIM

Hey Tony, the best way to get to this and other data would likely be a ticket with support. They will help you with the table structure and best places to pull this info (note, from your sample data, summarized might be the best and most efficient instead of detailed).

Like here is an example query against a SQL repo, but again, you may want to go after rolled up data. Custom Report - Metric Values

Thanks Robert!  I'll try this example out, and if no luck, I will reach out to support.  So far all they've done is give me an ERD - which is massive...

mandevil​, support just confirmed all they can do is give me the latest ERD.  A bit disturbing since I have been under the impression querying the DPA repository is supported as well as I would be able to get assistance in this area by SolarWinds.  But for now I'm working with the example you provided.

Ok, let me do some digging also - might pull a rabbit out...

Here is what I have so far, but it doesn't roll things up to the day like Perfstack does:

SET @instanceName := 'ServerName';
SET @metricName := 'Memory Utilization';
SET @startDate = CAST(DATE_ADD(CAST(NOW() AS DATE), INTERVAL -DAY(NOW()) + 1 DAY) AS DATETIME);
SET @endDate = CAST(LAST_DAY(@startDate) AS DATETIME);
SET @dbid := (SELECT ID FROM COND WHERE name = @instanceName);

SET @sql := CONCAT(
    'SELECT MN.NAME AS "Metric Name", MD.D AS "Timestamp", MD.V AS "Metric Value"',
    'FROM CON_METRICS_DETAIL_', @dbid, ' AS MD',
    '    INNER JOIN CON_METRICS_', @dbid, ' AS M',
    '        ON MD.METRICS_ID = M.ID',
    '    INNER JOIN CON_METRICS_NAMES_', @dbid, ' AS MN',
    '        ON M.METRIC_NAME_ID = MN.ID ',
    'WHERE MN.NAME = ''', @metricName, ''' ',
    'AND MD.D >= ''', @startDate, ''' ',
    'AND MD.D < ''', @endDate, ''' '
    'ORDER BY MD.D'
);

SELECT @sql;

​

Try this one out. It's goitng against the daily metrics rollup (so won't have today's data). The advantage is the max and avg are already computed and stored, so you can see both or just use the one you want to report on...

I haven't run this in test yet, so let me know if it errors out. I can test tomorrow.

SET @instanceName := 'PRD-SQL-03';
SET @metricName := 'Memory Utilization (%)';
SET @startDate = CAST(DATE_ADD(CAST(NOW() AS DATE), INTERVAL -DAY(NOW()) + 1 DAY) AS DATETIME);
SET @endDate = CAST(LAST_DAY(@startDate) AS DATETIME);
SET @dbid := (SELECT ID FROM dpa_remotedbarepo.cond WHERE name = @instanceName);

SET @sql := CONCAT(
    'SELECT MN.NAME AS "Metric Name", MD.D AS "Timestamp", MD.V_AVG AS "Metric Avg Value", MD.V_MAX AS "Metric Max Value"',
    'FROM dpa_remotedbarepo.CON_METRICS_DAY_', @dbid, ' AS MD',
    '    INNER JOIN dpa_remotedbarepo.CON_METRICS_', @dbid, ' AS M',
    '        ON MD.METRICS_ID = M.ID',
    '    INNER JOIN dpa_remotedbarepo.CON_METRICS_NAMES_', @dbid, ' AS MN',
    '        ON M.METRIC_NAME_ID = MN.ID ',
    'WHERE MN.NAME = ''', @metricName, ''' ',
    'AND MD.D >= ''', @startDate, ''' ',
    'AND MD.D < ''', @endDate, ''' '
    'ORDER BY MD.D'
);

SELECT @sql;

FYI Robert, that appears to yield the same results as what the Perfstack report exports out.  At least for the simple metrics... now I need to figure out what to query for something like the WAIT TYPES.

Thanks again!

Try a query against the CON_EVENT_SUM_<id> table. You can pretty it up, but something along these lines.

This would be to get daily/hourly wait times by wait type. Again, haven't tested, but let me know if this gets you close to what you are looking for.

set @instanceName := '<instance_name>';

set @dbid := (SELECT ID FROM dparepoowner.cond WHERE name = @instanceName);

select ces.datehour, ce.name, ces.timesecs

from con_event_sum_@dbid ces inner join conev_@dbid ce on ces.eventid = ce.id

where ces.period = 'D' -- for day, but hourly is available also using 'H'

and ces.datehour > -- insert date range or use GETDATE() - N logic with greater than or between or ...

Hey Robert, I know this is an old thread, but I am just now getting to this one.  When I use Orion to export out the metrics "Blocked SQL Statements", "Impact of Root Blockers", or any "Wait Time" report i.e. "...by Wait Type, Database, DB user, etc..." I get a csv with the columns "Entity", "Metric", "Date/Time", Value repeated 10 times, once for each of the top items in this category.  I'm not certain if using these tables will provide me with what I need.  I've been messing around with them and it doesn't seem like these tables will provide me that type of detail.  Am I missing something?  See screenshot attached for an idea.