How to correlate the dashboard view's Top CPU/Memory Utilisation's to the data in Vertica

book

Article ID: 38704

calendar_today

Updated On:

Products

CA Infrastructure Management CA Infrastructure Management CA Performance Management - Usage and Administration CA Performance Management - Data Polling

Issue/Introduction

Introduction:

When data appears on the CAPC dashboard that does not look valid, the raw data can be pulled from the Data Repository to verify the values. 

Question:

How can CPU or memory utilisation data be extracted from the Data Repository DB?

Environment:  

CA Performance Center 2.4.0 and above.

Answer: 

Here are the steps to find out what is the exact Vertica SQL select statement for the Top CPU/Memory Utilization dashboard in CAPC.

1. In CAPC, create one dashboard, for example Top CPU/Memory Utilization (Gauge/Table) for devices.

2. Once the dashboard has been created and shows the graph and table, login to the Data Repository (DR) Vertica Linux machine.

3. Go to the Vertica database's catalog directory, for example:

$cd /catalog/da_dr/v_da_dr_node0002_catalog

4. Run this command to identify the select statement used;

tail -f vertica.log | grep -i select

5. Go back to CAPC, rerun the Top CPU/Memory Utilization dashboard that just been created.

6. Check the output from step 4 and look for the SELECT v1."xxx ..." statement until DESC.

For example, the output will be similar to;

"

2016-02-06 19:55:33.017 Init Session:0x7f78a801cb80 [Session] <INFO> [PQuery] TX:0(619.xxx.com-52312:0x721b3) SELECT v1.".PollItem.DeviceID",di.item_display_name ".PollItem.DevDisplayName",i.item_name ".Item.Name",v1.".Utilization.Avg",v1.".Utilization.Min",v1.".Utilization.Max" FROM (SELECT p4.device_item_id as ".PollItem.DeviceID", AVG(NRM_CPUSTATS.im_Utilization) as ".Utilization.Avg", MIN(NRM_CPUSTATS.min_im_Utilization) as ".Utilization.Min", MAX(NRM_CPUSTATS.max_im_Utilization) as ".Utilization.Max", NRM_CPUSTATS.item_id as ".ID" FROM NRM_CPUSTATS_rate NRM_CPUSTATS INNER JOIN poll_item p4 ON (p4.item_id = NRM_CPUSTATS.item_id) AND p4.is_filtered=0 INNER JOIN item di ON (di.item_id = p4.device_item_id) INNER JOIN ( SELECT distinct device_item_id, item_id from poll_item p3 inner join (SELECT distinct dgRW.member_item_id from v_group_membership dgRW WHERE ((dgRW.group_item_id) IN ((1221),(1394),(1400),(1471),(1472),(1470),(1466),(1442),(1224),(1426),(1223),(1427),(1428),(1429),(1430),(1431),(1432),(1232),(1231),(1230),(1229),(1228),(1227),(1226),(1225),(1239),(1240),(1291),(1237),(1238),(1235),(1474),(1236),(1473),(1233),(1234),(1245),(1243),(1244),(1241),(1242)))) dgRW ON (dgRW.member_item_id = p3.device_item_id AND p3.is_filtered=0) ) p3 ON (p3.item_id = NRM_CPUSTATS.item_id) WHERE (NRM_CPUSTATS.tstamp > 1407365400 AND NRM_CPUSTATS.tstamp <= 1407369300) GROUP BY NRM_CPUSTATS.item_id, p4.device_item_id ORDER BY AVG(NRM_CPUSTATS.im_Utilization) IS NULL ASC, AVG(NRM_CPUSTATS.im_Utilization) DESC LIMIT 10) v1 INNER JOIN poll_item p4 ON (p4.item_id = v1.".ID") AND p4.is_filtered=0 INNER JOIN item di ON (di.item_id = v1.".PollItem.DeviceID") INNER JOIN item i ON (i.item_id = v1.".ID") ORDER BY v1.".Utilization.Avg" IS NULL ASC, v1.".Utilization.Avg" DESC

"

7. Copy the SQL select statement from "SELECT" until "DESC", see below as an example;

SELECT v1.".PollItem.DeviceID",di.item_display_name ".PollItem.DevDisplayName",i.item_name ".Item.Name",v1.".Utilization.Avg",v1.".Utilization.Min",v1.".Utilization.Max" FROM (SELECT p4.device_item_id as ".PollItem.DeviceID", AVG(NRM_CPUSTATS.im_Utilization) as ".Utilization.Avg", MIN(NRM_CPUSTATS.min_im_Utilization) as ".Utilization.Min", MAX(NRM_CPUSTATS.max_im_Utilization) as ".Utilization.Max", NRM_CPUSTATS.item_id as ".ID" FROM NRM_CPUSTATS_rate NRM_CPUSTATS INNER JOIN poll_item p4 ON (p4.item_id = NRM_CPUSTATS.item_id) AND p4.is_filtered=0 INNER JOIN item di ON (di.item_id = p4.device_item_id) INNER JOIN ( SELECT distinct device_item_id, item_id from poll_item p3 inner join (SELECT distinct dgRW.member_item_id from v_group_membership dgRW WHERE ((dgRW.group_item_id) IN ((1221),(1394),(1400),(1471),(1472),(1470),(1466),(1442),(1224),(1426),(1223),(1427),(1428),(1429),(1430),(1431),(1432),(1232),(1231),(1230),(1229),(1228),(1227),(1226),(1225),(1239),(1240),(1291),(1237),(1238),(1235),(1474),(1236),(1473),(1233),(1234),(1245),(1243),(1244),(1241),(1242)))) dgRW ON (dgRW.member_item_id = p3.device_item_id AND p3.is_filtered=0) ) p3 ON (p3.item_id = NRM_CPUSTATS.item_id) WHERE (NRM_CPUSTATS.tstamp > 1407365400 AND NRM_CPUSTATS.tstamp <= 1407369300) GROUP BY NRM_CPUSTATS.item_id, p4.device_item_id ORDER BY AVG(NRM_CPUSTATS.im_Utilization) IS NULL ASC, AVG(NRM_CPUSTATS.im_Utilization) DESC LIMIT 10) v1 INNER JOIN poll_item p4 ON (p4.item_id = v1.".ID") AND p4.is_filtered=0 INNER JOIN item di ON (di.item_id = v1.".PollItem.DeviceID") INNER JOIN item i ON (i.item_id = v1.".ID") ORDER BY v1.".Utilization.Avg" IS NULL ASC, v1.".Utilization.Avg" DESC

