/*
Purpose:	This Active Script Active Monitor checks the data collected by the Interface Utilization Performance monitor 
		over a specified sample period for the polled device.  If any interface on that device has 
		an average (for the sample period) receieve or transmit utilization exceeding the specified 
		threshold, the monitor fails.
Usage:	Change the MAX_AVERAGE_PERCENT_THRESHOLD value to the maximum average receive and transmit threshold you desire.
	Change the SAMPLE_INTERVAL_MINUTES value to the maximum sample interval you desire.  Warning: arbitrarily increasing
	this value will increase the amount of time necessary to process the script, especially on devices with
	a large number of interfaces, or which have been collecting data for a long period or with a short polling interval.
	This script MUST complete within 60 seconds.
*/

// CHANGE THESE
var MAX_AVERAGE_PERCENT_THRESHOLD	= 50; // monitor will fail if either in or out usage greater than this
var SAMPLE_INTERVAL_MINUTES		= 60; // in minutes, increase with caution
// CHANGE THESE

// CONSTANTS
var INTERFACE_MONITOR_GUID = "3D199773-46EF-4CBB-B992-7076612C7668";
var INVALID_DATABASE_ID = -1;

// SCRIPT CLASSES - DO NOT MODIFY
function CWugDB()
{
	// public
	this.Failed = false;
	this.EOF = false; // preferred
	this.IsEOF = false;
	// ADODB uses EOF, not IsEOF, but don't make people remember

	// private, do not use
	this.m_oDb = Context.GetDB;
	this.m_oRecordset = null;
	this.m_sLastError = "";
}
CWugDB.prototype.GetDB = function ()
{
	if (this.m_oDb === null || typeof(this.m_oDb) === "unknown")
	{
		this.Failed = true;
		this.m_sLastError = "Unable to create WhatsUp Database connection.";
	}
	return;
}
CWugDB.prototype.Execute = function (sSql)
{
	var oRecordset = this.m_oDb.Execute(sSql); // returns an ADODB.Recordset object
	if (oRecordset.EOF) // no records in the record set
	{
		this.IsEOF = true;
		this.EOF = true;
		return;
	}
	else
		this.m_oRecordset = oRecordset;
	return;
}
CWugDB.prototype.GetValueAsString = function (sColumnName)
{
	var oRecordset = this.m_oRecordset;
	if (oRecordset === null)
	{
		this.Failed = true;
		this.m_sLastError = "RecordSet contains no data.";
		return null;
	}
	var oValue = oRecordset.Fields.Item(sColumnName).Value;
	var sValue = oValue.toString();
	return sValue;
}
CWugDB.prototype.GetValueAsNumber = function (sColumnName)
{
	var oRecordset = this.m_oRecordset;
	if (oRecordset === null)
	{
		this.Failed = true;
		this.m_sLastError = "RecordSet contains no data.";
		return null;
	}
	var oValue = oRecordset.Fields.Item(sColumnName).Value;
	var nValue = parseInt(oValue);
	if (isNaN(nValue))
	{
		this.Failed = true;
		this.m_sLastError = "Unable to parse number from " + sColumnName;
		return null;
	}
	return nValue;
}
CWugDB.prototype.MoveNext = function ()
{
	var oRecordset = this.m_oRecordset;
	if (!oRecordset.EOF)
	{
		oRecordset.MoveNext();
		if (oRecordset.EOF)
		{
			this.IsEOF = true;
			this.EOF = true;
		}
	}
	return;
}
CWugDB.prototype.GetError = function ()
{
	var sLastError = this.m_sLastError;
	this.Failed = false;
	this.m_sLastError = "";
	return sLastError;
}
CWugDB.prototype.IsValueNULL = function (sColumnName)
{
	var oRecordset = this.m_oRecordset;
	if (oRecordset === null)
	{
		this.Failed = true;
		this.m_sLastError = "RecordSet contains no data.";
		return null;
	}
	var bIsNull = false;
	var oValue = oRecordset.Fields.Item(sColumnName).Value;
	if (oValue == null)
	{
		bIsNull = true;
	}
	return bIsNull;
}
function CStatisticalInterface()
// interface object class
{
	this.StatisticalInterfaceIdentificationID	= -1;
	this.IfSpeedIn							= -1; // bits per second
	this.IfInOctets_Avg						= -1; // bytes * 8 = bits per second
	this.IfSpeedOut						= -1; // bits per second
	this.IfOutOctets_Avg					= -1; // bytes * 8 = bits per second
	this.IfIndex							= -1;
	this.IfDisplayName						= "";
//
	this.IfDescr							= "";
	this.IfInSpeedCustom					= -1;
	this.IfOutSpeedCustom					= -1;
}
// SCRIPT CLASSES - DO NOT MODIFY

// SCRIPT EXECUTION STARTS HERE
Main();

