[Perfparse-devel] Summary tables

[Tim W. <tim...@pi...>]

Greetings Programs!

I noticed you have a few tables in the PP db layout that are meant for
summarizing data. What are the plans here? 

I'm currently working on an 'availability' report. It is based on the raw
plugin data, i.e. it only looks at the nagios_status field in the _raw
table. Basically, I'm creating a report showing ALL hostgroups, hosts and
services with their resp. uptime (%) and downtime, similar to the summary on
the raw history report.

My first approach was to create a Mysql UDF (user-defined function), called
'status_time' that can be used in a 'group by' clause. The resulting SQL
then looks something like this:
select host_name, service_description,
status_time(unix_timestamp(ctime), nagios_status, 0) as UPTIME,
status_time(unix_timestamp(ctime), nagios_status, 1) as WARNTIME,
status_time(unix_timestamp(ctime), nagios_status, 2) as CRITICALTIME,
status_time(unix_timestamp(ctime), nagios_status, 3) as UNDEFTIME
from perfdata_service_raw where ctime > '2004-07-31' and ctime <
'2004-09-01' 
group by host_name, service_description order by ctime

This works, but performance is bad (just think about how many records need
to be processed for a few 100 services and 30 days of data!), and I am
relying on MySQL to give me the data in chronological order (so far it's
always been correct, but I'm not comfortable with it)

Since the data is not changing once it was entered, I thought of simplifying
by 'data-warehousing' the raw data. Using the query above, I summarize the
availability data every x time (x could be daily, weekly, monthly), and
store them in a table, e.g. perfdata_summary_raw. I'm planning on creating a
more elaborate script (in Perl, I could try C, but it's been a while) to do
this.

Q1: did you plan on making something similar? If so, is there any code I
could look at/use/improve ?
Q2: what should the summary_raw table look like? Here's my current proposal:
CREATE TABLE `perfdata_summary_availability` (
  `id` int(11) NOT NULL auto_increment,
  `period` varchar(10) NOT NULL default '',
  `host_name` varchar(75) NOT NULL default '',
  `service_description` varchar(75) NOT NULL default '',
  `sum_uptime` bigint(20) NOT NULL default '0',
  `sum_warntime` bigint(20) NOT NULL default '0',
  `sum_criticaltime` bigint(20) NOT NULL default '0',
  `sum_undeftime` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `perfdata_summary_availability_ix1`
(`period`,`host_name`,`service_description`),
  KEY `perfdata_summary_availability_ix0`
(`host_name`,`service_description`)
) TYPE=InnoDB;

Thanks for the input.

Tim