Here are the queries used to generate each section, as of today:

Total DWS Traffic

SELECT period,year,month,volume_in,volume_out
FROM reports.excel_yearly_volumes
WHERE (excel_yearly_volumes.project='DWS')
AND (excel_yearly_volumes.circuit_usage='Interconnect')
AND (excel_yearly_volumes.period >= '201711')
AND (excel_yearly_volumes.period <= '201810')
ORDER BY period ;

Total IPv6 Traffic

SELECT period,year,month,volume_in,volume_out
FROM reports.excel_yearly_dante_service_volumes
WHERE (excel_yearly_dante_service_volumes.project='GEANT2')
AND (excel_yearly_dante_service_volumes.circuit_usage='Access')
AND (excel_yearly_dante_service_volumes.type='ipv6')
AND (excel_yearly_dante_service_volumes.period>= '"+ConcatPeriodFrom+"')
AND (excel_yearly_dante_service_volumes.period<= '"+ConcatPeriodTo+"')
ORDER BY period;

Total LHCONE Traffic

SELECT excel_utilization.year,excel_utilization.month,(SUM(excel_utilization.utilization_in)*2678400)/8000000000000,(SUM(excel_utilization.utilization_out)*2678400)/8000000000000
FROM reports.excel_utilization excel_utilization, reports.opsdb_circuit opsdb_circuit
WHERE excel_utilization.base_absid = opsdb_circuit.circuit_absid
AND (excel_utilization.service_type='LHC')
AND (opsdb_circuit.project='LHCONE')
AND (opsdb_circuit.new_service_type='L3 VPN') 
AND (opsdb_circuit.name not like 'ESNET%')
AND (CONCAT(excel_utilization.year,lpad(excel_utilization.month,2,'0')) >= '"+ConcatPeriodFrom+"')
AND (CONCAT(excel_utilization.year,lpad(excel_utilization.month,2,'0')) <= '"+ConcatPeriodTo+"')
AND (opsdb_circuit.status='Operational')
GROUP BY YEAR,MONTH;

Total IPv4 Traffic

SELECT period,year,month,volume_in,volume_out
FROM reports.excel_yearly_volumes
WHERE (excel_yearly_volumes.project='GEANT2')
AND (excel_yearly_volumes.circuit_usage='Access')
AND (excel_yearly_volumes.period >= '"+ConcatPeriodFrom+"')
AND (excel_yearly_volumes.period <= '"+ConcatPeriodTo+"')
ORDER BY period;

Total Interconnect Traffic (R&E Peering)

SELECT period,year,month,from_data,to_data
FROM reports.msr_graphical_data
WHERE (msr_graphical_data.period >= '"+ConcatPeriodFrom+"')
AND (msr_graphical_data.period <= '"+ConcatPeriodTo+"')
ORDER BY period;
  • No labels