How yo Use zabbix db (trigger/alert)

[syslog]

Hi!

I want to create a webpage which show just the alerts of zabbix.
To do this, i need to have the severity, Host, name and some others informations (as status = PROBLEM).
So i want to do a sql request, but i don't understand the database. I think i have to request the tables "alerts" and "triggers" but i'm not sure and i don't see the link between them.

Can you explain simply the way, tables and request i have to do?

(sorry for my english...)
Thx!

[zabbix_zen]

Forgot in which zabbix .php this was,
dumbbed down version, replace "groupid IN (54,55,56,57)"
by your intended hostgroupids

<?php
require_once('include/config.inc.php');
function make_latest_issues($group){
global $USER_DETAILS;

$severity[0]="Not Classified";
$severity[1]="Information";
$severity[2]="Warning";
$severity[3]="Average";
$severity[4]="High";
$severity[5]="Disaster";

$priority=0;


$available_hosts = get_accessible_hosts_by_user($USER_DETAILS,PERM_RE AD_ONLY);
$available_triggers = get_accessible_triggers(PERM_READ_ONLY);

$scripts_by_hosts = get_accessible_scripts_by_hosts($available_hosts);
$config=select_config();

$sql = 'SELECT DISTINCT t.triggerid,t.status,t.description, t.priority, t.lastchange,t.value,h.host,h.hostid '.
' FROM triggers t,hosts h,items i,functions f, hosts_groups hg '.
' WHERE f.itemid=i.itemid '.
' AND h.hostid=i.hostid '.
' AND hg.hostid=h.hostid '.
' AND t.triggerid=f.triggerid '.
' AND t.status='.TRIGGER_STATUS_ENABLED.
' AND i.status='.ITEM_STATUS_ACTIVE.
// ' AND '.DBin_node('t.triggerid').
' AND '.DBcondition('t.triggerid',$available_triggers).
' AND h.status='.HOST_STATUS_MONITORED.
' AND t.value='.TRIGGER_VALUE_TRUE.
' AND h.hostid IN (select hostid from hosts_groups where groupid IN (54,55,56,57))'.
' ORDER BY t.lastchange DESC';

$result = DBselect($sql);
while($row=DBfetch($result)){
// Check for dependencies
if(trigger_dependent($row["triggerid"])) continue;

$event_sql = 'SELECT e.eventid, e.value, e.clock, e.objectid as triggerid, e.acknowledged, t.type '.
' FROM events e, triggers t '.
' WHERE e.object='.EVENT_SOURCE_TRIGGERS.
' AND e.objectid='.$row['triggerid'].
' AND t.triggerid=e.objectid '.
' AND e.value='.TRIGGER_VALUE_TRUE.
' ORDER by e.object DESC, e.objectid DESC, e.eventid DESC';

$res_events = DBSelect($event_sql,1);

while($row_event=DBfetch($res_events)){

$description = expand_trigger_description_by_data(
array_merge($row, array("clock"=>$row_event["clock"])),
ZBX_FLAG_EVENT);

$priority=$row["priority"];
$lastchange=date("Y-m-d H:i:s",$row["lastchange"]);
//print $row["host"]." - ".$severity[$priority]." - ".$description."\n";
print $row["host"]." - ".$severity[$priority]." - ".$description."\n";
}
unset($row,$description,$actions,$alerts,$hint);
}
}


$out=make_latest_issues($argv[1]);

?>

[syslog]

Sorry i don't understand how to use it ...

The 'alerts' table is not require to show the triggers alerts?
Can you explain?
Thx!

[zabbix_zen]

If I've correctly understood your objective the events table has everything you need

[syslog]

Event table contain clock and .... objectid?
I don't see an object table so how can i use this object id ?

I'm not so good to understand databases...

Maybe event.objectid = trigger.triggerid ?

But what's the column value? And how to request the other informations?

[zabbix_zen]

You must read it from top to buttom,
the first query among other things guarantees

f.itemid=i.itemid '.
' AND h.hostid=i.hostid '.
' AND hg.hostid=h.hostid '.
' AND t.triggerid=f.triggerid '.

it's result is passed to a while cycle to check for Trigger dependencies
, grabs them into a list of rows,
then filters by (among other things)

e.objectid='.$row['triggerid'].
' AND t.triggerid=e.objectid '.

....
etc
....

All this to grab your intended,
$row["host"]." - ".$severity[$priority]." - ".$description."

This code is not pure SQL code and as you can see uses some functions from include/config.inc.php