cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

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

Jump to solution

I have DPA 12.1.698 installed using MySQL 5.7.9 (hey, it's free - don't judge ).  I want to run queries directly against the DPA repository to yield the same results as Perfstack would.  Longer story but I am unable to leverage Perfstack reports due to PS being a bit inflexible in a few areas.  Aside from that, when I export a Perfstack report to CSV, I get something like the example below.  What would a MySQL query look like against the DPA repository?  I assume for other metrics it will be as simple as changing the metric name I'm looking for?

EntityMetricDate/TimeValue
SOMESERVER (DPASERVER)O/S Memory Utilization (%)4/30/201986
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/1/201986
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/2/201987
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/3/201987
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/4/201989
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/5/201986
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/6/201969
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/7/201947
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/8/201953
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/9/201960
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/10/201965
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/11/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/12/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/13/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/14/201990
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/16/201988
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/17/201988
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/18/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/19/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/20/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/21/201991
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/22/201989
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/23/201985
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/24/201986
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/25/201990
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/28/201957
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/29/201963
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/30/201967
SOMESERVER (DPASERVER)O/S Memory Utilization (%)5/31/201971
Tags (1)
0 Kudos
1 Solution

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;

View solution in original post

10 Replies
Level 14

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

0 Kudos

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.

0 Kudos

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

0 Kudos

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;

0 Kudos

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;

View solution in original post

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!

0 Kudos

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 ...

0 Kudos

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.example.png

0 Kudos

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...

0 Kudos
Level 14

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).

0 Kudos