8. Log in to the Vertica database as the Vertica database user;

For example;

$ /opt/vertica/bin/vsql -U dradmin -w dbpassword

Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands

       \g or terminate with semicolon to execute query

       \q to quit

dradmin=>

9. Copy Step 7's sql statement and run it with ";" at the end.

dradmin=> SELECT v1.".PollItem.DeviceID",di.item_display_name ".PollItem.DevDisplayName",i.item_name ".Item.Name",v1.".Utilization.Avg",v1.".Utilization.Min",v1.".Utilization.Max" FROM (SELECT p4.device_item_id as ".PollItem.DeviceID", AVG(NRM_CPUSTATS.im_Utilization) as ".Utilization.Avg", MIN(NRM_CPUSTATS.min_im_Utilization) as ".Utilization.Min", MAX(NRM_CPUSTATS.max_im_Utilization) as ".Utilization.Max", NRM_CPUSTATS.item_id as ".ID" FROM NRM_CPUSTATS_rate NRM_CPUSTATS INNER JOIN poll_item p4 ON (p4.item_id = NRM_CPUSTATS.item_id) AND p4.is_filtered=0 INNER JOIN item di ON (di.item_id = p4.device_item_id) INNER JOIN ( SELECT distinct device_item_id, item_id from poll_item p3 inner join (SELECT distinct dgRW.member_item_id from v_group_membership dgRW WHERE ((dgRW.group_item_id) IN ((1221),(1394),(1400),(1471),(1472),(1470),(1466),(1442),(1224),(1426),(1223),(1427),(1428),(1429),(1430),(1431),(1432),(1232),(1231),(1230),(1229),(1228),(1227),(1226),(1225),(1239),(1240),(1291),(1237),(1238),(1235),(1474),(1236),(1473),(1233),(1234),(1245),(1243),(1244),(1241),(1242)))) dgRW ON (dgRW.member_item_id = p3.device_item_id AND p3.is_filtered=0) ) p3 ON (p3.item_id = NRM_CPUSTATS.item_id) WHERE (NRM_CPUSTATS.tstamp > 1407365400 AND NRM_CPUSTATS.tstamp <= 1407369300) GROUP BY NRM_CPUSTATS.item_id, p4.device_item_id ORDER BY AVG(NRM_CPUSTATS.im_Utilization) IS NULL ASC, AVG(NRM_CPUSTATS.im_Utilization) DESC LIMIT 10) v1 INNER JOIN poll_item p4 ON (p4.item_id = v1.".ID") AND p4.is_filtered=0 INNER JOIN item di ON (di.item_id = v1.".PollItem.DeviceID") INNER JOIN item i ON (i.item_id = v1.".ID") ORDER BY v1.".Utilization.Avg" IS NULL ASC, v1.".Utilization.Avg" DESC;

10. The output of the SQL select statement will be similar to the following;

 

 .PollItem.DeviceID |         .PollItem.DevDisplayName         | .Item.Name | .Utilization.Avg | .Utilization.Min | .Utilization.Max

--------------------+------------------------------------------+------------+------------------+------------------+------------------

               1915 | 615.xxx.com                   | CPU 2      | 23.2307692307692 |               20 |               31

               1915 | 615.xxx.com                   | CPU 1      | 22.0153846153846 |               18 |               30

                902 | 668:xxx.com                   | CPU 1      |  19              |               19 |               19

                733 | 619:xxx.com                   | CPU 2      | 12.4615384615385 |                2 |               40

                733 | 619:xxx.com                   | CPU 1      | 10.9076923076923 |                2 |               34

               1189 | 30e:xxx.com                  | CPU 1      | 6.87692307692308 |                4 |               16

                759 | 616:xxx.com                  | CPU 3      | 5.73846153846154 |                1 |               18

                759 | 616:xxx.com                  | CPU 2      | 5.73846153846154 |                1 |               19

                759 | 616:xxx.com                  | CPU 1      | 5.55384615384615 |                1 |               18

                759 | 616:xxx.com                  | CPU 4      | 5.26153846153846 |                1 |               18

(10 rows)

11. From CAPC's dashboard, export it to a .csv file. The file will have output similar to;

Report Period:    8:55:00 - 9:55:00 February 7 2016 Eastern Standard Time (New South Wales)           

View Title:Top CPU Utilization Gauge/Table    

Device Name        Name      Utilization - Average

615.xxx.comCPU 223.23%

615.xxx.comCPU 122.02%

668:xxx.comCPU 119%

619:xxx.comCPU 212.46%

619:xxx.comCPU 110.91%

30e:xxx.comCPU 16.88%

616.xxx.comCPU 35.74%

616.xxx.comCPU 25.74%

616.xxx.comCPU 15.55%

616.xxx.comCPU 45.26%

Filter Options :            

 

From step 10 and step 11, the user can identify those metrics values that are matched with each other.

 

Environment

Release: IMDAGG99000-2.5-Infrastructure Management-Data Aggregator
Component: