/* ======================================================================
JScript Source File -- Created with SAPIEN Technologies PrimalScript 4.0

NAME: SQLResponse.js
AUTHOR: Neale Brown , AKOS Technology Services
DATE  : 10/27/2006

COMMENT: This script is my first attempt at JScript and my first attempt
at something as complex as a SQL Response script.  I have probably missed
many things but I am sure someone will point it out. :)  
If you have any suggestions, feel free to a message on my blog at 
http://www.whiskeytango.name/
========================================================================= */

//map event types & numbers to friendly names
var EVENT_TYPE_SUCCESS = 0;
var EVENT_TYPE_ERROR = 1;
var EVENT_TYPE_WARNING = 2;
var EVENT_TYPE_INFORMATION = 4;
var EVENTLOG_AUDIT_SUCCESS = 8;
var EVENTLOG_AUDIT_FAILURE = 16; 

//map alert types & numbers to friendly names
var ALERT_SUCCESS = 10
var ALERT_INFORMATION = 20
var ALERT_WARNING = 30
var ALERT_ERROR = 40
var ALERT_CRITICAL_ERROR = 50
var ALERT_SECURITY_BREACH = 60
var ALERT_SERVICE_UNAVAILABLE = 70

/******************************************************************************
Name:		main
Purpose:	main routine of the progarm.  Makes the database calls and string
            comparisons.  If it runs into any errors, it calls the LogError
            function.
Parameters: 
		Conn - Connection string passed by MOM. This string can be provided
		          by an UDL file.  
		Threshold - time limit for the SQL transction. If execution time 
		               exceeds this limit, it generate an alert.
		Command - SQL Statement
		Column - What column to retrieve data from
		CompareString - What string to compare the results to. If this fails, 
					 the script will generate an event.
		logSuccessful - This is for troubleshooting purposes.  If this is
							 enabled, then you will recieve alerts and events 
							 on successful execution of the script.
		Server - The server that will serve as an proxy for the script.
		            Strictly for alerting purposes.
** The column name and Compare fields are primarily used for string matching of
   SQL results. If you do not want that, then at the minimum strCompare needs to
   be blank and strColumnName can be as well. If you want string match testing, 
   then both fields need to be populated.   
Returns:  nothing
******************************************************************************/
function main()
{
	//Retrieve MOM variables
	var strConn = ScriptContext.Parameters.Get("conn");
	var intThreshold = ScriptContext.Parameters.Get("threshold");
	var strCommand = ScriptContext.Parameters.Get("command");
	var strColumnName = ScriptContext.Parameters.Get("column");
	var strCompare = ScriptContext.Parameters.Get("compareString");
	var blnSuccessfulAlert = ScriptContext.Parameters.Get("logSuccessful");
	var strServer = ScriptContext.Parameters.Get("Server");
	
	//declare variables
	var total, start, end, str, strSQLResponse;
	
	try 
    { 
		//open a connection to the database
		var objConn = new ActiveXObject("ADODB.Connection");
		objConn.Open(strConn);
	}
	catch (ex)
    {
		//log error on failure
		LogError(ex.number, ex.description, "");
		return;    	
    }
    
    //retrieve timer data by calling the timer function
    start = timer();
    
    try
    {	
		//execute command from MOM parameter
		var objRS = new ActiveXObject("ADODB.Recordset");
		objRS.Open(strCommand, objConn);	
	}
	catch (ex)
    {
		//log error on failure
		LogError(ex.number, ex.description, "");
		return;    	
    }
   	
   	//If there are no data, more than likely there is something wrong with 
   	//the SQL query.
   	if (objRS.EOF)
   	{
   		WScript.Echo("error: " & ex.number);
   		LogError("61001", "", "");
   		return;
   	}
   	
   	//Checks to see if strCompare is populated. If not, then it skips
   	//a string check and calls the database check good.
   	if (strCompare == null || strCompare == "")
   	{
   		//only logs event if this MOM parameter is set to 1
   		if (blnSuccessfulAlert == "1")
   		{	
   			LogError("62002", "", "");
   		}
   	}	
   	else
   	{
   		 try
   		{   	
	   		//Retrieves data from the MOM Parameter
	   		strSQLResponse = objRS(strColumnName);
   		}
   		catch (ex)
	   	{
	   		//log error on failure
			LogError("61001", "", "");
   			return;
   		}

		//Sting comparison against retuned data
   		if (strSQLResponse == strCompare)
		{
			//only logs event if this MOM parameter is set to 1
			if (blnSuccessfulAlert == "1")
			{	LogError("63002", "", "");	}
		}
		else
		{	
			//log error on failure
			LogError("63001", strSQLResponse, strCompare);
			return;
		}
	}
	//Retrieve time information from Timer function
	end = timer();	
	//Calculate time
	total = timercalc(start, end);
	
	//Enter perf data
	CreatePerfData("Response Time Monitoring","SQL Response Time",strServer,total);
	
	if (total > intThreshold)
	{
		//if total exceeds threshold, create alert
		LogError("63005",intThreshold,total);
	}
	else
	{
		//only logs event if this MOM parameter is set to 1
		if (blnSuccessfulAlert == "1"){
			str = "Elasped time: " + total + " ms.";
		}
	}
	
	//Close Connections
	objRS.close;
	objConn.close;
}

