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

           

Memory Problems with Ubuntu

After data has been collected, upon continuing on the phase of text parsing and database insertion, i get a memory overflow 8GB  used !! and ubuntu hangs(can't resume without restart). Still investigating the issue....

After much investigation, the issue was based on how my script was developed. What happened was the script was trying to store all the data of file's accessed into an array before insertion into the database. The RAM couldn't handle this much information which caused a massive increase of memory utilization to 100%. So i resolved this issue by making sure that data is inserted per file accessed in the directory. 

Data Collection

Since, the database is now setup and running, i intend to start collecting data daily from the ftp site and storing.

Updated Database

I made some changes regarding the database which can be seen below.

Mysql

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `BGPstorage` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE `BGPstorage` ;

-- -----------------------------------------------------

-- Table `BGPstorage`.`as_detail`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_detail` (

  `AS_No` INT NOT NULL ,

  `AS_Name` VARCHAR(45) NULL ,

  `AS_Location` VARCHAR(45) NULL ,

  PRIMARY KEY (`AS_No`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`peer_detail`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`peer_detail` (

  `Peer_IP` VARCHAR(45) NOT NULL ,

  `Peer_Location` VARCHAR(45) NULL ,

  `Peer_IP_Name` VARCHAR(45) NULL ,

  `Peer_AS` INT NULL ,

  PRIMARY KEY (`Peer_IP`) )

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`update_detail`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`update_detail` (

  `Update_ID` INT NOT NULL AUTO_INCREMENT ,

  `Time` VARCHAR(45) NOT NULL ,

  `Type` VARCHAR(45) NULL ,

  `Peer_IP` VARCHAR(45) NULL ,

  PRIMARY KEY (`Update_ID`, `Time`) ,

  INDEX `Peer_IP_PD_idx` (`Peer_IP` ASC) ,

  CONSTRAINT `Peer_IP_fk`

    FOREIGN KEY (`Peer_IP` )

    REFERENCES `BGPstorage`.`peer_detail` (`Peer_IP` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`announce_update`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`announce_update` (

  `Announce_UpdateID` INT NOT NULL AUTO_INCREMENT ,

  `IP` VARCHAR(45) NULL ,

  `Subnet` VARCHAR(45) NULL ,

  `Update_ID` INT NOT NULL ,

  PRIMARY KEY (`Announce_UpdateID`, `Update_ID`) ,

  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,

  CONSTRAINT `Update_ID_fk_announce`

    FOREIGN KEY (`Update_ID` )

    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`as_path`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_path` (

  `AS_Path_ID` INT NOT NULL AUTO_INCREMENT ,

  `Path_Index` INT NULL ,

  `AS_No` INT NOT NULL ,

  `Update_ID` INT NOT NULL ,

  PRIMARY KEY (`AS_Path_ID`, `Update_ID`, `AS_No`) ,

  INDEX `AS_No_fk_idx` (`AS_No` ASC) ,

  INDEX `Update_ID_fk_as_path_idx` (`Update_ID` ASC) ,

  CONSTRAINT `AS_No_fk`

    FOREIGN KEY (`AS_No` )

    REFERENCES `BGPstorage`.`as_detail` (`AS_No` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `Update_ID_fk_as_path`

    FOREIGN KEY (`Update_ID` )

    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

-- -----------------------------------------------------

-- Table `BGPstorage`.`withdraw_update`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `BGPstorage`.`withdraw_update` (

  `Withdraw_UpdateID` INT NOT NULL AUTO_INCREMENT ,

  `IP` VARCHAR(45) NULL ,

  `Subnet` VARCHAR(45) NULL ,

  `Update_ID` INT NOT NULL ,

  PRIMARY KEY (`Withdraw_UpdateID`, `Update_ID`) ,

  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,

  CONSTRAINT `Update_ID_fk_withdraw`

    FOREIGN KEY (`Update_ID` )

    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;

USE `BGPstorage` ;

SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Database

The data is going to be collected and stored in mysql database. Below is a representation of the tables and relations.

These are the mysql statements :

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `BGPstorage` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `BGPstorage` ;

-- -----------------------------------------------------
-- Table `BGPstorage`.`as_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_detail` (
  `AS_No` INT NOT NULL ,
  `AS_Name` VARCHAR(45) NULL ,
  `AS_Location` VARCHAR(45) NULL ,
  PRIMARY KEY (`AS_No`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`peer_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`peer_detail` (
  `Peer_IP` VARCHAR(45) NOT NULL ,
  `Peer_Location` VARCHAR(45) NULL ,
  `Peer_IP_Name` VARCHAR(45) NULL ,
  `Peer_AS` INT NULL ,
  PRIMARY KEY (`Peer_IP`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`update_detail`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`update_detail` (
  `Update_ID` INT NOT NULL ,
  `Time` VARCHAR(45) NOT NULL ,
  `Type` VARCHAR(45) NULL ,
  `Peer_IP` VARCHAR(45) NULL ,
  PRIMARY KEY (`Update_ID`, `Time`) ,
  INDEX `Peer_IP_PD_idx` (`Peer_IP` ASC) ,
  CONSTRAINT `Peer_IP_fk`
    FOREIGN KEY (`Peer_IP` )
    REFERENCES `BGPstorage`.`peer_detail` (`Peer_IP` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`announce_update`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`announce_update` (
  `Announce_UpdateID` INT NOT NULL AUTO_INCREMENT ,
  `Prefix` VARCHAR(45) NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`Announce_UpdateID`) ,
  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,
  CONSTRAINT `Update_ID_fk_announce`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`as_path`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`as_path` (
  `AS_Path_ID` INT NOT NULL AUTO_INCREMENT ,
  `Path_Index` INT NULL ,
  `AS_No` INT NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`AS_Path_ID`) ,
  INDEX `AS_No_fk_idx` (`AS_No` ASC) ,
  INDEX `Update_ID_fk_as_path_idx` (`Update_ID` ASC) ,
  CONSTRAINT `AS_No_fk`
    FOREIGN KEY (`AS_No` )
    REFERENCES `BGPstorage`.`as_detail` (`AS_No` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Update_ID_fk_as_path`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `BGPstorage`.`withdraw_update`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `BGPstorage`.`withdraw_update` (
  `Withdraw_UpdateID` INT NOT NULL AUTO_INCREMENT ,
  `Prefix` VARCHAR(45) NULL ,
  `Update_ID` INT NULL ,
  PRIMARY KEY (`Withdraw_UpdateID`) ,
  INDEX `Update_ID_fk_idx` (`Update_ID` ASC) ,
  CONSTRAINT `Update_ID_fk_withdraw`
    FOREIGN KEY (`Update_ID` )
    REFERENCES `BGPstorage`.`update_detail` (`Update_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

USE `BGPstorage` ;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

=======================================================================

This table may still need some changes but for now, this is what i will use if errors are not encountered during the migration of the dumps.

Data Collection

Present phase which i am taking is to build a parser using perl. This parser is going to download BGP updates from the awmn community network, process them and store in mysql database.

LITERATURE STUDY ON BGP DATA ANALYSES

BGP RIB table growth from http://bgp.potaroo.net/

In recent years, there have been increasing interest in the BGP protocol operation and research, one of the most significant current discussions lie on security with less focus on the analysis of BGP data and what useful operations or inference  can be done with this data in a network. BGP research today has been solely based on the internet. While, my study is focused on community networks, analysis that has been done before may prove contradictory or different from BGP internet analyses. In the internet today, most ASes are considered to be ISP's or very large scale networks while in community networks, each node running at it's municipality or area is considered an Autonomous System.

BGP table growth from Cisco. 

A considerable amount of literature has been published on the study of bgp data analyses. However, majority of these studies were done during the rising age of BGP and not very recently. There is no doubt that some other studies have been done, but not really into the focus of BGP data analyses.

The first serious discussions and analyses of BGP emerged in 1991 by Y. Rekhter (rfc1265)   for BGP while after the new BGPv4 version, a new analysis study was done by Meyer & Patel in 2006 (rfc4274). These studies were focused on BGP's general analysis on bandwidth, performance, scalability and memory requirements. Since BGP dumps collected from routers is not in a readable format this data is considered RAW and needs to be processed by some tool, using some format in other to make it human readable which is how the MRT routing information export format came about. According to the  rfc draft by L blunk et al,  "the MRT format was developed to encapsulate, export, and archive this information in a standardized data representation". So today we have different tools available with this format implemented for conversion. An obsolete library called  Libbgpdump  written by Dan Ardelean is one of these. Today, we have the BGPdump and other tools that uses this library. The analyses of BGP data can be classified as a parent among other studies like : ASPATH analyses, BGP routing table analyses and others which strongly relate to the actual constituents of BGPdata. The first study that actually does the analysis of BGP data was done by :