IBM Support

Create maintenance windows (MWM) with a Netcool/Impact policy

General Page

This guide describes the process of creating maintenance windows in Netcool/Impact with SQL queries instead of the MWM GUI.
Netcool/Impact stores maintenance windows manager (MWM) entries in the ImpactDB derby database under the mm_windows and mm_extrainfo tables.

When creating a maintenance window in the Netcool/Impact GUI, the window information is forwarded to the MWM_Add1TWin (one-time) or MWM_AddRWin (recurring) policy, which generates the appropriate SQL statements to populate the database.

This guide explains the process of capturing and re-using the SQL queries to create your own custom maintenance windows outside of the Netcool/Impact GUI.
 
Schema
The following tables describe the schemas for the MWM tables in Impact 7.1.0.29.

Table 1. SQL Schema for mm_windows
 
Table 1. SQL Schema for mm_windows
Field Type Description Since
MWID INTEGER UNIQUE Key Field. An incremental number assigned to each MWM entry. 7.1.0.0
FILTERSTAT VARCHAR(1024) An SQL query that represents the set of suppression filters. 7.1.0.0
WINTYPE INTEGER The MWM type where 1=One Time, 2=Day of Week, 3=Day of Month, 4=Nth Day of Week in Month, 5=Weekly. 7.1.0.0
OT_STARTTIME TIMESTAMP Start time for a one-time window in Timestamp format. 7.1.0.0
OT_ENDTIME TIMESTAMP End time for a one-time window in Timestamp format. 7.1.0.0
EOT_STARTTIME INTEGER Start time for a one-time window in epoch format. 7.1.0.0
EOT_ENDTIME INTEGER End time for a one-time window in epoch format. 7.1.0.0
R_STARTTIME TIME Start time for a recurring window. 7.1.0.0
R_ENDTIME TIME End time for a recurring window. 7.1.0.0
DAYSOFWEEK VARCHAR(64) A pipe-delimited list of weekdays for a recurring window. For example, "Sun|Mon|Tue" denotes the Sunday, Monday and Tuesday of each week. 7.1.0.0
DAYSOFMONTH VARCHAR(128) A pipe-delimited list of days in the month for a recurring window. For example, "|7|14|" would denote day 7 and day 14 of each month. 7.1.0.0
NWEEKDAYOFMONTH VARCHAR(512) A pipe-delimited list of recurring nth day in a month for a recurring window. For example, "|firstMon|firstThu|thirdMon|thirdThu|" would denote the first Monday, first Thursday, third Monday, and third Thursday of each month. 7.1.0.0
ACTIVATED INTEGER When a maintenance window is active, this field is set to 1. 7.1.0.0
TIMEZONE VARCHAR(64) The timezone for the MWM entry. For example, "GMT". 7.1.0.0
M_STARTDAY VARCHAR(3) The start day for a weekly window. For example, "Tues" denotes that the weekly window starts on Tuesday. 7.1.0.22
M_ENDDAY VARCHAR(3) The end day for a weekly window. For example, "Wed" denotes that the weekly window ends on a Wednesday. 7.1.0.22
M_DOW VARCHAR(27) A pipe-delimited list, which tracks the weekdays covered by a weekly window. 7.1.0.22
MWM_DESCRIPTIONS VARCHAR(4096) A base64 encoded JSON string, which holds the description text of a window. The decoded JSON string uses the format: {"description":"test here"} 7.1.0.27
 

Each maintenance window entry in the mm_windows table has a corresponding entry in the mm_extrainfo table linked by the MWID key field.

Table 2. The SQL schema for mm_extrainfo
 
Table 2. The SQL schema for mm_extrainfo
Field Type Description Since
MWID INTEGER Key Field. The numerical identifier for each MWM entry. This identifier is cross-referenced from the MWM entry in the mm_windows table. 7.1.0.0
USERNAME VARCHAR The username that created the MWM entry. 7.1.0.0

 
SQL query breakdown

The following example describes the creation of a MWM entry in the ImpactDB database for Impact 7.1.0.29.
Create a row in MM_WINDOWS for a one-time window. Declare the SQL filter in FILERSTAT and set the WINTYPE to 1 along with the appropriate times and timezone information.

Note: The SQL filter generated by the MWM GUI only supports simple AND condition structures. You can bypass those restrictions and declare a custom SQL filter instead but you will be unable to edit the filter from the MWM GUI. To update a custom filter, modify the field from with an SQL client or the View Data Items tab.

