Jasper Time Series Charts

I've been working on a monitoring solution that uses a suite of open source tools to remotely monitor IT infrastructure resources. While that part has been moving along quite well, the next piece is the hardest part for me, giving managers a dumbed down report of what is going on. For this task, I have been learning how to use Jasper tools for generating reports based off of the perfdata that is being stored in a MySQL database.

It took me a bit of time to get the full swing of Jasper, but I think I am finally wrapping my brain around it. The biggest bear for me to learn was the time series chart. I tried googling this for a little over an hour and could not find a single example of a time series chart, so I figured I'd blog it for reference by others who may be trying to do similar things with Jasper.

For starters, here is my massive SQL query. Its not as bad as it looks. I am grabbing the perfdata (string) , the timestamp, the server name, and the name of the service check. I use some where statements to ensure I get only the stuff I want for this report and dump any blank values so my report doesn't blow up.

SELECT
nagios_servicechecks.`end_time` AS nagios_servicechecks_end_time,nagios_servicechecks.`perfdata` AS nagios_servicechecks_perfdata,nagios_services.`display_name` AS nagios_services_display_name,nagios_servicechecks.`end_time_usec` AS
nagios_servicechecks_end_time_usec,nagios_hosts.`display_name` AS nagios_hosts_display_name
FROM
`nagios_servicechecks` nagios_servicechecks,
`nagios_services` nagios_services,
`nagios_hosts` nagios_hosts
WHERE
nagios_servicechecks.`service_object_id` =
nagios_services.`service_object_id`
AND nagios_services.`display_name` = "Disk_Utilization"AND nagios_hosts.`alias` = "njugl001"
AND nagios_services.`host_object_id` = nagios_hosts.`host_object_id`
AND nagios_servicechecks.`perfdata` <> ""
OR nagios_servicechecks.`service_object_id` =
nagios_services.`service_object_id`
AND nagios_services.`display_name` = "Disk_Utilization"
AND nagios_hosts.`alias` = "njugl002"
AND nagios_services.`host_object_id` = nagios_hosts.`host_object_id`
AND nagios_servicechecks.`perfdata` <> ""

I used a phpmyadmin to run the query and ensure its only returning the values I want, which it does, so I moved on. One snag that held me up for a while was which band to put the chart. I started by putting the chart in the detail band. After troubleshooting for quite some time, I came to the relization that for each row returned by the query from the main report, it would print a copy of the chart, which gave me a huge report, which I didn't want. To remedy, I moved the chart into the Title band.

Next, I added the time series chart onto my band and set it up all to fit perfectly on the page and created a subdataset for the query I will be using with this report. I then went to the chart data and set the Sub Dataset (under the dataset tab) to the new sub dataset that I created with my query. The report type was set to report (default) and all other values on this tab were left alone.

One thing I learned was the Time Period value under the details tab for chart data was VERY important. This specified the minimal increments for plot points to be differentiated. For example, if I have results that are timestamped every 15 minutes and I set the Time Period to "Days", it will only plot one point for all 96 values per day. This threw me off for a very long time, as I was only getting 1 point on my graph for a subset of data that I knew had hundreds of values. As soon as I changed the Time Period from 'weeks' down to 'hours' I was able to see my values. I would also suggest taking a look at the "print repeated values" checkbox in the chart properties.

Next I had to do some casting and parsing. My data plots were strings, so I needed to use string tokenizers to parse out the numerical value for plotting, then convert it over to a number so Jasper knew what to do with it. My timestamps were pretty complicated as well. The value was of type java.sql.timestamp, but the expected value was java.util.date. I was able to use the getTime() method on the timestamp to do a simple conversion. I was under the impression that the Time Period error I had was an error related to timestamp casting because my values were coming out at 00:00.000 every time, but I later learned this was the "round to the nearest week" that I had set in Time Period earlier.

Now that I have all of my data in the correct format, its time to get it all put into the chart data. So, I added a new Time Series under the details tab of Chart Data and the three fields I filled out are:

• Series Expression (String): this is what each different plot on the graph is labelled. I used my field that contains the server name to label each plot
• Time Period Expression (Date): this is the expression for the date value of each plot point
• Value Expression (Number): this is what number is going to be plotted on the chart.

I think I now have my head wrapped around this Jasper Time Series Chart stuff.. please feel free to shoot me an email and I can send you a copy of my .jrxml file for reference.