I have a need to output a recordset that shows the record with the higest value for severity within a date range. That is easy enough, but, in the same query, I need to show that data 3 times in the same query. Once where event_avail = 1, then again where event_perf = 1 and finally where even_sec = 1. It can be across any hostid as long as it is in the groupid list as seen below. I am including an example of what looks like it works for ONE of the sets of data (see e.event_avail = 1) but I need to include a severity, hostid, color, severitydesc, fontcolor, eventid and description for the most servere event in that group where event_perf = 1 and again for wehre event_sec = 1... This is grouped by Groupid so I can show the most severe event that has happened for availability, performance and security within that group (across any host in the group).
Here is a sample that shows me avail data correctly (I think) select g.name AS name, g.groupid AS groupid, e.severity AS apoint, e.hostid AS ahostid, fs.color AS apointcolor, fs.severitydesc AS apointdesc, fs.fontcolor AS apointfont, e.eventid, e.description FROM groups g, fs_events e, fs_severity fs, hosts_groups hg WHERE e.hostid = hg.hostid and g.groupid = hg.groupid and fs.severityid = e.severity and e.event_avail = 1 and e.time_stamp >= date_sub(now(), interval 30 DAY) and e.acknowledged in (0,1) and g.groupid in (2,3,4,5,6) group by groupid order by name,apoint desc Here are the tables involved: CREATE TABLE `groups` ( `groupid` bigint(20) unsigned NOT NULL default '0', `name` varchar(64) NOT NULL default '', PRIMARY KEY (`groupid`), KEY `groups_1` (`name`), KEY `groupid` (`groupid`,`name`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `hosts_groups` ( `hostgroupid` bigint(20) unsigned NOT NULL default '0', `hostid` bigint(20) unsigned NOT NULL default '0', `groupid` bigint(20) unsigned NOT NULL default '0', PRIMARY KEY (`hostgroupid`), KEY `hosts_groups_groups_1` (`hostid`,`groupid`), KEY `hostid` (`hostid`,`groupid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `fs_severity` ( `severityid` int(4) NOT NULL, `severitydesc` varchar(64) NOT NULL, `color` varchar(64) NOT NULL, `fontcolor` varchar(64) NOT NULL, `severityabbrev` varchar(64) default NULL, `severityclass` varchar(64) default NULL, PRIMARY KEY (`severityid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE `fs_events` ( `eventid` int(4) NOT NULL auto_increment, `hostid` int(4) NOT NULL, `expression` varchar(255) default NULL, `description` varchar(255) default NULL, `remediation` varchar(255) default NULL, `status` int(4) NOT NULL, `value` int(4) default NULL, `severity` int(4) NOT NULL, `time_stamp` datetime default NULL, `event_type` int(4) NOT NULL, `src_addr` varchar(64) NOT NULL default '', `dest_addr` varchar(64) default NULL, `service_type` varchar(64) default NULL, `event_avail` int(11) NOT NULL, `event_perf` int(11) NOT NULL, `event_sec` int(11) NOT NULL, `itemid` int(4) NOT NULL, `triggerid` int(4) NOT NULL, `devicetype` int(11) default NULL, `acknowledged` int(11) NOT NULL, `comment` varchar(4000) default NULL, `last_changed` datetime default NULL, `username` varchar(255) default NULL, `url` varchar(255) default NULL, PRIMARY KEY (`eventid`,`src_addr`), KEY `hostid` (`hostid`), KEY `severity` (`severity`), KEY `time_stamp` (`time_stamp`), KEY `triggerid` (`triggerid`), KEY `hostid_2` (`hostid`,`severity`,`time_stamp`,`triggerid`), KEY `hostid_3` (`hostid`,`status`,`severity`) ) ENGINE=MyISAM AUTO_INCREMENT=20967 DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