The MWM_DESCRIPTIONS field is a base64 encoded version of the json string: {"description":"" }.
 
$ /opt/IBM/tivoli/impact/bin/nci_db connect

ij> connect 'jdbc:derby://host:1527/ImpactDB;CHARSET=UTF-8;user=<impactdb_user>;password=<impactdb_password>';
ij>
ij> INSERT INTO IMPACT.MM_WINDOWS (FILTERSTAT, WINTYPE, OT_STARTTIME, EOT_STARTTIME, OT_ENDTIME, EOT_ENDTIME, TIMEZONE, MWM_DESCRIPTIONS)
VALUES (
'(Node = "test") AND (Severity > 2)',
1,
'2023-03-27 15:25:00',
1679945100,
'2023-03-27 16:25:00',
1679948700,
'EST',
'eyJkZXNjcmlwdGlvbiI6IiJ9');

 
After the row is created, retrieve the MWID for the new row: 
ij> SELECT MWID FROM IMPACT.MM_WINDOWS WHERE FILTERSTAT = '(Node = "test") AND (Severity > 2)';

MWID
-----------
4

The MWID and username must be added to the MM_EXTRAINFO table.
 
ij> INSERT INTO IMPACT.MM_EXTRAINFO (MWID, USERNAME) values (4, 'impactadmin');
Procedure

The following procedure describes the process of generating MWM SQL statements from a Netcool/Impact policy.

Note: We recommend creating a backup of the derby database before hand.
  1. Go to the Services tab and open the PolicyLogger service.
  2. Change the Highest Log Level to 3.
  3. Enable the check boxes for the "All SQL Statements / Policy Query Diagnostics" and "Append Policy Name to Log File Name" options.

    PolicyLogger confguration
  4. Save the service.
  5. Open the "Maintenance Window" tab and create a maintenance window with the attributes you want to duplicate in your SQL query.

    New MWM entry
  6. Save the maintenance window.
  7. Locate the MWM policy logs and search for an INSERT statement into the IMPACT.MM_WINDOWS table. For a one-time window, the insert statements can be found in $IMPACT_HOME/logs/NCI_policylogger_MWM_Add1TWin.log file. For recurring windows, search the $IMPACT_HOME/logs/NCI_policylogger_MWM_AddRWin.log file.

    Example of the insert statement for a one-time window:
      
    27 Mar 2023 08:26:22,493: [MWM_Add1TWin][pool-4-thread-44]Parser log: Insert/update is INSERT INTO IMPACT.MM_WINDOWS (FILTERSTAT, WINTYPE, OT_STARTTIME, EOT_STARTTIME, OT_ENDTIME, EOT_ENDTIME, TIMEZONE, MWM_DESCRIPTIONS) VALUES ('(Node = "test") AND (Severity > 2)', 1, '2023-03-27 15:25:00', 1.6799451E9, '2023-03-27 16:25:00', 1.6799487E9, 'EST', 'eyJkZXNjcmlwdGlvbiI6IiJ9')
