Performance Monitor Data Archiving
Which are the database tables to be queried for performance data?
Here are the details that might help you extract reports by connecting to the database:
- The resources polled for data collection are stored in the table polleddata, and each polled data is assigned an ID. The name of the device in which these resources are polled is stored in the AGENT field. The following query will list the resource name (which is the polled-data), the resource id, and the agent (device) name:
select name,id,agent from polleddata;
- The data collected on polling these resources is stored in the STATSDATA tables. The data is periodically archived for easy reporting and maintenance. The value of ID field in polleddata is stored as POLLID in the STATSDATA table. So, for a given device, the data collected for a particular resource can be retrieved using the following query assuming, the POLLID is 413: select * from STATSDATA3_5_2018 where pollid=413;
- The data archiving is something like this:
All the collected raw data is in a table called STATSDATA. Assume the data collection interval is 15 mins. So, in an hour, there are 4 values collected. Once every hour, these 4 values are \\averaged\' to one value in STATSDATA_HOURLY table. Every day, the values in the STATSDATA_HOURLY table are averaged to the STATSDATA_DAILY table.
The data in the raw table, i.e., STATSDATA table, is maintained for 7 days by default and can be incremented to a max of 15 days. In _HOURLY table, the data is maintained for 30 days by default, and can be incremented to 90 days max. Similarly, the data in the _DAILY table is maintained for 365 days, and can be pushed upto 3650 days max. This maintenance can be configured from Settings → General Settings → Database Maintenance option.
The data used for displaying performance graphs will be controlled by both the Database Maintenance Settings and the Graph Display Settings. Make sure the values provided in Graph Display Settings is lesser than the corresponding value in Database Maintenance settings to ensure graphs are displayed properly.
For example, if the Detailed raw data retention time is set to 7 days and the Detailed statistics retention limit in the Graph Display Settings is set to 8 days, there won't be enough data from the Detailed table for OpManager to display graphs and so the data from the Hourly table will be used instead.