IDS Forum

Re: space alerting

Posted By: ALI SHAHNAZI
Date: Monday, 4 January 2010, at 1:43 a.m.

In Response To: Re: space alerting (DAVORIN KREMENJAS)

Happy New Year.

Thanks to All, I used this link : http://www.informix-zone.com/node/901

and made this script :

-- Putting information in ph_threshold table ( We can exclude some dbspaces )
-- Delete from ph_threshold where task_name = "Dbspace full alarm";
insert into ph_threshold values (0, "DBSPACE_PCT_FULL_RED", "Dbspace full alarm", 90, "NUMERIC", "Dbspace percentage full alarm - red");
insert into ph_threshold values (0, "DBSPACE_PCT_FULL_YELLOW", "Dbspace full alarm", 70, "NUMERIC", "Dbspace percentage full alarm - yellow");
insert into ph_threshold values (0, "DBSPACE_PCT_FULL_EXCLUDE", "Dbspace full alarm", "''", "STRING", "Comma separated list of dbspaces (each in single quites) to exclude from the check");

-- Checking space and put information in ph_alert
-- DROP FUNCTION _sysadmin_dbspace_full;
CREATE FUNCTION _sysadmin_dbspace_full(task_id INT, task_seq INT) RETURNING INTEGER

DEFINE dbspace_name CHAR(129);
DEFINE exclude_list LVARCHAR(1024);
DEFINE dbspace_query LVARCHAR(2048);
DEFINE dbspace_pct_free DECIMAL(4,2);
DEFINE dbspace_pct_free_red DECIMAL(4,2);
DEFINE dbspace_pct_free_yellow DECIMAL(4,2);

ON EXCEPTION IN (-201, -217)

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, "ERROR", "RED", "SQL", exclude_list, "Dbspace full alarm: Exclude list [" || exclude_list || "] wrong format!", NULL);

RETURN 0;

END EXCEPTION;

LET exclude_list = (select value from ph_threshold where name = "DBSPACE_PCT_FULL_EXCLUDE" and task_name = "Dbspace full alarm");

IF exclude_list IS NULL OR exclude_list = '' THEN

LET exclude_list = "''";

END IF

LET dbspace_query = "select trim(name) dbspace, round ((sum(nfree))/(sum(chksize))*100,2) percent_free from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum = c.dbsnum and trim(name) not in (" || exclude_list || ") and is_sbspace != 1 group by 1 union select trim(name)||' (UD)', round(100/sum(udsize)*sum(udfree),2) from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and trim(name) not in (" || exclude_list || ") and is_sbspace=1 group by 1 union select trim(name)||' (MD)', round(100/sum(mdsize)*sum(nfree),2) from sysmaster:sysdbspaces d, sysmaster:syschunks c where d.dbsnum=c.dbsnum and trim(name) not in (" || exclude_list || ") and is_sbspace=1 group by 1 INTO TEMP __t_dbspace_full";

EXECUTE IMMEDIATE dbspace_query;

let dbspace_pct_free_red = (select value from ph_threshold where name = "DBSPACE_PCT_FULL_RED" and task_name = "Dbspace full alarm");

IF dbspace_pct_free_red IS NULL THEN

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, "ERROR", "YELLOW", "SQL",

dbspace_name,

"Task parameter [dbspace_pct_free_red] for task [Dbspace full alarm] is not defined. Task not run.",

NULL

);

RETURN 0;

END IF

let dbspace_pct_free_yellow = (select value from ph_threshold where name = "DBSPACE_PCT_FULL_YELLOW" and task_name = "Dbspace full alarm");

IF dbspace_pct_free_yellow IS NULL THEN

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, "ERROR", "YELLOW", "SQL",

dbspace_name,

"Task parameter [dbspace_pct_free_yellow] for task [Dbspace full alarm] is not defined. Task not run.",

NULL

);

RETURN 0;

END IF

FOREACH

SELECT dbspace,

