3.3 Planned Maintenance

On pages that contain circuit availability data such as the IP Access Detailed View: http://test-msr.geant.net:8888/msr/ms_ip_avail_new.jsp There is a column for the Total Outage Time During Maintenances.

The following query will obtain the Planned Maintenance from the otrs database on test-msr.geant.net:

SELECT a.id,a.title,c.field_id,UNIX_TIMESTAMP(c.value_date) as 'Time As Epoch',c.value_text
FROM otrs.ticket a
LEFT JOIN otrs.ticket_type b ON (b.id = a.type_id)
LEFT JOIN otrs.dynamic_field_value c ON (c.object_id = a.id)
WHERE a.queue_id IN (4,5,6,10,15,19,20,22,24,40,45,46,62,63,64)
AND a.type_id = 6
AND c.field_id in (6,7,22)
AND YEAR(a.change_time) = 2017
AND MONTH(a.change_time) = 7
ORDER BY a.id, field_id ASC;

** Pay attention to the year and month values in the SQL statement

** A ticket type of 6 is a planned maintenance ticket. The date, time and affected services by the planned maintenance are stored in dynamic field values:

6 = Maintenance Start Time (UTC)

7 = Maintenance End Time (UTC)

22 =  Related Services

Look at the title and value_text columns of the rows that have the field_id column of 22. You are looking for meaningful circuit names of IP Access, Interconnect, GEANT Lambda and GEANT Plus circuits.

Using OpsDB (http://opsdb.dante.net) obtain the circuit absid of the affected circuit

Create a spreadsheet and in column A put the circuit absid of the affected service, in column B put the Time as Epoch value when the field_id is 6 (Start Time) and in column C put the Time as Epoch value when the field_id is 7 (End Time).

The duration of the maintenance in seconds is the End Time - Start Time. In Column D , Row 1, create a formula =(C1-B1) / 86400. Drag this formula down the length of the number of rows with data in it, to complete the column of maintenance durations.

** 86400 is the number of seconds in a day, 24 * 60 * 60

Now we need to convert the duration time in seconds into the format hours:minutes:seconds, to do this right-click the column header for column D and select format cells. In the Number tab, select the Custom Category, then in the type list, select hh:mm:ss

** WARNING - Watch out for times that are greater that 23:59:59, as they will not be converted properly. Please refer to the WARNINGS in the 3.1 Availability section.

In Column E, Row 1, create a formula  =CONCATENATE("UPDATE report_service_availability SET out_maint='",TEXT(D1,"hh:mm:ss"),"' WHERE base_absid = ",A1," AND year = 2017 AND month = 7 ;"). Drag this formula down the length of the number of rows with data in it, to complete the column of SQL statements.

** Pay attention to the year and month values in the SQL statement

** IMPROVEMENT - This is way too manual, so automation is required some how

** Example query generated

UPDATE reports.report_service_availability
SET out_maint = '06:00:00'
WHERE base_absid = 31467
AND year = 2017
AND month = 7;

** The base_absid is the absid of the circuit we want to change.  You can verify the circuit information by checking the OpsDB Application using the following link: http://opsdb.dante.net/circuit-display-basic.phtml?absid=31467  alternatively you could use the following query:

SELECT * FROM reports.opsdb_circuit
WHERE cirtcuit_absid = 31467;

** Pay attention to the circuit absid, year and month values in the SQL statement