/******************************************************************************
Name:		LogError
Purpose:	creates descriptions based on passed error parameters and creates
			either an event or an alert
Parameters: 
		intErrorNumber - Error number passed by the try/catch routine or 
						 an assigned number 
		strErrorDesc - Description of error
		str SearchSting - Search string used for optional success/failure 
		                  measurement.
		*** In some cases the parameters can be used to pass data that it was 
		    not intended for. Yes, I am lazy. :)
Returns:  nothing
******************************************************************************/
function LogError(intErrorNumber, strErrorDesc, strSearchString)
{
	//declare variables
	var str;
	var strName;
	
	//Creating alert messages before we pass them to the GenereateAlert function.
	
	if (intErrorNumber == "-2147467259")
	{
		str = "The server is not available.\n";
		str = str + "Error Number: " + intErrorNumber + "\n";
		str = str + "Error: " + strErrorDesc + "\n";
		strName = "SQL Response Script";
		GenerateEvent("75001", EVENT_TYPE_ERROR, str)
	}
	else if (intErrorNumber == "-2147217843")
	{
		str = "Login failed for the user. Please check username and password.\n";
		str = str + "Error Number: " + intErrorNumber + "\n";
		str = str + "Error: " + strErrorDesc + "\n";
		strName = "SQL Response Script";
		GenerateEvent("75001", EVENT_TYPE_ERROR, str)
	}
	else if (intErrorNumber == "61001")
	{
		str = "The SQL statement did not execute correctly. Please review your SQL query.\n";
		strName = "SQL Response Script";
		GenerateEvent("75001", EVENT_TYPE_ERROR, str)
	}
	else if (intErrorNumber == "62002")
	{
		str = "The SQL statement executed successfully.\n";
		strName = "SQL Response Script";
		GenerateEvent("75003", EVENT_TYPE_SUCCESS, str)
	}
	else if (intErrorNumber == "63001")
	{
		str = "A search string string match was not found.\n";
		str = str + "Search String: " + strSearchString + "\n";
		str = str + "Returned String: " + strErrorDesc;
		strName = "SQL Response Script";
		GenerateEvent("75001", EVENT_TYPE_ERROR, str)
	}
	else if (intErrorNumber == "63002")
	{
		str = "The SQL statement executed successfully and search string match was found.\n";
		strName = "SQL Response Script";
		GenerateEvent("75003", EVENT_TYPE_SUCCESS, str)
	}
	else if (intErrorNumber == "63005")
	{
		str = "The SQL statement has exceeded the threshold of " + strErrorDesc + " (ms).\n";
		str = str + "The elasped time is " + strSearchString +" (ms).";
		strName = "SQL Response Script";
		GenerateAlert(ALERT_WARNING,str,strName);
	}
	
	//catch all
	else{
		str = "General script failure caused early or abnormal termination.\n";
		str = str + "Error Number: " + intErrorNumber + "\n";
		str = str + "Error Description " + strErrorDesc + "\n";
		strName = "SQL Response Script";
		GenerateEvent("75002", EVENT_TYPE_WARNING, str)
	}
}

/******************************************************************************
Name:		timer
Purpose:	returns a time value for later calculation
Parameters: nothing
Returns:  time value using the date object
******************************************************************************/
function timer()
{
	//gets the time and returns it.
	intTime = new Date();  
	return(intTime.getTime());
}

/******************************************************************************
Name:		timercalc
Purpose:	calculates the time from time values returned by the timer function
Parameters:
	start - Start Time
	end - end time
Returns:  total time
******************************************************************************/
function timercalc(start, end)
{	
	//Converts timer information into seconds
	//return((end - start) / 1000);
	return((end - start));
}

/******************************************************************************
Name:		GenerateEvent
Purpose:	Raises an event
Parameters:
	EventNo - Number to alert identity for Mom
	EventType - Severity Level of event	
	Message - Event Description
Returns: nothing
******************************************************************************/
function GenerateEvent(EventNo, EventType, Message)
{
    var MyEvent = ScriptContext.CreateEvent();
    MyEvent.Message = Message;
    MyEvent.EventNumber = EventNo;
    MyEvent.EventType = EventType;
    MyEvent.EventSource = "Custom SQL Response Time";

    ScriptContext.Submit(MyEvent);
}

/******************************************************************************
Name:		CreateAlert
Purpose:	Raises an alert
Parameters:
	iSeverity - The severity of the alert.
	strDescription - The alert description.
	strName - The name of the alert.
Returns: nothing
******************************************************************************/
function GenerateAlert(iSeverity, strDescription, strName)
{	 
	 var objAlert = ScriptContext.CreateAlert();
     objAlert.Name = strName;
	 objAlert.AlertSource = "Custom HTTP Synthetic Transaction Script";
	 objAlert.Description = strDescription;
	 objAlert.AlertLevel = iSeverity;
	 ScriptContext.Submit(objAlert); 
}

/*******************************************************************************
Name:		CreatePerfData
Purpose:	Creates a performance object to display response time in Performance View
Parameters:
	strObjectName - Name of the script
	strCounterName - Name for the performance information logged
	strInstanceName - Where the script will be running
	numValue - Performance number to submit to MOM 
Returns: nothing
****************************************************************************** */
function CreatePerfData(strObjectName,strCounterName,strInstanceName,numValue)
{
	var objPerfData = ScriptContext.CreatePerfData();
	objPerfData.ObjectName = strObjectName;
	objPerfData.CounterName =strCounterName;
	objPerfData.InstanceName = strInstanceName;
	objPerfData.Value = numValue;
	ScriptContext.Submit(objPerfData);
}