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:
1. 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;
2. 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 statsdata6_17_2006 where pollid=413;
3. The data archiving is something like this:
All the collected raw data is in a table called STATSDATA<MM-dd-YY>. 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 icremented 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 Admin-->Database Maintenance option.