percent_free

INTO dbspace_name, dbspace_pct_free

FROM __t_dbspace_full

WHERE percent_free <= (100.00 - dbspace_pct_free_yellow)

AND percent_free > (100.00 - dbspace_pct_free_red)

ORDER BY percent_free DESC

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, "WARNING", "YELLOW", "DBSPACE",

dbspace_name,

"Dbspace [" || trim(dbspace_name) || "] has less than " || (100.00-dbspace_pct_free_yellow) || " percent free ",

NULL

);

END FOREACH

FOREACH

SELECT dbspace,

percent_free

INTO dbspace_name, dbspace_pct_free

FROM __t_dbspace_full

WHERE percent_free <= (100.00 - dbspace_pct_free_red)

ORDER BY percent_free DESC

INSERT INTO ph_alert

(ID, alert_task_id, alert_task_seq, alert_type, alert_color, alert_object_type, alert_object_name, alert_message, alert_action)

VALUES

(0,task_id, task_seq, "WARNING", "RED", "DBSPACE",

dbspace_name,

"Dbspace [" || trim(dbspace_name) || "] has less than " || (100.00-dbspace_pct_free_red) || " percent free ",

NULL

);

END FOREACH

DROP TABLE __t_dbspace_full;

RETURN 0;

END FUNCTION;

-- Put above function in ph_task for running automatically
-- DELETE FROM ph_task WHERE tk_name = "Dbspace full alarm";
INSERT INTO ph_task
(
tk_name,
tk_type,
tk_group,
tk_description,
tk_execute,
tk_start_time,
tk_stop_time,
tk_frequency
)
VALUES
(
"Dbspace full alarm",
"TASK",
"DISK",
"Checks if dbspace free space has dropped below configured thresholds",
"_sysadmin_dbspace_full",
DATETIME(00:00:00) HOUR TO SECOND,
NULL,
INTERVAL ( 1 ) HOUR TO HOUR
);

-- Send Email Procedure
-- DROP PROCEDURE SendEmail
CREATE PROCEDURE SendEmail( subject char(8), body char(200) )

SYSTEM 'echo "' || body || '" > /tmp/spacealert' ;

SYSTEM 'mail -s "' || subject || 'Alert For SPACE" informix@localhost </tmp/spacealert' ;

SYSTEM 'rm -f /tmp/spacealert' ;
END PROCEDURE ;

-- Trigger on insertion in ph_alert
-- DROP TRIGGER SpaceAlert
CREATE TRIGGER SpaceAlert

INSERT ON ph_alert

REFERENCING NEW AS post

FOR EACH ROW ( EXECUTE PROCEDURE SendEmail( post.alert_color,post.alert_message ) ) ;

Messages In This Thread

• space alerting
  ALI SHAHNAZI -- Monday, 21 December 2009, at 9:12 p.m.
  • Re: space alerting
    Art Kagel -- Monday, 21 December 2009, at 9:16 p.m.
  • RE: space alerting
    Habichtsberg, Reinhard -- Tuesday, 22 December 2009, at 4:15 a.m.
  • RE: space alerting
    Habichtsberg, Reinhard -- Tuesday, 22 December 2009, at 4:18 a.m.
  • Re: space alerting
    DAVORIN KREMENJAS -- Tuesday, 22 December 2009, at 7:34 a.m.
  • Re: space alerting
    Richard Snoke -- Tuesday, 22 December 2009, at 7:34 a.m.
  • Re: space alerting
    ALI SHAHNAZI -- Tuesday, 22 December 2009, at 9:20 p.m.
    • Re: space alerting
      John Miller iii -- Tuesday, 22 December 2009, at 11:52 p.m.
    • Re: space alerting
      DAVORIN KREMENJAS -- Wednesday, 23 December 2009, at 8:45 a.m.
      • Re: space alerting
        ALI SHAHNAZI -- Monday, 4 January 2010, at 1:43 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.