Reporting: iReport & mySQL Connectors

[Abosherpa]

Hey everyone.

I currently have a Zabbix server set up, and I am trying to find the way to pull the data from a remote mySQL database, and create reports by setting up mySQL Database connectors in iReport-Designer. Does anyone have any suggestions on how to get this to work. Or is there a guide anywhere, that gives better instructions on how to add an external mySQL source.

Thanks,
~Abosherpa

[Pada]

This is the Zabbix forum and not the JasperReports forum, so you'll be better off asking iReport-Designer on JasperReports website/forums (or whatever support methods they provide).

If you're trying to ask in what tables the Zabbix values are, then we can help you with that...

I didn't quite get your question - as to whether it was a Zabbix or JasperReports related one.

[Abosherpa]

Hey there..

Thanks for the response. It's just been challenging trying to find the right information. Sorry for not posing the question properly. It should go on the Jaspersoft forums too, but I was trying to see if anyone had any suggestions, as I'm currently reaching to find solutions.

I currently have Zabbix running, and I have been collecting data for a month or so now, but I'm having a problem trying to find a solution to do reporting. I read on these forums that iReport is a suggested product to use, but I'm thinking that implementing it isn't going to be as easy. Unless anyone here has any suggestions for better reporting options for Zabbix.

~Abosherpa

[Pada]

Ah OK.

Have a look at the iReport MySQL guides and tutorials, like the one explained here: http://ireport-guide.blogspot.com/20...e-or-data.html

• Take note that you may need to download the JDBC MySQL driver first.
• Also take note that you'll need to create a new MySQL user that would give you access (eg. SELECT only) from your remote host to your Zabbix tables.
  
  You can create a MySQL user with the following commands:
  

  create user 'zabbixuser'@'%' identified by 'zabbixpass';
  grant select privileges on 'zabbixdb'.* to 'zabbixuser'@'%';
  flush privileges;

  Just replace:
  - "zabbixuser" with the new MySQL user name that you want to use
  - "zabbixpass" with the new MySQL user's password
  - "zabbixdb" with your existing Zabbix MySQL database name
  
  Your machine that is hosting the MySQL server should also have a firewall rule in place to allow incoming TCP connections to port 3306 (default MySQL port) if it's blocking incoming connections by default.

Perhaps first test whether the MySQL credentials works on the remote host, by using something like SQLyog (or another MySQL client other than iReport), before attempting to get iReport's MySQL connector to work!

The table structure of Zabbix isn't that complex.
For integer items, the following table and fields may be important:

• hosts table = table containing all your Zabbix hosts
  - hostid column = unique id for that host, which has a 1-to-many relationship with the items table
  - host column = the name of the host
  - status column = whether the host is enabled (0)
• items table = table containing all your Zabbix items for each host
  - itemid column = unique id for the item on a particular host, which has a 1-to-many relationship with the history_uint table (as well as trends_uint, but I'm gonna ignore that for now)
  - hostid column = hostid, which references the hosts table
  - description column = description of the item
  - status column = whether the item is enabled (0)
  - value_type column = whether the item is an integer (3)
• history_uint table = the Zabbix table containing all the integer data points
  - itemid column = itemid, which references the items table
  - clock column = unix timestamp of that particular data point
  - value column = value at the particular data point

Here is a SQL query to get the last 10 data points of the host (that is enabled) with the name 'testhost' and an item (that is enabled and an integer) with the description 'testitem':

SELECT h.host as 'Host', h.hostid as 'Host ID', i.description as 'Item', i.itemid as 'Item ID', from_unixtime(d.clock) as 'Timestamp', d.value as 'Value'
FROM hosts h
RIGHT JOIN items i ON i.hostid = h.hostid
RIGHT JOIN history_uint d ON d.itemid = i.itemid
WHERE 
    h.hostid = (SELECT hostid FROM hosts WHERE host like 'testhost' LIMIT 1) AND
    i.itemid = (SELECT itemid FROM items WHERE hostid = h.hostid AND description like 'testitem' AND status = 0 AND value_type = 3 LIMIT 1)
ORDER BY d.clock DESC
LIMIT 10;

Please take note that this MySQL query isn't optimized for doing many items or multiple hosts either. You'll have to replace the subqueries with something else.

I hope this can get you somewhere. If not, then perhaps search some more for iReport and Zabbix

[Pada]

...and here is a complete example on how to draw CPU utilization reports from the trends table:

http://www.akamit.com/blog/2010/09/creating-and-using-reports-for-zabbix-with-ireports/

[Abosherpa]

Hey Pada...

I will give this a try tonight when I'm home from work, and let you know if I have any other issues. Thank you very much!

~Abosherpa

[wrocha]

Just one more information that was important to me. My mysql configuration did not allow remote connections, so i had to edit the "/etc/mysql/my.cnf" file and change the line:

bind-address = 127.0.0.1
to
bind-address = 0.0.0.0

After that you have to restart the mysql service

/etc/init.d/mysql restart