SQL query to know which items and triggers are in a host

[angelhc]

Hi all!,

Please, could someone tell us how to make a query to the mysql to know for each host what items and triggers are associated to each host?

We want to implement the jasper reports but before we need to know which items are on each host, directly from the Database.

Thanks so much!

[angelhc]

My first approach:

Select a.host, b.description
from (Select hostid,host from hosts where host not like 'Template%') a, items b
where a.hostid = b.hostid;

But it shows this output:
ndilo | Used disk space on $1 |
ndilo | Espacio libre en $1 en % |
ndilo | Espacio libre en $1 en % |
ndilo | Espacio libre en $1 en % |
ndilo | Free disk space on $1 |

The $1 I think is the variable wich means the filesystem (in this case) but we need the complete value
Any ideas?

And on the other hand, What's the relation between items table and triggers one??? I can't find it.

[trikke]

Hi there,

quick and dirty:

select concat(h.host,'ç',i.key_,'ç',i.description,'ç',t.e xpression,'ç',f.functio
n,'ç',f.parameter) from items i, hosts h, triggers t, functions f where h.hostid=i.hostid and i.itemid=f.itemid and f.triggerid=t.trig
gerid and h.host='"$HOST"'

Here u see, relation between items and triggers go throug the functions.

Greets
Patrick

[angelhc]

Thx for the replay trikke, is not exactly what we need but is so usefull!

[nelsonab]

I have a relationship diagram somewhere for part of the Zabbix database, I'll see if I can find it for you.

[untergeek]

nelsonab,

That would be wonderful. I've begged for that in other forum posts in the past but nobody seemed to know of one. We need to pin that to a wiki post or something.

[angelhc]

I have a relationship diagram somewhere for part of the Zabbix database, I'll see if I can find it for you.

It could be very interesting!!
Please post it.
Thanks so much.