Jump to content

Complex SQL Query - OR - dynamic external datasource


Recommended Posts


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) 

<<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. 


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!




Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...