SLA Trend collection rule

SCDPM.SLATrendCollection.Rule (Rule)

Knowledge Base article:

Element properties:

Member Modules:

Source Code:

<Rule ID="SCDPM.SLATrendCollection.Rule" Enabled="true" Target="DPM!Microsoft.SystemCenter.DataProtectionManager.2011.Library.DPMServer" ConfirmDelivery="true" Remotable="true" Priority="Normal" DiscardLevel="100">

  <Category>EventCollection</Category>

  <DataSources>

    <DataSource ID="DS" TypeID="Windows!Microsoft.Windows.TimedScript.EventProvider">

      <IntervalSeconds>21600</IntervalSeconds>

      <SyncTime/>

      <ScriptName>CollectSLATrend.js</ScriptName>

      <Arguments/>

      <ScriptBody><Script>

			var SCRIPT_NAME = "CollectSLATrend.js";

var SEVERITY_ERROR = 1;

var SEVERITY_WARNING = 2;

var SEVERITY_INFO = 4;

var EVENT_ID = 90100;

var ARGUMENT_COUNT = 0;

var SCRIPT_FREQUENCY = 24;

var MAX_ROWS = 1000;



if (WScript.Arguments.length != ARGUMENT_COUNT) {

    WScript.Quit(0);

}



var DPM_INSTALL_REGKEY = "HKLM\\SOFTWARE\\Microsoft\\Microsoft Data Protection Manager\\Setup\\InstallPath";

var logFilePath = "";

var dpmNotInstalled = false;

var useRowNum = false;

var rowsProcessed = 0;

var totalRowsFetched = 0;

var wsh = new ActiveXObject("WScript.Shell");



try

{

	logFilePath = wsh.RegRead(DPM_INSTALL_REGKEY);

	if (logFilePath.substr(logFilePath.length - 1) != "\\") logFilePath = logFilePath + "\\";

	logFilePath = logFilePath + "Temp\\" + SCRIPT_NAME + ".log";

}

catch (err)

{

	// DPM install path not found. Write to %TEMP%;

	var tempDir = wsh.ExpandEnvironmentStrings("%TEMP%");

	if (tempDir.substr(tempDir.length - 1) != "\\") tempDir = tempDir + "\\";

	logFilePath = tempDir + SCRIPT_NAME + ".log";

	dpmNotInstalled = true;

}



var fso  = new ActiveXObject("Scripting.FileSystemObject"); 

var fh = null;

try

{

	fh = fso.CreateTextFile(logFilePath, 2, true);

}

catch (err)

{

	//Ignore

}



function Trace(fileH, str) {

var traceDate = new Date();

if(fileH != null) fileH.WriteLine(traceDate + ": " + str); 

}



if (dpmNotInstalled)

{

	Trace(fh, "DPM install path not found in registry. Is DPM installed?");

	WScript.Quit(0);

}

var momApi = new ActiveXObject("MOM.ScriptAPI");



var lastFetchTime = new Date()

var fetchUptoTime = new Date();

var regPath = momApi.GetScriptStateKeyPath(SCRIPT_NAME);

var regPathFetchTime = "HKLM\\" + regPath + "\\LastFetchTime";

var regPathRowsProcessed = "HKLM\\" + regPath + "\\RowsProcessed";

var regPathTotalRowsFetched = "HKLM\\" + regPath + "\\TotalRowsFetched";

try

{

	var lastFetchTimeStr = wsh.RegRead(regPathFetchTime)

	lastFetchTime = new Date(lastFetchTimeStr)

}

catch (err)

{

	// Registry entry does not exist yet. Start time is current time minus some hours

	//JScript can handle -ve value for hours

	lastFetchTime.setHours(lastFetchTime.getHours() - SCRIPT_FREQUENCY);

}



//If lastFetchTime is too old, reset it to current time

var oldDateTime = new Date();

oldDateTime.setDate(oldDateTime.getDate() - 30); //30 days old. -ve values are handled by setDate

if (lastFetchTime &lt; oldDateTime)

{

	lastFetchTime = new Date();

	lastFetchTime.setHours(lastFetchTime.getHours() - SCRIPT_FREQUENCY)

}



Trace(fh, "lastFetchTime = " + lastFetchTime.toUTCString());



