В чем опасность использовать прямую работу с БД если есть API
Жил был скрипт, который использовал вот таакой запрос к БД Zabbix:
select sendto from media where userid in (select userid from (select users_groups.userid from operations inner join opmessage_grp on opmessage_grp.operationid=operations.operationid inner join users_groups on opmessage_grp.usrgrpid=users_groups.usrgrpid where operations.actionid in (select actionid from conditions where conditiontype=2 and value=%s union all select actionid from conditions where conditiontype=0 and value in (SELECT groups.groupid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=%s) union all select actionid from conditions where conditiontype=1 and value in (SELECT hosts.hostid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=%s)) union all select opmessage_usr.userid from operations inner join opmessage_usr on opmessage_usr.operationid=operations.operationid where operations.actionid in (select actionid from conditions where conditiontype=2 and value in (select triggers.triggerid from triggers where triggers.triggerid = %s union all select triggers.templateid from triggers where triggers.triggerid = %s) union all select actionid from conditions where conditiontype=0 and value in (SELECT groups.groupid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=%s) union all select actionid from conditions where conditiontype=1 and value in (SELECT hosts.hostid FROM triggers INNER JOIN functions ON triggers.triggerid=functions.triggerid INNER JOIN items ON items.itemid=functions.itemid INNER JOIN hosts ON hosts.hostid=items.hostid INNER JOIN hosts_groups ON hosts_groups.hostid=hosts.hostid INNER JOIN groups ON groups.groupid=hosts_groups.groupid WHERE triggers.triggerid=%s))) as us group by userid union all select userid from users_groups where usrgrpid=18) and mediatypeid=5
Единственной функцией котого было дать список телефонов пользователей по id триггера. И работал бы этот скрипт еще долго, если бы не случилось обновление Zabbix с 3.4 до 4.4 Структура БД изменилась. Запрос работать перестал. Посмотрел я на него, посмотрел.. И понял, я наверное был не в себе когда его писал, и повторить такой подвиг больше не смогу. Решил переписать его использую API Zabbix. Вышло много понятнее 😉
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
echo "-получаем api-key\n"; $res=$zab->Auth($login, $password); $triggerid=$options["t"]; $txt=$options["s"]; $users_id_for_sms=array(); echo "-действия по триггеру\n"; $res=$zab->Execute("action.get", ["triggerids"=>$triggerid,"output"=>'extend','selectOperations'=>'extend','selectRecoveryOperations'=>'extend','selectFilter'=>'extend']); ParseRes($res); echo "-получаем хост\n"; $hosts=$zab->Execute("host.get", ["triggerids"=>$triggerid]); foreach ($hosts->result as $host) { $hostid=$host->hostid; $hostname=$host->name; echo "--действия по хосту $hostid $hostname\n"; $res=$zab->Execute("action.get", ["hostids"=>$hostid,"output"=>'extend','selectOperations'=>'extend','selectRecoveryOperations'=>'extend','selectFilter'=>'extend']); ParseRes($res); $hostsg=$zab->Execute("hostgroup.get", ["hostids"=>$hostid]); foreach ($hostsg->result as $hostg) { $groupid=$hostg->groupid; echo "--- действия по группе хоста $groupid\n"; $res=$zab->Execute("action.get", ["groupids"=>$groupid,"output"=>'extend','selectOperations'=>'extend','selectRecoveryOperations'=>'extend','selectFilter'=>'extend']); ParseRes($res); } }; echo "- пользователи ZABBIX\n"; var_dump($users_id_for_sms); $sms=array(); foreach ($users_id_for_sms as $userid) { $res=$zab->Execute("user.get", ["userids"=>$userid,"output"=>'extend','status'=>0,"selectMedias"=>"extend"]); foreach ($res->result as $usersinfo) { foreach ($usersinfo->medias as $md) { if ($md->mediatypeid==5){ $sms[]=$md->sendto; }; }; } } var_dump($sms); function ParseRes($res){ global $zab,$users_id_for_sms; if (count($res->result)!=0){ foreach ($res->result as $oper) { foreach ($oper->operations as $vl) { echo "---получаю пользователей группы\n"; foreach ($vl->opmessage_grp as $usergroups) { $usrgrpid= $usergroups->usrgrpid; echo "----$usrgrpid\n"; $res=$zab->Execute("user.get", ["usrgrpids"=>$usrgrpid,"output"=>'extend','status'=>0]); foreach ($res->result as $user) { $users_id_for_sms[]=$user->userid; }; // $res=$zab->Execute("user.get", ["usrgrpids"=>15,"output"=>'extend','status'=>0]); // foreach ($res->result as $user) { // $users_id_for_sms[]=$user->userid; // }; }; echo "---добавляю просто пользователей\n"; //var_dump($vl->opmessage_usr); foreach ($vl->opmessage_usr as $userslist) { $users_id_for_sms[]= $userslist->userid; }; }; } }; }; |