Dear All, I have created zones(Zon1, Zone2 etc..,) as a group and I have assigned some nodes(servers) to each zone.
I have difficulty in writing a SQL query to find the availability of each zone meaning find how long all nodes in zone1 are down at once and how long. e.g., zone1 have Srv1,Srv2,Srv3,Srv4. I am able to make this temp table sample data as below. zonename nodeid nodelable noderegainedservice nodelostservice Zone1 27 Srv1 2013-02-21 10:04:56+08 2013-02-21 09:48:48+08 Zone1 27 Srv1 2013-02-21 10:14:01+08 2013-02-21 10:09:27+08 Zone1 27 Srv1 2013-02-21 10:26:29+08 2013-02-21 10:24:20+08 Zone1 27 Srv1 2013-02-21 11:27:24+08 2013-02-21 11:25:15+08 Zone1 27 Srv1 2013-02-28 16:24:59+08 2013-02-28 15:52:59+08 Zone1 27 Srv1 2013-02-28 16:56:19+08 2013-02-28 16:40:18+08 Zone1 27 Srv1 2013-02-28 17:09:28+08 2013-02-28 16:58:38+08 Zone1 27 Srv1 2013-02-28 17:39:50+08 2013-02-28 17:29:47+08 Zone1 27 Srv1 2013-03-01 09:39:36+08 2013-02-28 19:12:26+08 Zone1 27 Srv1 2013-03-01 13:35:07+08 2013-03-01 12:10:03+08 Zone1 27 Srv1 2013-03-04 11:04:14+08 2013-03-04 10:48:07+08 Zone1 27 Srv1 2013-03-06 16:36:56+08 2013-03-06 16:33:10+08 Zone1 27 Srv1 2013-03-13 13:54:11+08 2013-03-06 16:43:51+08 Zone1 27 Srv1 2013-03-14 11:43:28+08 2013-03-13 19:09:31+08 Zone1 27 Srv1 2013-03-18 18:38:16+08 2013-03-15 18:55:31+08 Zone1 27 Srv1 2013-03-22 11:18:57+08 2013-03-22 09:53:38+08 Zone1 27 Srv1 2013-03-28 16:48:27+08 2013-03-26 10:23:47+08 Zone1 27 Srv1 2013-04-04 10:33:24+08 2013-04-04 10:32:51+08 Zone1 27 Srv1 2013-04-04 11:48:54+08 2013-04-04 11:48:23+08 Zone1 27 Srv1 2013-04-08 19:01:34+08 2013-04-08 19:01:03+08 Zone1 27 Srv1 2013-04-08 19:37:05+08 2013-04-08 19:31:38+08 Zone1 27 Srv1 2013-04-08 21:48:07+08 2013-04-08 21:47:35+08 Zone1 27 Srv1 2013-04-08 21:54:02+08 2013-04-08 21:52:29+08 Zone1 27 Srv1 2013-04-10 09:33:53+08 2013-04-10 09:32:34+08 Zone1 27 Srv1 2013-04-10 12:01:01+08 2013-04-10 12:00:30+08 Zone1 27 Srv1 2013-04-10 14:57:25+08 2013-04-10 14:56:53+08 Zone1 27 Srv1 2013-04-10 16:25:50+08 2013-04-10 16:24:31+08 Zone1 27 Srv1 2013-04-10 16:57:02+08 2013-04-10 16:56:19+08 Zone1 27 Srv1 2013-04-10 17:17:37+08 2013-04-10 17:15:18+08 Zone1 27 Srv1 2013-04-11 21:35:43+08 2013-04-11 21:31:50+08 Zone1 39 Srv2 2013-04-05 13:15:53+08 2013-04-05 12:26:04+08 Zone1 39 Srv2 2013-04-05 13:23:10+08 2013-04-05 13:21:14+08 Zone1 39 Srv2 2013-04-05 13:35:23+08 2013-04-05 13:33:32+08 Zone1 39 Srv2 2013-04-05 15:17:25+08 2013-04-05 14:25:51+08 Zone1 39 Srv2 2013-04-07 16:49:56+08 2013-04-05 17:43:01+08 Zone1 39 Srv2 2013-04-09 22:32:19+08 2013-04-07 20:00:44+08 Zone1 39 Srv2 2013-04-09 22:38:02+08 2013-04-09 22:37:40+08 Zone1 39 Srv2 2013-04-10 11:16:21+08 2013-04-10 11:13:32+08 Zone1 39 Srv2 2013-04-10 16:15:37+08 2013-04-10 15:44:05+08 Zone1 39 Srv2 2013-04-10 16:23:07+08 2013-04-10 16:20:59+08 Zone1 39 Srv2 2013-04-10 16:48:46+08 2013-04-10 16:33:29+08 Zone1 39 Srv2 2013-04-10 17:19:11+08 2013-04-10 17:04:10+08 Zone1 39 Srv2 2013-04-11 21:39:21+08 2013-04-11 21:28:51+08 Zone1 39 Srv2 2013-04-11 22:05:02+08 2013-04-11 21:49:44+08 Zone1 39 Srv2 2013-04-15 14:02:11+08 2013-04-12 16:41:48+08 Zone1 39 Srv2 2013-04-17 00:00:00+08 2013-04-15 20:50:40+08 Zone1 29 Srv3 2013-03-12 17:20:02+08 2013-03-12 17:16:49+08 Zone1 29 Srv3 2013-03-12 18:08:30+08 2013-03-12 17:55:43+08 Zone1 13 Srv4 2013-01-09 17:23:59+08 2013-01-09 17:19:13+08 Zone1 13 Srv4 2013-01-10 16:54:27+08 2013-01-10 16:53:48+08 Zone1 13 Srv4 2013-01-10 16:59:55+08 2013-01-10 16:56:56+08 Zone1 13 Srv4 2013-01-10 17:07:10+08 2013-01-10 17:04:11+08 Zone1 13 Srv4 2013-01-10 17:13:54+08 2013-01-10 17:10:42+08 Zone1 13 Srv4 2013-01-16 10:31:45+08 2013-01-15 14:47:25+08 Zone1 13 Srv4 2013-01-24 17:52:35+08 2013-01-24 17:20:31+08 Zone1 13 Srv4 2013-01-28 17:24:25+08 2013-01-28 16:53:10+08 Zone1 13 Srv4 2013-02-18 12:16:45+08 2013-02-18 12:10:05+08 Zone1 13 Srv4 2013-02-18 15:00:26+08 2013-02-18 14:12:04+08 Zone1 13 Srv4 2013-02-18 17:11:10+08 2013-02-18 17:00:58+08 Zone1 13 Srv4 2013-02-21 10:14:24+08 2013-02-21 10:13:45+08 Zone1 13 Srv4 2013-02-25 14:29:39+08 2013-02-25 13:44:50+08 Zone1 13 Srv4 2013-02-26 10:40:08+08 2013-02-26 10:19:33+08 Zone1 13 Srv4 2013-03-04 11:37:34+08 2013-03-04 11:00:56+08 Zone1 13 Srv4 2013-04-10 16:25:27+08 2013-04-10 16:24:07+08 Zone1 13 Srv4 2013-04-10 17:17:39+08 2013-04-10 17:14:40+08 Zone1 13 Srv4 2013-04-11 21:39:05+08 2013-04-11 21:28:22+08 Cheers, Reddy. ------------------------------------------------------------------------------ Precog is a next-generation analytics platform capable of advanced analytics on semi-structured data. The platform includes APIs for building apps and a phenomenal toolset for data science. Developers can use our toolset for easy data analysis & visualization. Get a free account! http://www2.precog.com/precogplatform/slashdotnewsletter _______________________________________________ Please read the OpenNMS Mailing List FAQ: http://www.opennms.org/index.php/Mailing_List_FAQ opennms-devel mailing list To *unsubscribe* or change your subscription options, see the bottom of this page: https://lists.sourceforge.net/lists/listinfo/opennms-devel