27 Mar 2023 08:26:22,494: [MWM_Add1TWin][pool-4-thread-44]SQL: INSERT INTO IMPACT.MM_WINDOWS (FILTERSTAT, WINTYPE, OT_STARTTIME, EOT_STARTTIME, OT_ENDTIME, EOT_ENDTIME, TIMEZONE, MWM_DESCRIPTIONS) VALUES ('(Node = "test") AND (Severity > 2)', 1, '2023-03-27 15:25:00', 1.6799451E9, '2023-03-27 16:25:00', 1.6799487E9, 'EST', 'eyJkZXNjcmlwdGlvbiI6IiJ9')
27 Mar 2023 08:26:22,501: [MWM_Add1TWin][pool-4-thread-44]
!++++++++++( DirectSQL
!DirectSQL->    Num DataItems: 1
!DirectSQL->    Memory Increase After Query: 0.46 MB
++++++++++
27 Mar 2023 08:26:22,501: [MWM_Add1TWin][pool-4-thread-44]SQL: SELECT MAX(MWID) AS MAXID FROM IMPACT.MM_WINDOWS
27 Mar 2023 08:26:22,505: [MWM_Add1TWin][pool-4-thread-44]
!++++++++++( DirectSQL
!DirectSQL->    Num DataItems: 1
!DirectSQL->    Memory Increase After Query: 0.58 MB
++++++++++
27 Mar 2023 08:26:22,506: [MWM_Add1TWin][pool-4-thread-44]Parser log: Result is {MAXID = 1}
27 Mar 2023 08:26:22,506: [MWM_Add1TWin][pool-4-thread-44]Parser log: MaxId is 1 and userName is impactadmin
27 Mar 2023 08:26:22,506: [MWM_Add1TWin][pool-4-thread-44]Parser log: Escaping username=impactadmin
27 Mar 2023 08:26:22,506: [MWM_Add1TWin][pool-4-thread-44]Parser log: userNameInsert is INSERT INTO IMPACT.MM_EXTRAINFO (MWID, USERNAME) values (1, 'impactadmin')
27 Mar 2023 08:26:22,507: [MWM_Add1TWin][pool-4-thread-44]SQL: INSERT INTO IMPACT.MM_EXTRAINFO (MWID, USERNAME) values (1, 'impactadmin')
27 Mar 2023 08:26:22,513: [MWM_Add1TWin][pool-4-thread-44]
!++++++++++( DirectSQL
!DirectSQL->    Num DataItems: 1
!DirectSQL->    Memory Increase After Query: 0.14 MB
++++++++++
  8. Make a copy of the SQL statements used to populate the database:
      
    INSERT INTO IMPACT.MM_WINDOWS (FILTERSTAT, WINTYPE, OT_STARTTIME, EOT_STARTTIME, OT_ENDTIME, EOT_ENDTIME, TIMEZONE, MWM_DESCRIPTIONS) VALUES ('(Node = "test") AND (Severity > 2)', 1, '2023-03-27 15:25:00', 1.6799451E9, '2023-03-27 16:25:00', 1.6799487E9, 'EST', 'eyJkZXNjcmlwdGlvbiI6IiJ9')

SELECT MAX(MWID) AS MAXID FROM IMPACT.MM_WINDOWS

INSERT INTO IMPACT.MM_EXTRAINFO (MWID, USERNAME) values (1, 'impactadmin')
    Note: The exact format of the SQL statements can vary between fix packs. This logging example was taken from Impact 7.1.0.29.
  9. For this example, we adapt the SQL statements into a policy that uses the DirectSQL policy function.
  10. Create an IPL policy with the following code:
      
    // Create the MWM entry in MM_WINDOWS
DataSource = "ImpactDB";
Query1 = "INSERT INTO IMPACT.MM_WINDOWS (FILTERSTAT, WINTYPE, OT_STARTTIME, EOT_STARTTIME, OT_ENDTIME, EOT_ENDTIME, TIMEZONE, MWM_DESCRIPTIONS) VALUES ('(Node = \"test2\") AND (Severity > 2)', 1, '2023-03-27 15:25:00', 1.6799451E9, '2023-03-27 16:25:00', 1.6799487E9, 'EST', 'eyJkZXNjcmlwdGlvbiI6IiJ9')";
CountOnly = false; DirectSQL(DataSource, Query1, CountOnly);
// After creating a new window in MM_WINDOWS, we need to create an entry in MM_EXTRAINFO
 
// Fetch the MaxID of the new window
Query2 = "SELECT MAX(MWID) AS MAXID FROM IMPACT.MM_WINDOWS";
Result2 = DirectSQL(DataSource, Query2, CountOnly);
MaxID = Result2[0].MAXID;
Log(MaxID);

// Get the next ID
NextID = Int(MaxID) + 1;

// Insert into MM_EXTRAINFO
Query3 = "INSERT INTO IMPACT.MM_EXTRAINFO (MWID, USERNAME) values (" +  Result2[0].NextID + ", 'impactadmin')";
DirectSQL(DataSource, Query3, CountOnly);

    Note: If the SQL statement contains double quote characters, you must escape them with a backslash (\").
  11. Save the policy and run the policy.
  12. Review the Maintenance Window tab and search for the new window.

New MWM GUI entry

[{"Type":"MASTER","Line of Business":{"code":"LOB45","label":"Automation"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSSHYH","label":"Tivoli Netcool\/Impact"},"ARM Category":[{"code":"a8m500000008ZwhAAE","label":"Impact-\u003EMWM (Maintenance Window)"}],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.1.0"}]

Document Information

Modified date:
29 March 2023

UID

ibm16963892

Page Feedback