Page History

...

KElliott@infinera.com James, Darren Clarke or Temoor may know

Now, Connect on prod-cacti01-fra-de.geant.net terminal connect to reports database on test-msr.geant.net: (using MySQL Workbench)

Run the following commands:command on prod-cacti putty session in order to connect to reports database on test-msr host.

       mysql -ureportsUser -p -htest-msr.geant.net reports     ### Password for "reportsUser" user is on LastPass. Shared with Erik and Mark.

Run the below queries:       

         truncate 1. truncate table reports.infinera_dna_outages;

       LOAD  2. LOAD DATA LOCAL INFILE '/var/tmp/filename.csv' (e.g. 2017-08-01_event-export.csv) INTO TABLE reports.infinera_dna_outages FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 10 LINES;

...

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

     3. update reports.infinera_dna_outages set event_sub_type = 'ALARM' where message like 'Restoration%';
     4. update reports.infinera_dna_outages set circuit_id = additional_text where message like 'Restoration%';
     5. 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.%';

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

Close the MySQL connect (type exit).

We now need to populate a table called reports.infinera_outage_ci. :

on prod-cacti01  cd to /home/cactiuser/scripts/

execute exportoutages.sh as cactiuser user  (run "sudo ./exportoutages.sh")

This will create a log file INFINERA-Extraction-Log-yyyymmdd.log that can be used for any debugging

...

Once the batch program has completed, log into the reports database (Heidi-SQL) with the reportsUser user and run the following commands2 queries:

1:

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

2:

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'

...