function Main()
{
	var oDb = new CWugDB();
	oDb.GetDB();
	if (oDb.Failed)
	{
		var sError = oDb.GetError();
		Context.SetResult(1, "Error: " + sError);
		return;
	}
	
	// Get the device ID
	var nDeviceID = Context.GetProperty("DeviceID");

	var sDeviceDisplayName = GetDeviceDisplayName(oDb, nDeviceID);
	Context.LogMessage("Checking Device: " + sDeviceDisplayName);

	// get the PivotID for the Interface Util. monitor on this device
	var nPivotStatisticalMonitorTypeToDeviceID = GetStatPivotIDForDevice(oDb, nDeviceID);
	if (nPivotStatisticalMonitorTypeToDeviceID === INVALID_DATABASE_ID)
	{
		Context.SetResult(1, "The Interface Utilization Performance Monitor is not enabled for this device.");
		return;
	}

	var arrInterfaces = _GetInterfaceData(oDb, nPivotStatisticalMonitorTypeToDeviceID, SAMPLE_INTERVAL_MINUTES);
	if (arrInterfaces.length < 1)
	{
		Context.SetResult(1, "No Interface Utilization records for any interfaces on this device within " + 
						"the " + SAMPLE_INTERVAL_MINUTES + " minute sample period.");
		return;
	}

	for (var item in arrInterfaces) // enumerate all the interfaces for the device
	{
		var oInterface = arrInterfaces[item];
		Context.LogMessage("Checking Interface [" + oInterface.IfDisplayName + " (" + oInterface.IfIndex + ")] on [" + sDeviceDisplayName + "]");

		var nAvgInPercentage = new Number();
		var nAvgOutPercentage = new Number();
		if (oInterface.IfSpeedIn > 0 && oInterface.IfSpeedOut > 0)
		{
			var nRawInPercentage = (oInterface.IfInOctets_Avg / oInterface.IfSpeedIn) * 100;
			nAvgInPercentage = nRawInPercentage.toFixed(2);

			var nRawOutPercentage = (oInterface.IfOutOctets_Avg / oInterface.IfSpeedOut) * 100;
			nAvgOutPercentage = nRawOutPercentage.toFixed(2);
		}
		else
		{
			nAvgInPercentage = 0
			nAvgOutPercentage = 0
		}

		Context.LogMessage("Interface [" + oInterface.IfDisplayName + " (" + oInterface.IfIndex + ")] at " + nAvgInPercentage + "% in.");
		Context.LogMessage("Interface [" + oInterface.IfDisplayName + " (" + oInterface.IfIndex + ")] at " + nAvgOutPercentage + "% out.");

		if (nAvgInPercentage > MAX_AVERAGE_PERCENT_THRESHOLD || nAvgOutPercentage > MAX_AVERAGE_PERCENT_THRESHOLD)
		{
			var bInAboveThreshold = false;
			var bOutAboveThreshold = false;
			if (nAvgInPercentage > MAX_AVERAGE_PERCENT_THRESHOLD)
				bInAboveThreshold = true;
			if (nAvgOutPercentage > MAX_AVERAGE_PERCENT_THRESHOLD)
				bOutAboveThreshold = true;
			
			var sResultText = new String();
			sResultText += "Error: Interface ";
			sResultText += "[" + oInterface.IfDisplayName + " (" + oInterface.IfIndex + ")] "
			if (bInAboveThreshold)
				sResultText += "at " + nAvgInPercentage + "% in utilization ";
			if (bInAboveThreshold && bOutAboveThreshold)
				sResultText += "and ";
			if (bOutAboveThreshold)
				sResultText += "at " + nAvgOutPercentage + "% out utilization ";
			sResultText += "of " + MAX_AVERAGE_PERCENT_THRESHOLD + "% allowed.";
			Context.SetResult(1, sResultText);
		}
		else
		{
			Context.SetResult(0, "All interfaces on " + sDeviceDisplayName + " are within limits.");
		} // if (nAvgInPercentage > MAX_AVERAGE_PERCENT_THRESHOLD || nAvgOutPercentage > MAX_AVERAGE_PERCENT_THRESHOLD)
	} // for (var item in arrInterfaces)

	return;
}

function GetDeviceDisplayName(oDb, nDeviceID)
{
	var sDeviceDisplayName = "";
	
	// get the Device's display name, should be only one
	var sSql = "SELECT Device.sDisplayName FROM Device WHERE nDeviceID = " + nDeviceID;

	oDb.Execute(sSql);
	if (!oDb.Failed && !oDb.EOF)
	{
		sDeviceDisplayName = oDb.GetValueAsString("sDisplayName");
	}
	
	return sDeviceDisplayName;
}

