Page History

...

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