Wednesday, July 8, 2009

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.

10 comments:

  1. First, thanks so much for the reference! Kudos for writing it down...
    Second, about the band for the chart - I stumbled upon the same problem, and found that the proper solution is to put it in the Summary band. Catch is, it is 0-sized by default, so you have to enlarge it through the Properties pane.

    ReplyDelete
  2. Rivey - Thanks for posting about this. I stumbled onto this after spending about an hour or two trying to figure out why I had so many charts on the page.

    Could you send me a copy of the .jrxml file? That would be useful for me.

    Thanks,
    Eric

    ReplyDelete
  3. phethunia@yahoo.comDecember 9, 2009 at 11:02 AM

    Hi,

    Thanks you for this post, really cleared out a whole lot of issues I had with report I am trying to create. My report has to show:

    1. Y- axis "max_concurrent_calls"
    2. X-axis "15 minute interval" eg.00:15, 00:30, 00:4 etc.

    3. Series expression: "25-Oct-09, 26-Oct-2009" etc.

    I am pulling data from Orace DB with timestamp "java.sql.timestamp".


    Please help, can I have review of your code.

    Thanks in advance
    phethunia@yahoo.com

    ReplyDelete
  4. hi,thanks so much for writing this. but any idea about how to plot an upper control limit and lower control limit in a time series chart?

    ReplyDelete
  5. Hey, I have chosen 'day' and I have 2 records - 19 Apr 2010 and 20 Apr 2010. So the X axis starts at 19 Apr and ends at 20 Apr. It fills up the value in between with 19 Apr 00:00, 19 Apr 04:00 and so on. How can I make sure that there is only one tick label for one day?

    ReplyDelete
  6. Hi
    I need the time stamp in the x axis (time series) and a string value in the Y (value expression ) any ideas ?

    ReplyDelete
  7. @sadagopan: y value has to be a number value.. you cant have a string there..

    ReplyDelete
  8. Rivey, thanks for your excellent post. Please send me your jrxml file. I am struggling getting the timeseries chart right. Thanks,

    ReplyDelete
  9. How do I set the Time Period Expression to display DAYS_OF_MONTH?

    ReplyDelete
  10. I have 28 records where dates are repeating. i dont find all the values are plotted in the graph; rather i find the last value plotted on the graph. How to get all the values plotted in the graph?

    ReplyDelete