function GetStatPivotIDForDevice(oDb, nDeviceID)
{
	var nPivotStatisticalMonitorTypeToDeviceID = INVALID_DATABASE_ID;
	
	// Retrieve the nPivotStatisticalMonitorTypeToDeviceID, should be only one
	var sSql = "SELECT nPivotStatisticalMonitorTypeToDeviceID FROM PivotStatisticalMonitorTypeToDevice" + 
			" INNER JOIN StatisticalMonitorType ON PivotStatisticalMonitorTypeToDevice.nStatisticalMonitorTypeID = StatisticalMonitorType.nStatisticalMonitorTypeID" + 
			" WHERE PivotStatisticalMonitorTypeToDevice.nDeviceID = " + nDeviceID + 
			" AND StatisticalMonitorType.nCLSID = '" + INTERFACE_MONITOR_GUID + "'" + 
			" AND PivotStatisticalMonitorTypeToDevice.bEnabled = 1";

	oDb.Execute(sSql);
	if (!oDb.Failed && !oDb.EOF)
	{
		nPivotStatisticalMonitorTypeToDeviceID = oDb.GetValueAsNumber("nPivotStatisticalMonitorTypeToDeviceID");
	}

	return nPivotStatisticalMonitorTypeToDeviceID;
}

function _GetInterfaceData(oDb, nPivotStatisticalMonitorTypeToDeviceID, nSampleInterval)
{
	var arrInterfaces = new Array(); // create an array to hold the interfaces for this device

	var sSql = "" + 
	" SELECT" + 
	"	SII.nStatisticalInterfaceIdentificationID," + 
	"	MAX(SI.nIfSpeedIn) AS nIfSpeedIn," + 
	"	AVG(SI.nIfInOctets_Avg) * 8 AS nIfInOctets_Avg," + // speeds are in bps, need to multiply bytes * 8
	"	MAX(SI.nIfSpeedOut) AS nIfSpeedOut," + 
	"	AVG(SI.nIfOutOctets_Avg) * 8 AS nIfOutOctets_Avg," + 
	"	SII.nIfInSpeedCustom," +  
	"	SII.nIfOutSpeedCustom," +  
	"	sIfDisplayName, SII.sIfDescr," + // sIfDisplayName may be NULL on upgrade
	"	SII.nIfIndex" +  
	" FROM StatisticalInterface AS SI" + 
	" INNER JOIN StatisticalInterfaceIdentification AS SII ON" + 
	"	SI.nStatisticalInterfaceIdentificationID = SII.nStatisticalInterfaceIdentificationID" + 
	" WHERE SI.dPollTime > DATEADD(minute, -" + nSampleInterval + ", GETDATE())" + 
	"	AND SII.nPivotStatisticalMonitorTypeToDeviceID = " + nPivotStatisticalMonitorTypeToDeviceID + 
	"	AND nDataType = 1" + // only get 'raw' records
	" GROUP BY SII.nStatisticalInterfaceIdentificationID," + 
	"	SII.nIfInSpeedCustom," + 
	"	SII.nIfOutSpeedCustom," + 
	"	SII.sIfDisplayName," + 
	"	SII.sIfDescr," + 
	"	SII.nIfIndex";

	oDb.Execute(sSql);
	while (!oDb.EOF && !oDb.Failed)
	{
		oInterface = new CStatisticalInterface();
		oInterface.StatisticalInterfaceIdentificationID	= oDb.GetValueAsNumber("nStatisticalInterfaceIdentificationID");
		oInterface.IfInOctets_Avg					= oDb.GetValueAsNumber("nIfInOctets_Avg");
		oInterface.IfOutOctets_Avg					= oDb.GetValueAsNumber("nIfOutOctets_Avg");
		oInterface.IfIndex							= oDb.GetValueAsNumber("nIfIndex");

		// upgraders may have NULL sIfDisplayName values until a poll takes place
		if (oDb.IsValueNULL("sIfDisplayName"))
		{
			oInterface.IfDisplayName					= oDb.GetValueAsString("sIfDescr");
		}
		else
		{
			oInterface.IfDisplayName					= oDb.GetValueAsString("sIfDisplayName");
		}

		// check if the user created a custom in/out speed for the interface
		oInterface.IfSpeedIn						= oDb.GetValueAsNumber("nIfSpeedIn");
		if (!oDb.IsValueNULL("nIfInSpeedCustom"))
		{
			oInterface.IfSpeedIn					= oDb.GetValueAsNumber("nIfInSpeedCustom");
		}
		oInterface.IfSpeedOut						= oDb.GetValueAsNumber("nIfSpeedOut");
		if (!oDb.IsValueNULL("nIfOutSpeedCustom"))
		{
			oInterface.IfSpeedOut					= oDb.GetValueAsNumber("nIfOutSpeedCustom");
		}

		arrInterfaces.push(oInterface); // add interface to array
		oDb.MoveNext();
	}

	return arrInterfaces;
}