Mk_oracle and custom SQL

[fabricedupre (Fabrice Dupré)]

CMK version:2.0.0p34 (CEE)
OS version:Red Hat Enterprise Linux Server release 7.9 (Maipo)

Error message:Unknown error: 19.9.0.0.0

Output of “cmk --debug -vvn hostname”: (If it is a problem with checks or plugins)

[fabricedupre (Fabrice Dupré)]

I need some help to configure custom sql.
My mk_oracle.cfg file

SQLS_SECTIONS="CheckVersion"

CheckVersion () {
    SQLS_SIDS="ATLANTIC"
    SQLS_DIR="/etc/check_mk"
    SQLS_SQL="CheckVersion.sql"
}

My CheckVersion.sql file

set serveroutput on
set feedback off
declare
l_version varchar2(20);
l_version_full varchar2(20);
begin
execute immediate 'select VERSION from v$instance' into l_version;
if l_version like '19%' then
execute immediate 'select VERSION_FULL from v$instance' into l_version_full;
else
l_version_full := l_version;
end if;
dbms_output.put_line(l_version_full);
end;
/
prompt exit:0

On cosole i’ve my service on unknow

[miwu]

Hi,

custom sqls are quite boring in check_mk. I tried it but stayed at check_oracle_health…

The problem in your case is, that the output of your sql has to start with the keywords described in:

Documentation

So you have to change your PL/SQL-Block in that way, that the output is like this:

detail: 19.18
exit: 0
perfdata: 
long:

Currently, your output is just the Version without the keyword before it.

[fabricedupre (Fabrice Dupré)]

Thanks for your help, it unblocked me.
My pl/sql where changed like that

set serveroutput on
set feedback off
declare
  l_version varchar2(20);
  l_version_full varchar2(20);
begin
  execute immediate 'select VERSION from v$instance' into l_version;
  if l_version like '19%' then
    execute immediate 'select VERSION_FULL from v$instance' into l_version_full;
  else
    l_version_full := l_version;
  end if;
  dbms_output.put_line('details: ' || l_version_full);
  dbms_output.put_line('exit: 1');
  dbms_output.put_line('perfdata:');
  dbms_output.put_line('long:');
end;
/

[fabricedupre (Fabrice Dupré)]

I’ve also made the following pl/sql to count the users who will expire within 30 days

set serveroutput on
set feedback off
set head off
set lines 130
set pages 62
set verify off
set feedback off
set echo off
set verify off
col username for a30
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS'
declare
  l_users_expired varchar2(20);
  l_users_expired_list varchar2(500);
begin
  execute immediate 'select count(*) from dba_users where ACCOUNT_STATUS in (''OPEN'',''EXPIRED(GRACE)'') and to_char(EXPIRY_DATE,''YYYYMMDD'') > to_char(sysdate,''YYYYMMDD'') and  EXPIRY_DATE-sysdate between 1 and 130' into l_users_expired;
  if l_users_expired like '0' then
    dbms_output.put_line('details: ' || l_users_expired || ' ORACLE account(s) will expire within fiew days');
    dbms_output.put_line('exit: 0');
    dbms_output.put_line('perfdata:');
    dbms_output.put_line('long:');
  else
    dbms_output.put_line('details: ' || l_users_expired || ' ORACLE account(s) will expire within fiew days');
    dbms_output.put_line('exit: 2');
    dbms_output.put_line('perfdata:');
    dbms_output.put_line('long:');
  end if;
end;
/

[miwu]

I am very happy to were able to unblock you

Thank you for the PL/SQL Examples. I think they are very helpful for other users trying to get Custom SQL running.

[fabricedupre (Fabrice Dupré)]

Another one to list Oracle accounts who will be expired within n days

set serveroutput on
set feedback off
set head off;
set lines 130;
set pages 62;
set verify off;
set feedback off;
set echo off;
set verify off;
define days_ahead = 300;
col username for a30;
alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
declare
  l_users_expired varchar2(20);
  l_username varchar2(300);
  l_account_status varchar2(300);
  l_expiry_date date;
  l_created_date date;

  v_sql varchar2(32767) := 'select USERNAME,ACCOUNT_STATUS,EXPIRY_DATE,CREATED from dba_users where ACCOUNT_STATUS in (''OPEN'',''EXPIRED(GRACE)'') and to_char(EXPIRY_DATE,''YYYYMMDD'') > to_char(sysdate,''YYYYMMDD'') and  EXPIRY_DATE-sysdate between 1 and &days_ahead order by CREATED,USERNAME';
  type t_rec is record (l_username varchar2(300), l_account_status varchar2(300), l_expiry_date date, l_created_date date);
  v_rec t_rec;
  c1 sys_refcursor;
begin
  execute immediate 'select count(*) from dba_users where ACCOUNT_STATUS in (''OPEN'',''EXPIRED(GRACE)'') and to_char(EXPIRY_DATE,''YYYYMMDD'') > to_char(sysdate,''YYYYMMDD'') and  EXPIRY_DATE-sysdate between 1 and &days_ahead' into l_users_expired;
  if l_users_expired like '0' then
    dbms_output.put_line('details: ' || l_users_expired || ' ORACLE account(s) will expire within fiew days');
    dbms_output.put_line('exit: 0');
    dbms_output.put_line('perfdata:');
    dbms_output.put_line('long:');
  else
    open c1 for v_sql;
    dbms_output.put_line('USERNAME;ACCOUNT_STATUS;EXPIRY_DATE;CREATED');
    loop
      fetch c1 into v_rec;
      exit when c1%notfound;
      dbms_output.put_line(v_rec.l_username||';'||v_rec.l_account_status||';'||v_rec.l_expiry_date||';'||v_rec.l_created_date);
    end loop;
    dbms_output.put_line('exit: 2');
    dbms_output.put_line('perfdata:');
    dbms_output.put_line('long:');
  end if;
end;
/

I’ve improved my knowledge on pl/sql this few days

[miwu]

Cool, thanks for this block.

If you want to show many lines of additional text outside from the status line, you can use the “long:”-field. e.g. in order to list the expiriy informations abort all expiring users. This will be shown in check_mk in the Long-Output-Area of the check-details.