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 /var/tmp/2017-08-01_event-export.csv  ----this originally used to copy the file to "/tmp" but I've changed it to "/var/tmp" due to limited space issue on "/tmp"

** 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 /var/tmp directory of prod-cacti01-fra-de.geant.net

Notes:

if you get this error "ssh: connect to host 62.40.99.114 port 22: Connection timed out" check with Anura or Aleksandr Kurbatov to open the firewall port to allow the connection

dnaadmin username and password are in LastPass (shared it from within LastPass with Erik and Mark on 02 July 2020)

If you get "ssh: connect to host 62.40.99.114 port 22: Connection timed out" try to ping the machine as it could be down. Speak to OC or Aleksandr Kurbatov.

Any issues with dna instance speak to Temoor. He ran the script and create the log file "2020-04-07_event-export.csv" in April 2020

There is an export script that runs on each of the DNA servers and saves the output to the relevant directory. The script is maintained by Infinera, the last person who used to administer this was Kevin Elliot, not sure if he is still responsible for this or not

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

Now, on prod-cacti01-fra-de.geant.net terminal connect to reports database on test-msr.geant.net:

Run the following 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:       

        1. truncate table reports.infinera_dna_outages;

       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;

** 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

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 2 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'

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

Note: How to export data using Heidi-SQL. See screenshots below.

Right click on the results and choose "Export grid rows"

Choose "Output format" as "Delimited text"