[#ZBXNEXT-2611] Add action to "Database monitor" item if query result is empty.

Comment by Aleksandrs Saveljevs [ 2014 Nov 21 ]

How about a notification when an item goes unsupported ( see https://www.zabbix.com/documentation/2.4/manual/config/notifications/unsupported_item )? Although filtering possibilities only include "Application" condition, but not item type.

Comment by Denis [ 2014 Nov 24 ]

I don't need actions on unsupported items. and items I'm talking about aren't "unsupported". They just in some periods of time return empty results, and other time they return some result.
E.g. Oracle users Audit query item looks like:

select username "username",
       to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS') "time_stamp",
       action_name "statement",
       os_username "os_username",
       userhost "userhost",
       returncode || decode(returncode, '1004', '-Wrong Connection', '1005', '-NULL Password', '1017',  '-Wrong Password', '1045',  '-Insufficient Priviledge', '0', '-Login Accepted', '--') "returncode"
  from sys.dba_audit_session
 where (sysdate - timestamp) * 24 < 1
   and returncode <> 0
 order by timestamp

And of course if there are no items in this table i returns empty result which should be logged in zabbix host data as "empty", "none" or whatever else.
In SQL to write such statement if empty result return simple row/column with result "empty" is quite difficult.
Orabbix was doing it by himself sending whatever result to zabbix trapper.
I think programmatically it should be quite easy to implement such "conditional check"

At this time I have 4 such problematic queries, on of them was solved like this:
Locked users probe, solved because it returns only one column and can be "solved" within SQL:

SELECT username || ' ' || lock_date || ' ' || account_status as res
  FROM dba_users
 where ACCOUNT_STATUS like 'EXPIRED(GRACE)'
    or ACCOUNT_STATUS like 'LOCKED(TIMED)'
union
select 'none'
from dual where not exists 
(
SELECT username
  FROM dba_users
 where ACCOUNT_STATUS like 'EXPIRED(GRACE)'
    or ACCOUNT_STATUS like 'LOCKED(TIMED)'
)

--------------- Problematic:
==Tablespaces

SELECT *
  FROM (select '- Tablespace ->',
               t.tablespace_name ktablespace,
               '- Type->',
               substr(t.contents, 1, 1) tipo,
               '- Used(MB)->',
               trunc((d.tbs_size - nvl(s.free_space, 0)) / 1024 / 1024) ktbs_em_uso,
               '- ActualSize(MB)->',
               trunc(d.tbs_size / 1024 / 1024) ktbs_size,
               '- MaxSize(MB)->',
               trunc(d.tbs_maxsize / 1024 / 1024) ktbs_maxsize,
               '- FreeSpace(MB)->',
               trunc(nvl(s.free_space, 0) / 1024 / 1024) kfree_space,
               '- Space->',
               trunc((d.tbs_maxsize - d.tbs_size + nvl(s.free_space, 0)) / 1024 / 1024) kspace,
               '- Perc->',
               decode(d.tbs_maxsize,
                      0,
                      0,
                      trunc((d.tbs_size - nvl(s.free_space, 0)) * 100 /
                            d.tbs_maxsize)) kperc
          from (select SUM(bytes) tbs_size,
                       SUM(decode(sign(maxbytes - bytes), -1, bytes, maxbytes)) tbs_maxsize,
                       tablespace_name tablespace
                  from (select nvl(bytes, 0) bytes,
                               nvl(maxbytes, 0) maxbytes,
                               tablespace_name
                          from dba_data_files
                        union all
                        select nvl(bytes, 0) bytes,
                               nvl(maxbytes, 0) maxbytes,
                               tablespace_name
                          from dba_temp_files)
                 group by tablespace_name) d,
               (select SUM(bytes) free_space, tablespace_name tablespace
                  from dba_free_space
                 group by tablespace_name) s,
               dba_tablespaces t
         where t.tablespace_name = d.tablespace(+)
           and t.tablespace_name = s.tablespace(+)
         order by 8)
 where kperc > 98
   and tipo <> 'T'
   and tipo <> 'U'

== User locks

select sn.USERNAME || '@' || sn.machine,
       '|SID->' || m.SID,
       '|Serial->' || sn.SERIAL#,
       '|Lock Type->' || m.TYPE,
       decode(LMODE,1, 'Null', 2, 'Row-S (SS)', 3, 'Row-X (SX)', 4, 'Share',  5, 'S/Row-X (SSX)',  6, 'Exclusive') lock_type,
       decode(REQUEST, 0, 'None', 1,  'Null',  2, 'Row-S (SS)',  3, 'Row-X (SX)',  4,  'Share', 5, 'S/Row-X (SSX)',  6, 'Exclusive') lock_requested,
       '|Time (Sec)->' || m.CTIME "Time(sec)",
       '|ID1->' || m.ID1,
       '|ID2->' || m.ID2,
       '|SQL Text->' || t.SQL_TEXT
  from v$session sn, v$lock m, v$sqltext t
 where t.ADDRESS = sn.SQL_ADDRESS
   and t.HASH_VALUE = sn.SQL_HASH_VALUE
   and ((sn.SID = m.SID and m.REQUEST != 0) or
       (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and
       (ID1, ID2) in (select s.ID1, s.ID2
                          from v$lock S
                         where REQUEST != 0
                           and s.ctime > 5
                           and s.ID1 = m.ID1
                           and s.ID2 = m.ID2)))
 order by sn.USERNAME, sn.SID, t.PIECE

Comment by Aleksandrs Saveljevs [ 2014 Nov 24 ]

If your SQL queries return empty response and the corresponding items do not become unsupported, I presume they just contain an empty string. If so, why don't you use strlen() function to check that the result is empty and alert on that?

Comment by Denis [ 2014 Nov 24 ]

They do become unsupported when SQL queries return empty results.
I don't want alert or action, It should be considered like proper data received from monitored host, and logged in it's history (latest data) like "empty".
I've released my initial template_v1 on github:
https://github.com/denissss/zbx_oracle_odbc

Comment by Aleksandrs Saveljevs [ 2014 Nov 24 ]

According to https://www.zabbix.com/documentation/2.4/manual/config/items/itemtypes/odbc_checks#important_notes , only the first column of the first row is used. Why don't you simply add "union select 'none'" to all the queries? The "select from dual where not exists ..." part does not seem to be necessary in the second example above.

[ZBXNEXT-2611] Add action to "Database monitor" item if query result is empty. Created: 2014 Nov 21 Updated: 2015 Jun 25
Status:	Need info
Project:	ZABBIX FEATURE REQUESTS
Component/s:	Server (S)
Affects Version/s:	2.4.2
Fix Version/s:	None

[ZBXNEXT-2611] Add action to "Database monitor" item if query result is empty. Created: 2014 Nov 21 Updated: 2015 Jun 25