Cleaning up expired One-Time Maintenance Windows in Impact MWM

Body

One thing that is not currently catered for in the MWM solution is the automatic deletion of expired MWM Windows.

Given that MWM is controlled through the GUI interface, the prescribed mechanism for deleting expired windows is also through the GUI.  However, if many windows are left in the ImpactDB, over time it can lead to performance issues in the MWM GUI.  It can also lead to the MWM policy taking longer to run. 

To automatically clean up these MWM windows you can create a policy, similar to the REPORT_PurgeData policy, to clean up the expired entries from the MM_WINDOWS table in the ImpactDB. 

Here is an example of such a policy, that I created:

// MWM_PurgeOneTime 
// NOTE: This is a sample policy which deletes old MWM One-Time Windows.
// Please tune the NUM_DAYS parameters according to your deployment.
// You can use a PolicyActivator service to trigger this policy at regular
// intervals.

// NUM_DAYS is the number of days since the 
// One-Time window expired. Default value is 7,  
// where windows that expired more than 7 days ago are deleted.

Log( "MWM_PurgeOneTime - Start policy" );
 
NUM_DAYS = 7;

// SQL to select rows from MM_WINDOWS where 
// One-Time Window end time (OT_ENDTIME)
// is older than the current time minus NUM_DAYS.
SELECT_SQL="SELECT FILTERSTAT, OT_STARTTIME, OT_ENDTIME, TIMEZONE FROM IMPACT.MM_WINDOWS WHERE WINTYPE=1 AND OT_ENDTIME < {fn TIMESTAMPADD(SQL_TSI_DAY, -"+NUM_DAYS+", CURRENT_TIMESTAMP)}";
// Run the SQL
Windows2Delete = DirectSQL('ImpactDB', SELECT_SQL, false);
Counter=0;
While( Counter < Num ) {
  log( Windows2Delete[Counter].FILTERSTAT + "  " + Windows2Delete[Counter].OT_STARTTIME + "  " + Windows2Delete[Counter].OT_ENDTIME + "  " + Windows2Delete[Counter].TIMEZONE );
  Counter=Counter+1;
} 

// SQL to delete rows from MM_WINDOWS where 
// One-Time Window end time (OT_ENDTIME)
// is older than the current time minus NUM_DAYS.
DELETE_SQL="DELETE FROM IMPACT.MM_WINDOWS WHERE WINTYPE=1 AND OT_ENDTIME < {fn TIMESTAMPADD(SQL_TSI_DAY, -"+NUM_DAYS+", CURRENT_TIMESTAMP)}";
// Run the SQL
DeletedWindows = DirectSQL('ImpactDB', DELETE_SQL, false);
Log( "Deleted " + Num + " One-Time Maintenance Windows that expired more than " + NUM_DAYS + " days ago." );

// Clean up MM_EXTRAINFO removing entries without a corresponging entry in MM_WINDOWS
DELETE_EXTRAINFO_SQL = "DELETE FROM IMPACT.MM_EXTRAINFO WHERE MWID NOT IN (SELECT MWID FROM IMPACT.MM_WINDOWS)";
// Run the SQL
DeletedExtrainfo = DirectSQL('ImpactDB', DELETE_EXTRAINFO_SQL, false);
Log( "Deleted " + Num + " entries from MM_EXTRAINFO with no corresponding entry in MM_WINDOWS.");

Log( "MWM_PurgeOneTime - End policy" );
 
// End

You can adjust the NUM_DAYS value depending on how long you want to keep expired windows for and you can run the policy manually or periodically from a Policy Activator.