DB Trigger Optmimization

/* Trigger: DB Full statement with Stored Procedure Parameters
   Description: Stores the full DB statement instead of just the method
   and also pulls parameters that are passed with stored procedures or dynamic SQL
   Event: DB_REQUEST
*/
if (event == 'DB_REQUEST'){
var stmt = DB.statement || DB.procedure;
var sp_params_string = "";
var x, obj;

if (stmt != null) {
    if (DB.statement){
  // restrict statement length to 1024 bytes
//        if (stmt.length >1024) {
//        stmt = stmt.substr(0,1024);
 //       }
   // remove initial newline if it exists
        if (stmt.charAt(0) == '\n' ) {
        stmt=stmt.slice(1);
        }
   }
    else if (DB.procedure){
    //if it's a stored procedure, capture the passed parameters
      var sp_params = DB.params;
           // DB.params is an array of objects, so we need to extract them
           // for conversion to strings to display
           for (var i = 0; i < sp_params.length; i++){
           obj = sp_params[i];
              for (x in obj);{
              sp_params_string += obj[x] + ", ";
              }
           }
      }
Flow.store.db_stmt=stmt;
Flow.store.ms_params=sp_params_string;
// debug("Statement or procedure: " + stmt + " Parameters: " + sp_params_string);
}
}
/* Trigger: Detail for slow SQL processing times
   Description: Adds sample set and counts for DB statements
   and stored procedures (with parameters) that have processing times > rspTimeLimit
   Event: DB_RESPONSE
*/

if (event == 'DB_RESPONSE'){
var rspTimeLimit = 500; //processing time limit in ms
var stmt = Flow.store.db_stmt;
var params = Flow.store.ms_params;
var clientAddress = Flow.client.ipaddr;
var date = new Date();

if (DB.tprocess > rspTimeLimit) {
  var timestamp = date.toLocaleTimeString();
   var statname = timestamp+":"+clientAddress+":"+DB.user+":"+stmt + " : " + params;
   Device.metricAddCount("db_slow_response", 1);
   Device.metricAddDetailCount("db_slow_response_detail",statname,1);
   Device.metricAddSampleset("db_slow_rsp_ss", DB.tprocess);
   Device.metricAddDetailSampleset("db_slow_rsp_ss_detail",statname,DB.tprocess);
}
}
// For every database request, capture the full query
// Query will be stored in the Flow store until the response
if ( event == "DB_REQUEST" ) {
   // var dbquery = DB.procedure || DB.statement;
     // if ( dbquery ) {
        Flow.store.procedure = DB.procedure;
        Flow.store.statement = DB.statement;
        Flow.store.table = DB.table;
        Flow.store.dbuser = DB.user;
        Flow.store.method = DB.method;
        Flow.store.params = DB.params;
    }

// On response, check for an error condition, assemble a custom metric
// Overload the metric key with Server IP, Client IP, Query & Error
// Writes to the Network object to capture all errors across devices in one place
 else if ( event == "DB_RESPONSE" ){
   var error = DB.error;
        if ( DB.error== null) {
        return;
        
             
        //if ( Flow.store.query ) {
         //   dbquery = Flow.store.query;
        //} else {
          //  dbquery = "Query not available";
        }

        var key = " 'Server IP:' " + Flow.server.ipaddr
            + " 'DBUser:' " + Flow.store.dbuser
            + " 'Client IP:' " + Flow.client.ipaddr
            + " 'Client Port:' " + Flow.client.port
            + " 'Method:' " + Flow.store.method
            + " 'Params:' " + Flow.store.params
            + " 'Procedure:' " + Flow.store.procedure
            + " 'Statement:' " + Flow.store.statement
            + " 'Table:' " + Flow.store.table       
            + " 'Error:' " + error;
            
        Device.metricAddCount("db_query_error", 1);
        Device.metricAddDetailCount("db_query_error_detail", key, 1);
    }

I am trying to get all my DB monitoring done within these two triggers. I would like any help optimizing them for trigger load and usage. Thanks in advance…
Pete

Hi Pmorse,
The baseline the current performance is a good starting point. Have you made note of :

  1. Current Trigger load from the system page
  2. Number of exceptions if any and its frequency
  3. Number of DB query per sec or minute ( Statement/min and Procedure/min)
  4. Number of performance cycles used
    thanks

I combined your triggers into one

// For every database request, capture the full query
// Query will be stored in the Flow store until the response
if (event == "DB_REQUEST") {
    Flow.store.procedure = DB.procedure;
    Flow.store.statement = DB.statement;
    Flow.store.table = DB.table;
    Flow.store.dbuser = DB.user;
    Flow.store.method = DB.method;
    if (DB.params && DB.params.length > 0) {
        for (var i = 0; i < DB.params.length; i++) {
            Flow.store.params += DB.params[i].name + '=' + DB.params[i].value + ',';
        }
    }
}

// On response, check for an error condition, assemble a custom metric
// Overload the metric key with Server IP, Client IP, Query & Error
// Writes to the Network object to capture all errors across devices in one place
else if (event == "DB_RESPONSE") {
    // Look for slow response
    if (DB.tprocess > 500) {
        var statement = Flow.store.statement || Flow.store.procedure || Flow.store.method;
        if (statement.charAt(0) == '\n') {
            statement = statement.slice(1);
        }
        var statname = (new Date()).toLocaleTimeString() + ":" + Flow.client.ipaddr + ":" + Flow.store.dbuser + ":" + statement + ":" + Flow.store.params;
        Device.metricAddCount("db_slow_response", 1);
        Device.metricAddDetailCount("db_slow_response_detail", statname, 1);
        Device.metricAddSampleset("db_slow_rsp_ss", DB.tprocess);
        Device.metricAddDetailSampleset("db_slow_rsp_ss_detail", statname, DB.tprocess);
    }
    // Look for errors
    if (DB.error) {
        var key = " 'Server IP:' " + Flow.server.ipaddr
            + " 'DBUser:' " + Flow.store.dbuser
            + " 'Client IP:' " + Flow.client.ipaddr
            + " 'Client Port:' " + Flow.client.port
            + " 'Method:' " + Flow.store.method
            + " 'Params:' " + Flow.store.params
            + " 'Procedure:' " + Flow.store.procedure
            + " 'Statement:' " + Flow.store.statement
            + " 'Table:' " + Flow.store.table       
            + " 'Error:' " + DB.error;
        Device.metricAddCount("db_query_error", 1);
        Device.metricAddDetailCount("db_query_error_detail", key, 1);
    }
}

I’ve assigned this trigger to database servers, and get the following message in the runtime log:

Line 23: Uncaught TypeError: Cannot read property ‘charAt’ of null

What’d I do wrong? This is line 23:

 if (statement.charAt(0) == '\n') {  

Thanks

In your case, the DB.statement and DB.procedure was null. Usually you will want to look for statement or procedure or method; one of those should be non-null. I’ve adjusted the script above to include DB.method, but I would suggest tweaking this trigger to suit your own needs, as this was intended for pmorse.

Thanks. I’m very green when it comes to DB traffic and triggers. I found this when I heard from my DBA that he wanted to see queries, who created them, where they came from and the time they took to process. I thought the combined trigger looked applicable to this need. Is there something better I should start with?

No, this is a good starting point, I was just suggesting you tweak it to be more generic for your needs. Anyway, good luck and hope I’ve helped.
.