try

{

	var rowsProcessedStr = wsh.RegRead(regPathRowsProcessed);

	var totalRowsFetchedStr = wsh.RegRead(regPathTotalRowsFetched);

	rowsProcessed = parseInt(rowsProcessedStr);

	totalRowsFetched = parseInt(totalRowsFetchedStr);

}

catch (err)

{

	// Registry entry does not exist yet. Set values to 0

	rowsProcessed = 0;

	totalRowsFetched = 0;

}



function getValue(str) {

if(str==null) str="";

return str;

}



var connString = "";

var lastFetchSQLTime = "";

try

{

    var sqlServer = wsh.RegRead("HKLM\\SOFTWARE\\Microsoft\\Microsoft Data Protection Manager\\DB\\SqlServer");

	var sqlInstance = wsh.RegRead("HKLM\\SOFTWARE\\Microsoft\\Microsoft Data Protection Manager\\DB\\InstanceName");

    var dbName = wsh.RegRead("HKLM\\SOFTWARE\\Microsoft\\Microsoft Data Protection Manager\\DB\\DatabaseName");

	

	if (sqlInstance.toUpperCase() == "MSSQLSERVER")

	{

		connString = "Data Source=" + sqlServer + ";Initial Catalog=" + dbName + ";Integrated Security=SSPI;Provider=SQLNCLI11";

	}

	else

	{

		connString = "Data Source=" + sqlServer + "\\" + sqlInstance + ";Initial Catalog=" + dbName + ";Integrated Security=SSPI;Provider=SQLNCLI11";

	}

	Trace(fh, "connString = " + connString);

	

	var rs = new ActiveXObject("ADODB.Recordset");

    var connection = new ActiveXObject("ADODB.Connection");

    connection.Open(connString);

	

	lastFetchSQLTime = "'" + lastFetchTime.getUTCFullYear() + "-" + (lastFetchTime.getUTCMonth() + 1) + "-" + lastFetchTime.getUTCDate() + 

				" " + lastFetchTime.getUTCHours() + ":" + lastFetchTime.getUTCMinutes() + ":" + lastFetchTime.getUTCSeconds() + "'";

	//If the number of records for time = lastFetchTime is &gt; MAX_ROWS, need to batch

	var countquery = "SELECT COUNT(*) FROM dbo.vw_RPT_SLATrend WHERE CreationTime = " + lastFetchSQLTime;

	rs.Open(countquery, connection);

	rs.movefirst;

	var recordsFetched = rs.fields.Item(0).Value;

	if (recordsFetched &gt;= MAX_ROWS)

	{

		useRowNum = true;

		if (recordsFetched != totalRowsFetched)

		{

			//Detected that the number of rows for the given DateTime has changed. Reset row count

			totalRowsFetched = recordsFetched;

			rowsProcessed = 1;

		}

	}

	rs.close;

	connection.close;

}

catch (e) {

	Trace(fh, "Exception in fetching MaxTime = " + e.message);

    momApi.LogScriptEvent(SCRIPT_NAME, EVENT_ID, SEVERITY_ERROR, e.message);

}



Trace(fh, "rowsProcessed = " + rowsProcessed);

Trace(fh, "totalRowsFetched = " + totalRowsFetched);

Trace(fh, "useRowNum = " + useRowNum);



var pBag = momApi.CreatePropertyBag();

