Loading

×Sorry to interrupt
This page has an error. You might just need to refresh it.
[NoErrorObjectAvailable] Uncaught ReferenceError: addCTracking is not defined
Failing descriptor: {markup://c:KM_ArticleThemeLayout}

Refresh

Cookie Preference Center

General Information on cookies

When you visit our website, we use cookies to ensure that we give you the best experience. This information does not usually directly identify you, but it can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience on the site and the services we are able to offer. Further information can be found in our

Cookie Policy.

Strictly Necessary

Strictly Necessary

Always Active

Strictly Necessary

Strictly necessary cookies are always enabled since they are essential for our website to function. They enable core functionality such as security, network management, and website accessibility. You can set your browser to block or alert you about these cookies, but this may affect how the website functions. For more information please visit www.aboutcookies.org or www.allaboutcookies.org.

Cookie Details‎

Performance

Performance

Performance

Performance cookies are used to analyze the user experience to improve our website by collecting and reporting information on how you use it. They allow us to know which pages are the most and least popular, see how visitors move around the site, optimize our website and make it easier to navigate.

Cookie Details‎

Please confirm your settings by refreshing the page.

Functional

Functional

Functional

Functional cookies help us keep track of your past browsing choices so we can improve usability and customize your experience. These cookies enable the website to remember your preferred settings, language preferences, location and other customizable elements such as font or text size. If you do not allow these cookies, then some or all of these services may not function properly.

Cookie Details‎

Advertising

Advertising

Advertising

Advertising cookies are used to send you relevant advertising and promotional information. They may be set through our site by third parties to build a profile of your interests and show you relevant advertisements on other sites. These cookies do not directly store personal information, but their function is based on uniquely identifying your browser and internet device.

Cookie Details‎

Advertising Cookies

Consent Leg.Interest

Select All Vendors

Select All Vendors

Select All Hosts

Select All

Clear Filters

Information storage and access

Apply

Confirm My Choices Reject All Allow All

My Subscriptions

Loading

Knowledge Base

M&R | ViPR SRM | Watch4net - How are the tables linked together in an apg timeseries database? What is the purpose of each table in an apg timeseries database? (333870)

Last Updated: 5/29/2020Categories: TroubleshootingTotal Views: 4030Language:
English

Subscribe to get an email
newsletter with new updates

Please provide article feedback:

Symptoms

How are the tables linked together in an apg timeseries database?
What is the purpose of each table in an apg timeseries database?

Resolution

In an apg timeseries database there are many tables which are used to store the collected data values and the meta-data which describes that collected data.

1. data_variable table:

The data_variable table contains the variable identifier (id field), the external unique identifier of that variable (name field) and the storage group (group field) of every collected metric in a given database

mysql> describe data_variable;
+-------+--------------+------+-----+---------+
| Field | Type         | Null | Key | Default |
+-------+--------------+------+-----+---------+
| id    | bigint(20)   | NO   | PRI | NULL    |
| name  | varchar(255) | NO   | UNI | NULL    |
| group | char(10)     | NO   | MUL | NULL    |
+-------+--------------+------+-----+---------+

SQL example showing a row from data_variable containing the variable data of the ifInOctects metric for interface Gi0/0 on device dev-MPLSp7:

mysql> select * from data_variable where name like "%dev-MPLSp7%Gi0/0%ifInOctets%";
+------+---------------------------------------------------+-------+
| id   | name                                              | group |
+------+---------------------------------------------------+-------+
| 8096 | IF-dev-MPLSp7-GigabitEthernet0/0-Gi0/0-ifInOctets | group |
+------+---------------------------------------------------+-------+

2. data_property table

The data_property table contains the properties associated with each variable.
The variableid field corresponds to the id field of the data_variable table.
The name field contains the property name. The value field contains the property value

mysql> describe data_property;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| variableid | bigint(20)    | NO   | PRI | NULL    |       |
| name       | varchar(8)    | NO   | PRI | NULL    |       |
| value      | varchar(1024) | NO   | MUL | NULL    |       |
+------------+---------------+------+-----+---------+-------+

SQL example showing the properties associated with the above metric. Note the association between variableid and id fields:

mysql> select * from data_property where variableid in (select id from data_variable where name like "%dev-MPLSp7%Gi0/0%ifInOctets%") limit 14;
+------------+----------+---------------------------+
| variableid | name     | value                     |
+------------+----------+---------------------------+
|       8096 | ifaceip  | A.B.C.X                   |
|       8096 | model    | 3845                      |
|       8096 | samdev   | dev-MPLSp7                |
|       8096 | objid    | IF-dev-MPLSp7/5           |
|       8096 | datagrp  | SMARTS-PM                 |
|       8096 | location | lab1                      |
|       8096 | vendor   | CISCO                     |
|       8096 | ifindex  | 5                         |
|       8096 | contact  | [email-masked]            |
|       8096 | ifalias  | to dev-MPLSp8 fe-5/2/2.0  |
|       8096 | ip       | A.B.C.D                   |
|       8096 | deviceid | dev-MPLSp7                |
|       8096 | unit     | Octets/s                  |
+------------+----------+---------------------------+

3. Performance data (cache) tables:

These contain the actual data values for every collected metric, both raw and aggregated data.

The raw data is stored in the cache_<storage group>_0_<timestamp> tables
Eg
cache_group_0_1427695200

The raw tables contain the variable identifier (variable field) which corresponds to the id and variableid fields in the data_variable and data_property tables respectively along with the raw value (value field) of the metric and the timestamp at which it was collected from the source.

mysql> describe cache_group_0_1427695200;
+-----------+------------+------+-----+---------+
| Field     | Type       | Null | Key | Default |
+-----------+------------+------+-----+---------+
| variable  | bigint(20) | NO   | PRI | NULL    |
| timestamp | int(11)    | NO   | PRI | NULL    |
| value     | float      | NO   |     | NULL    |
+-----------+------------+------+-----+---------+

The aggregated data is stored in the cache_<storage group>_<aggregation period in seconds>_<timestamp>
Eg
cache_group_3600_1427673600      =>  hourly aggregated data
cache_group_86400_1427673600    =>  daily aggregated data
cache_group_604800_1393459200  =>   weekly aggregated data

The aggregated data tables again contain the variable identifier (variable field) which corresponds to the id and variableid fields in the data_variable and data_property tables respectively along with aggregate timestamp the average value, the minimum raw value, the maximum raw value, the sum, the last raw value, the number of raw values used to calculates these aggregates (nbvalues)  and the timestamp of the last raw value (lasttimestamp)

mysql> describe cache_group_3600_1427673600;
+---------------+------------+------+-----+---------+
| Field         | Type       | Null | Key | Default |
+---------------+------------+------+-----+---------+
| variable      | bigint(20) | NO   | PRI | NULL    |
| timestamp     | int(11)    | NO   | PRI | NULL    |
| average       | float      | NO   |     | NULL    |
| min           | float      | NO   |     | NULL    |
| max           | float      | NO   |     | NULL    |
| sum           | float      | NO   |     | NULL    |
| last          | float      | NO   |     | NULL    |
| nbvalues      | int(11)    | NO   |     | NULL    |
| lasttimestamp | int(11)    | NO   |     | NULL    |
+---------------+------------+------+-----+---------+

SQL example showing the raw values of the ifInOctects metric for interface Gi0/0 on device dev-MPLSp7 from one table:

select * from cache_group_0_1426723200 where variable in (select id from data_variable where name like "%dev-MPLSp7%Gi0/0%ifInOctets%");
+----------+------------+----------+
| variable | timestamp  | value    |
+----------+------------+----------+
|     8096 | 1426723525 |     0.25 |
|     8096 | 1426723765 |     0.25 |
|     8096 | 1426729765 |        0 |
|     8096 | 1426730245 | 0.248963 |
|     8096 | 1426732885 | 0.248963 |
|     8096 | 1426734085 |     0.25 |
|     8096 | 1426734325 |        0 |
|     8096 | 1426735285 |        0 |
|     8096 | 1426735525 |     0.25 |
|     8096 | 1426736725 |     0.25 |
|     8096 | 1426736965 |        0 |
+----------+------------+----------+

SQL example showing the aggregated values the ifInOctects metric for interface Gi0/0 on device dev-MPLSp7 from one table:

mysql> select * from cache_group_3600_1424692800 where variable in (select id from data_variable where name like "%dev-MPLSp7%Gi0/0%ifInOctets%");
+----------+------------+-----------+-----+------+----------+----------+----------+---------------+
| variable | timestamp  | average   | min | max  | sum      | last     | nbvalues | lasttimestamp |
+----------+------------+-----------+-----+------+----------+----------+----------+---------------+
|     8096 | 1424692800 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424696345 |
|     8096 | 1424696400 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424699945 |
|     8096 | 1424700000 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424703545 |
|     8096 | 1424703600 | 0.0582642 |   0 | 0.25 | 0.873963 |        0 |       15 |    1424707145 |
|     8096 | 1424707200 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424710745 |
|     8096 | 1424710800 | 0.0499308 |   0 | 0.25 | 0.748963 |        0 |       15 |    1424714345 |
|     8096 | 1424714400 | 0.0575116 |   0 | 0.25 | 0.862674 |        0 |       15 |    1424717945 |
|     8096 | 1424718000 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424721545 |
|     8096 | 1424721600 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424725145 |
|     8096 | 1424725200 |      0.05 |   0 | 0.25 |     0.75 |        0 |       15 |    1424728745 |
|     8096 | 1424728800 | 0.0499308 |   0 | 0.25 | 0.748963 |        0 |       15 |    1424732345 |
|     8096 | 1424732400 | 0.0499308 |   0 | 0.25 | 0.748963 |        0 |       15 |    1424735945 |
|     8096 | 1424736000 | 0.0833333 |   0 | 0.25 |     1.25 |     0.25 |       15 |    1424739545 |
|     8096 | 1424739600 | 0.0333333 |   0 | 0.25 |      0.5 |        0 |       15 |    1424743145 |
+----------+------------+-----------+-----+------+----------+----------+----------+---------------+

Again, note the accociation between id and variable fields from the data_variable and cache_group_x tables.

Detectable by VMware SkylineTM

Actions

Copy

link to clipboard
copied!

Print

Language:
English

Attachments

Additional Resources

No results for undefined

Ask The Community

Get answers quickly from VMware experts in the community

Post Subject

CONTINUE IN COMMUNITIES

Related Products:

Related Versions:

VMware Smart Assurance - Watch4Net/M&R

Take Our Survey

Support Assistant