Analysis (Queries and Results)

To get the total number of update announcements with time between July - September, using the query below :

SELECT u.Update_ID, COUNT(Time), MONTH(Time)
   FROM  update_detail u
   JOIN  announce_update au
   ON u.Update_ID = au.Update_ID
   WHERE DATE(Time) BETWEEN '2013-07-14' and '2013-09-05'
   GROUP BY MONTH(Time)

We have the following results :

To get the total number of withdraw announcements with time between that same time period, this query provides the results :

SELECT u.Update_ID, COUNT(Time), MONTH(Time)
   FROM  update_detail u
   JOIN  announce_update au
   ON u.Update_ID = au.Update_ID
   WHERE DATE(Time) BETWEEN '2013-07-14' and '2013-09-05'
   GROUP BY MONTH(Time)

To get the top 10 updated IP from July to September:


SELECT * FROM (SELECT
    IP, count(IP) as cnt
FROM
    announce_update au
        JOIN
    update_detail u ON au.Update_ID = u.Update_ID
group by 1
having count(*) > 1) sub
ORDER BY cnt DESC   LIMIT 10;


Result :


# IP,               cnt
10.3.3.0,         75474
10.17.129.0,    51516
10.49.229.0,    20399
10.32.54.0,      16757
10.17.113.0,    16669
10.42.35.0,      16622
10.17.139.0,    16341
10.2.86.0,       16307
10.2.92.0,       16119
10.146.8.0,      14869

To get the Paths of a Particular IP


SELECT
    as_path.AS_No, as_path.Path_Index, as_path.Update_ID
FROM
    bgpstorage.as_path
        JOIN
    update_detail ON update_detail.Update_ID = as_path.Update_ID
        JOIN
    announce_update ON announce_update.Update_ID = update_detail.update_ID
WHERE
    announce_update.IP = '10.47.152.0'

Results : The list is quite long .





To get the average Path of a particular IP


SELECT IP, AVG(cnt),Update_ID FROM (SELECT
    announce_update.IP, COUNT(as_path.Path_Index) as cnt, as_path.Update_ID
FROM
    bgpstorage.as_path
        JOIN
    update_detail ON update_detail.Update_ID = as_path.Update_ID
        JOIN
    announce_update ON announce_update.Update_ID = update_detail.update_ID
WHERE
    announce_update.IP = '10.47.152.0' GROUP BY Update_ID) sub
GROUP BY IP;


results :

# IP,                AVG(Path)
10.47.152.0,       8.5000


List of ASeS that occur frequently in Paths :

SELECT * FROM (SELECT
    AS_No, count(*) as cnt
from
    as_path
group by 1
having count(*) > 1) sub
GROUP BY cnt DESC


results :

AS            Occurrences
172193 624215
19601       623457
14835        575958
4758         217856
19074       190572
1286         147212
12628 144938
12629 135131
410          133693
4704         112300