zabbix and oracle

[jansonz]

Hello, I have problem to see Most busy triggers top 100 in zabbix web frontend. My zabbix ir working with Oracle database. The error is:

* ociexecute() [function.ociexecute]: ORA-00918: column ambiguously defined[/var/www/html/include/db.inc.php:311]
* SQL error [ORA-00918: column ambiguously defined] in [select * from (select h.host, t.triggerid, t.description, t.description, t.priority, t.expression, count(distinct e.eventid) as count from hosts h, triggers t, functions f, items i, events e where h.hostid = i.hostid and i.itemid = f.itemid and t.triggerid=f.triggerid and t.triggerid=e.objectid and e.object=0 and e.clock>1215329468 and h.hostid in (10030,10031,10032,10033,10034,10035,10029,10020,1 0003,10024,10008,10001,10009,10016,10007,10004,100 02,10040,10094,10080,10095,10044,10096,10097,10098 ,10127,10099,10062,10091,10152,10153,10081,10084,1 0065,10067,10100,10134,10102,10072,10036,10092,100 68,10085,10103,10136,10135,10093,10086,10048,10138 ,10105,10106,10107,10108,10137,10082,10110,10109,1 0054,10111,10112,10045,10069,10073,10115,10114,101 16,10117,10118,10046,10074,10113,10143,10139,10055 ,10052,10119,10075,10120,10038,10025,10070,10076,1 0087,10049,10050,10051,10121,10122,10140,10071,100 37,10101,10056,10123,10088,10141,10083,10129,10125 ,10126,10142,10057,10058,10059,10039,10047,10149,1 0155,10148,10147,10146,10145,10150,10151,10154,100 89,10077,10128,10124,10090,10026,10027,10028,10156 ,10157,10158,10159,10078,10130,10131,10060,10061,1 0066,10104,10144,10132,10064,10042,10041,10043,100 63,10133,10079) and round(t.triggerid/100000000000000) in (0) group by h.host,t.triggerid,t.description,t.expression,t.pr iority order by count desc, h.host, t.description, t.triggerid) where rownum<100]
* ocifetchinto() expects parameter 1 to be resource, boolean given[/var/www/html/include/db.inc.php:418]


What could be the problem??

[Aly]

I think this peace of SQL is the source of a problem:

count(distinct e.eventid) as count

The patch:

[jansonz]

Hello, the problem is still there:

* ociexecute() [function.ociexecute]: ORA-00918: column ambiguously defined[/var/www/html/include/db.inc.php:311]
* SQL error [ORA-00918: column ambiguously defined] in [select * from (select h.host, t.triggerid, t.description, t.description, t.priority, t.expression, count(distinct e.eventid) as cnt_event from hosts h, triggers t, functions f, items i, events e where h.hostid = i.hostid and i.itemid = f.itemid and t.triggerid=f.triggerid and t.triggerid=e.objectid and e.object=0 and e.clock>1215334516 and h.hostid in (10030,10031,10032,10033,10034,10035,10029,10020,1 0003,10024,10008,10001,10009,10016,10007,10004,100 02,10040,10094,10080,10095,10044,10096,10097,10098 ,10127,10099,10062,10091,10152,10153,10081,10084,1 0065,10067,10100,10134,10102,10072,10036,10092,100 68,10085,10103,10136,10135,10093,10086,10048,10138 ,10105,10106,10107,10108,10137,10082,10110,10109,1 0054,10111,10112,10045,10069,10073,10115,10114,101 16,10117,10118,10046,10074,10113,10143,10139,10055 ,10052,10119,10075,10120,10038,10025,10070,10076,1 0087,10049,10050,10051,10121,10122,10140,10071,100 37,10101,10056,10123,10088,10141,10083,10129,10125 ,10126,10142,10057,10058,10059,10039,10047,10149,1 0155,10148,10147,10146,10145,10150,10151,10154,100 89,10077,10128,10124,10090,10026,10027,10028,10156 ,10157,10158,10159,10078,10130,10131,10060,10061,1 0066,10104,10144,10132,10064,10042,10041,10043,100 63,10133,10079) and round(t.triggerid/100000000000000) in (0) group by h.host,t.triggerid,t.description,t.expression,t.pr iority order by cnt_event desc, h.host, t.description, t.triggerid) where rownum<100]
* ocifetchinto() expects parameter 1 to be resource, boolean given[/var/www/html/include/db.inc.php:418]

[Aly]

Problem in

(select h.host, t.triggerid, [U]t.description, t.description[/U],

There is a patch for this problem somewhere in this forum, or you can remove second column manually.

P.S. Also this fixed in svn.

[jansonz]

tnx, thats solved the problem.