7.1 Availability

Log into prod-cacti01-fra-de.geant.net as yourself (using dante.surname credentials)

Run the following command:

sudo -u cactiuser scp -i /home/cactiuser/.ssh/id_dsa dnaadmin@62.40.99.114:/home/dnaadmin/2017-08-01_event-export.csv /tmp/2017-08-01_event-export.csv

** Pay attention to the year and the month value in the above statement. The month value is the actual month you are preparing the report. E.g. the above example will contain the data for July and NOT for August

The command will copy off the monthly event log from the London instance of the Infinera DNA box (62.40.99.114) and store it in the /tmp directory of prod-cacti01-fra-de.geant.net

** Are you able to sudo as cactiuser?

SCP the csv file from the /tmp directory onto your laptop into a directory called C:\MSR\Lambda

Start MySQL Workbench and connect to the reports database on test-msr.geant.net

Now run the following SQL statements:

use reports;

truncate table reports.infinera_dna_outages;

LOAD DATA LOCAL INFILE 'c:\\MSR\\Lambda\\2017-08-01_event-export.csv' REPLACE INTO TABLE reports.infinera_dna_outages FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 10 LINES;

** Pay attention to the year and the month value in the above statement. The month value is the actual month you are preparing the report. E.g. the above example will contain the data for July and NOT for August

** This will take some time, roughly 15-20 minutes. Time for a tea or coffee !

Because Lamdbas are clever, they can auto restore, so we need to turn these auto restoration events into alarms, using the following SQL statements:

update reports.infinera_dna_outages set event_sub_type = 'ALARM' where message like 'Restoration%';
update reports.infinera_dna_outages set circuit_id = additional_text where message like 'Restoration%';
update reports.infinera_dna_outages set severity = 'CR' where message like 'Restoration due to SNC Fault%'; update infinera_dna_outages set severity = 'Clear' where message like 'Restoration Successful.%';

delete from reports.infinera_dna_outages where event_sub_type != 'ALARM';

We now need to populate a table called reports.infinera_outage_ci. This is acheived by running a JAVA batch program called ExportOutages.

To Do. Create Bash Shell Script and move Java app from Richard's Laptop onto test-msr.geant.net

Once the batch program has completed, run the following commands:

update reports.infinera_outage_ci
set time_diff = (UNIX_TIMESTAMP(end_time) - UNIX_TIMESTAMP(start_time))
where end_time is not null;

SELECT CONCAT('UPDATE reports.report_service_availability SET outage = \'',TIME_FORMAT(SEC_TO_TIME(sum(a.time_diff)),'%H:%i:%s'),'\', number_of_failures = ',count(*),' WHERE base_absid = ',b.circuit_absid,' AND year = ',year(a.start_time),' AND month = ',month(a.start_time),';') as 'SQL Commands'

FROM reports.infinera_outage_ci a
LEFT JOIN reports.opsdb_circuit b ON (b.name = a.circuit_id)
WHERE year(a.start_time) = 2017
AND month(a.start_time) = 7
AND b.status = 'Operational'
AND b.new_service_type = 'Geant Lambda'
AND a.circuit_id NOT LIKE '%_EEX_ESNET_14%'
GROUP BY a.circuit_id;

** Pay attention to the year and the month value in the above statement. 

Export the data using Heidi-SQL using the Ouput format as Delimited Text. Paste the results back into Heidi-SQL and run the queries.

Check the results by using the following url: http://test-msr.geant.net:8888/msr/ms_lambda_avail_new.jsp