Complex SQL Query - OR - dynamic external datasource

[xefil]

 

Hi to all!

I've a problem creating a report based on a table I have. It looks like this:

mysql> show create table host_historyG 
*************************** 1. row *************************** 
Table: host_history 
Create Table: CREATE TABLE `host_history` ( 
`entry_datetime` datetime NOT NULL, 
`host` int(11) NOT NULL, 
`status` enum('UP','DOWN','UNREACHABLE') default NULL) 
ENGINE=InnoDB DEFAULT CHARSET=latin1 

<<skipped some rows and KEYS>>> 

This table contains records like, in example: 

+---------------------+------+--------+ 
| entry_datetime | host | status | 
+---------------------+------+--------+ 
| 2014-05-04 05:59:24 | 8123 | DOWN | 
| 2014-05-04 07:02:16 | 8123 | UP | 
| 2014-05-04 08:26:50 | 8123 | DOWN | 
| 2014-05-04 08:27:14 | 8123 | UP | 
+---------------------+------+--------+ 

I need to create a query that calculate the percent this host number 8123 was in DOWN state and in UP state, assuming the initial state is UP. 

In example timerange of give example is 24 hours. 

DOWN status at 05:59:24, means it was up from 00:00:00 to 05:59:24 = 21564 seconds 
then, it was DOWN between 05:59:24 and 07:02:16 = 3772 seconds 
then, it was UP between 07:02:16 and 08:26:50 = 5074 seconds 
then, it was DOWN between 08:26:50 and 08:27:14 = 24 seconds 
at the end, assume it was up until end date, because the last state is UP, until 2014-05-04 23:59:59 = 55965 seconds 

Summing up: 

DOWN for: 3796 seconds 
UP for: 82603 seconds 

UP+DOWN = 86399 seconds (one second missing by 23:59:59) = one day. 

UP=95.6% 
DOWN=4.5% 

Is there a way to obtain these precents in a SQL Query? If so, how?

I could in case create a PHP page that calculate these values but how in case tell JasperReports Server these values? A sort of external Data Source based on PHP page.

I could image I select a report in JasperServer, then choose a host from a dropdown menu which contains a list of hosts and their id-values. In my example hostA with id '8123'. Then I can choose startdate and enddate and press SUBMIT. These three values are sent to a php page which returns to JasperServer the UP/DOWN values. At the end, JasperServer will draw these values.

Is this second approach possible? How in case?

 

Thanks a lot!

 

Simon