Example: SQL procedure

This example SQL procedure generates a report on the total number of alerts received (and deduplicated) for a given node.

Within the SQL Procedure Details window, the SQL procedure named node_report is created with the following entries:
Table 1. Entries for the node_report SQL procedure in the SQL Procedure Details window
Field Entry
Name node_report
Parameters in node_name Char(255)
This read-only entry in the Parameters list is constructed from entries made in the In/Out, Name, Data Type, and Length fields in the Parameters area. For example:
  • In/Out: in
  • Name: node_name
  • Data Type: Char
  • Length: 255
Actions 
declare
     tally_total integer;
begin
     set tally_total = 0;
     for each row tmprow in alerts.status where tmprow.Node = node_name
     begin
     set tally_total = tally_total + tmprow.Tally;
     end;
     write into node_report_file values ( 'Total tally for node ', node_name,
 ' = ', tally_total );
end

The SQL command to create the node_report_file ObjectServer file and the full SQL text of the same node_report procedure is as follows:

create file node_report_file '/tmp/node_report';

create procedure node_report( node_name char(255) )
declare
     tally_total integer;
begin
     set tally_total = 0;
     for each row tmprow in alerts.status where tmprow.Node = node_name
     begin
     set tally_total = tally_total + tmprow.Tally;
     end;
     write into node_report_file values ( 'Total tally for node ', node_name,
 ' = ', tally_total );
end;