try {

    var connection = new ActiveXObject("ADODB.Connection");

    connection.Open(connString);

	

	var rs = new ActiveXObject("ADODB.Recordset");

	var query = "SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY CreationTime, DatasourceId) as Row    \

	      , DPMServerName    \

      , SMStatsID   \

      , DatasourceID   \

      , DatasourceName   \

      , ProtectionServer   \

      , ProtectionGroupID   \

      , ProtectionGroupName   \

      , CreationTime   \

      , DiskRecoveryPointAvailable   \

      , TapeRecoveryPointAvailable   \

      , CloudRecoveryPointAvailable   \

      , SLA   \

      , ScheduleType   \

	      FROM  vw_RPT_SLATrend WHERE CreationTime &gt;= " + lastFetchSQLTime +

		") As T WHERE Row BETWEEN " + rowsProcessed + " AND " + (rowsProcessed + MAX_ROWS);



	Trace(fh, query);

    rs.Open(query, connection);

   

	/* columns[] array stores the name of columns which will be present in views */

	/* THIS REPRESENTATION MAKES THE TASK OF CHANGING COLUMNS NAME EASY */

	

	var columns = ["DPMServerName"

      ,"SMStatsID"

      ,"DatasourceID"

      ,"DatasourceName"

      ,"ProtectionServer"

      ,"ProtectionGroupID"

      ,"ProtectionGroupName"

      ,"CreationTime"

      ,"DiskRecoveryPointAvailable"

      ,"TapeRecoveryPointAvailable"

      ,"CloudRecoveryPointAvailable"

      ,"SLA"

      ,"ScheduleType"] ;

	  

	 var noOfColumns = columns.length;

	var count = 0;

	var maxDate = new Date();

    while(!rs.eof)

    {

	        pBag = null; 

        var pBag = momApi.CreatePropertyBag();

		

       for(i=0;i&lt;noOfColumns;i++)

		pBag.AddValue(columns[i],getValue(rs.fields(i+1).Value));



		

        

        momApi.AddItem(pBag);

		//Rowset does not support fetching backward so keep collecting fetchUpto time here

		maxDate = new Date(getValue(rs.fields(8).Value));

		count++;

        rs.movenext;

    }

	Trace(fh, count + " records fetched");

	

	//Get new last fetch time

	if (count != 0)

	{

		var utcDate = new Date();

		utcDate.setUTCFullYear(maxDate.getFullYear());

		utcDate.setUTCMonth(maxDate.getMonth());

		utcDate.setUTCDate(maxDate.getDate());

		utcDate.setUTCHours(maxDate.getHours());

		utcDate.setUTCMinutes(maxDate.getMinutes());

		utcDate.setUTCSeconds(maxDate.getSeconds());

		utcDate.setUTCMilliseconds(maxDate.getMilliseconds());

		fetchUptoTime = new Date(utcDate.toUTCString());

	}

	Trace(fh, "fetchUptoTime = " + fetchUptoTime.toUTCString());

				

    rs.close;  

    connection.close;

	

	if (count == 0)

	{

		WScript.Quit(0);

	}

	

	//Update last fetched time and rowNum in registry

	if (useRowNum)

	{

		wsh.RegWrite(regPathRowsProcessed, (rowsProcessed + MAX_ROWS).toString(), "REG_SZ");

		wsh.RegWrite(regPathTotalRowsFetched, totalRowsFetched.toString(), "REG_SZ");

	}

	else

	{

		wsh.RegWrite(regPathFetchTime, fetchUptoTime.toUTCString(), "REG_SZ");

		wsh.RegWrite(regPathRowsProcessed, "1", "REG_SZ");

		wsh.RegWrite(regPathTotalRowsFetched, "0", "REG_SZ");

	}

}

catch (e) {

	Trace(fh, "Exception = " + e.message);

    momApi.LogScriptEvent(SCRIPT_NAME, EVENT_ID, SEVERITY_ERROR, e.message);

}

finally {

    momApi.ReturnItems();

}

</Script></ScriptBody>

      <TimeoutSeconds>240</TimeoutSeconds>

      <EventOriginId>$MPElement$</EventOriginId>

      <PublisherId>$MPElement$</PublisherId>

      <PublisherName>CustomScript</PublisherName>

      <Channel>CustomScript</Channel>

      <LoggingComputer>$Target/Property[Type="DPM!Microsoft.SystemCenter.DataProtectionManager.2011.Library.DPMServer"]/DPMServerName$</LoggingComputer>

      <EventNumber>555</EventNumber>

      <EventCategory>0</EventCategory>

      <EventLevel>4</EventLevel>

      <UserName/>

      <Params/>

    </DataSource>

  </DataSources>

  <WriteActions>

    <WriteAction ID="BulkWA1" TypeID="DPM.SLATrend.DW.WA.XmlBulkInsertModule" Target="MSDL!Microsoft.SystemCenter.DataWarehouseConnectorServer">

      <spname>SCDPM.SLATrendInsert</spname>

    </WriteAction>

  </WriteActions>

</Rule>