Zabbix-Extras 1.1

[spaww]

New features:

- Zabbix-NS - Report of items are not supported;
- Support for internationalization;
- Installer with support for Portuguese and English;

"Old" features:
- Zabbix-CAT - Capacity and Trend
- Zabbix-SC - Storage Costs
- Script for easy Install.

The installation will be done by downloading two files (the first can download the second but as your monitoring server can be in a confined environment i'm giving the option to download both files).

After installation is added to a menu group called "Extras" at the end of the list of standard menus Zabbix 2.x. We choose a specific menu to group better customization.

Patch is available on: http://spinola.net.br/blog

[Ulan]

Hi,
I have installed Zabbix-Extras on the zabbix 2.0.2 with Oracle database. But I have following errors when I launched Zabbix-CAT:

ociexecute(): ORA-00904: "DATE_FORMAT": invalid identifier [include/db.inc.php:446]
SQL error [ORA-00904: "DATE_FORMAT": invalid identifier] in [
select it.units, it.description, ano, mes, dia, momento, AVG(valor) as valor
from items it
inner join
(select
hu.itemid,
DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%Y') as ano, DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%m') as mes, DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%d') as dia,
DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%m/%Y') as momento, hu.value_max as valor
from trends_uint hu
where hu.clock between 1341747081 and 1344339081
) a
on a.itemid = it.itemid
where it.itemid = 48524
group by momento
order by momento
]
ocifetchinto(): ORA-24374: define not done before fetch or execute and fetch [include/db.inc.php:579]

[spaww]

Hi,
I have installed Zabbix-Extras on the zabbix 2.0.2 with Oracle database. But I have following errors when I launched Zabbix-CAT:

ociexecute(): ORA-00904: "DATE_FORMAT": invalid identifier [include/db.inc.php:446]
SQL error [ORA-00904: "DATE_FORMAT": invalid identifier] in [
select it.units, it.description, ano, mes, dia, momento, AVG(valor) as valor
from items it
inner join
(select
hu.itemid,
DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%Y') as ano, DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%m') as mes, DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%d') as dia,
DATE_FORMAT(FROM_UNIXTIME(hu.clock), '%m/%Y') as momento, hu.value_max as valor
from trends_uint hu
where hu.clock between 1341747081 and 1344339081
) a
on a.itemid = it.itemid
where it.itemid = 48524
group by momento
order by momento
]
ocifetchinto(): ORA-24374: define not done before fetch or execute and fetch [include/db.inc.php:579]

Hi Ulan,

I dont have a ORACLE database here... if I send some querys by e-mail you can try to run ? I think I can solve this but need to try.

[Ulan]

Hi Ulan,

I dont have a ORACLE database here... if I send some querys by e-mail you can try to run ? I think I can solve this but need to try.

Hi Adail,
Yes of course, I'll try to run your queries. I sent my e-mail address via private message.

Thank you!

[Ulan]

Hi, I think I got it to work with the Oracle Database. I edited zabbix-cat.php, following lines:

$query = "
select ".($DB['TYPE'] == ZBX_DB_POSTGRESQL ? " DISTINCT ON(momento) ": "" )."it.units, it.description, ano, mes, dia, momento, AVG(valor) as valor
  from items it
 inner join
(select
hu.itemid,
to_char(FROM_UNIXTIME(hu.clock),'YYYY') as ano, to_char(FROM_UNIXTIME(hu.clock),'MM') as mes, to_char(FROM_UNIXTIME(hu.clock), 'DD') as dia,
to_char(FROM_UNIXTIME(hu.clock), '".$intervalMask[$timeShiftSource]."') as momento, "
. $sourceAgregator[get_request('agregation',0)]." as valor
from ".$tabela_log." hu
where hu.clock between ".$report_timesince." and  ".$report_timetill."
) a
on a.itemid = it.itemid
where it.itemid = ".$itemid."
group by units, ano, mes, dia, description, momento
order by momento
";

Here, FROM_UNIXTIME is the function:

create or replace function FROM_UNIXTIME
  (
    unix_time   in integer
  )
  return date
as
  converted_date  date;
--  tz              varchar2(4) := 'EST';
begin
  /**
   * This function is used to convert a Unix timestamp (UTC) into an Oracle DATE. The converted DATE will
   * be in UTC unless the optional local_tz parameter is provided.
   * @param unix_time The Unix timestamp to convert
   * @param to_local_tz 0 = UTC, 1 = Local database time zone
   * @param local_tz The name of the local time zone
   *
   * @return date
   */
  select
    to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + numtodsinterval(unix_time, 'SECOND')
  into converted_date
  from dual;
        
    -- Convert the GMT time to the current local timezone
    converted_date := new_time(converted_date, 'GMT', 'EST');
   
  return converted_date;
end FROM_UNIXTIME;

[spaww]

Hi, I think I got it to work with the Oracle Database. I edited zabbix-cat.php, following lines:

$query = "
select ".($DB['TYPE'] == ZBX_DB_POSTGRESQL ? " DISTINCT ON(momento) ": "" )."it.units, it.description, ano, mes, dia, momento, AVG(valor) as valor
  from items it
 inner join
(select
hu.itemid,
to_char(FROM_UNIXTIME(hu.clock),'YYYY') as ano, to_char(FROM_UNIXTIME(hu.clock),'MM') as mes, to_char(FROM_UNIXTIME(hu.clock), 'DD') as dia,
to_char(FROM_UNIXTIME(hu.clock), '".$intervalMask[$timeShiftSource]."') as momento, "
. $sourceAgregator[get_request('agregation',0)]." as valor
from ".$tabela_log." hu
where hu.clock between ".$report_timesince." and  ".$report_timetill."
) a
on a.itemid = it.itemid
where it.itemid = ".$itemid."
group by units, ano, mes, dia, description, momento
order by momento
";

Here, FROM_UNIXTIME is the function:

create or replace function FROM_UNIXTIME
  (
    unix_time   in integer
  )
  return date
as
  converted_date  date;
--  tz              varchar2(4) := 'EST';
begin
  /**
   * This function is used to convert a Unix timestamp (UTC) into an Oracle DATE. The converted DATE will
   * be in UTC unless the optional local_tz parameter is provided.
   * @param unix_time The Unix timestamp to convert
   * @param to_local_tz 0 = UTC, 1 = Local database time zone
   * @param local_tz The name of the local time zone
   *
   * @return date
   */
  select
    to_date('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + numtodsinterval(unix_time, 'SECOND')
  into converted_date
  from dual;
        
    -- Convert the GMT time to the current local timezone
    converted_date := new_time(converted_date, 'GMT', 'EST');
   
  return converted_date;
end FROM_UNIXTIME;

Hi Ulan,

Oracle dont have this function with another name ?