gistfile1.sql

/* Table structure for a wireshark capture file CSV export */
DROP TABLE `scratch`.`packet_log`;
CREATE TABLE `scratch`.`packet_log` (
  `packet` INTEGER  NOT NULL,
  `time` DOUBLE  NOT NULL,
  `source` varchar(50)  NOT NULL,
  `destination` varchar(50)  NOT NULL,
  `protocol` VARCHAR(10)  NOT NULL,
  `info` VARCHAR(255)  NOT NULL,
  PRIMARY KEY (`packet`)
)
ENGINE = InnoDB;

LOAD DATA LOCAL INFILE 'capture.csv' INTO TABLE packet_log 
    FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES;   

/* Then to get a rough idea of accesses you can do a query 
to group similiarly timed packets together and count them up. 
The time offset is from when the capture was started in seconds. */

SELECT COUNT(*) AS "# of Packets", FLOOR(time) AS "Time offset", 
    DATE_ADD("2010-07-08 15:12:00", INTERVAL FLOOR(time) SECOND) AS "Time" 
    FROM scratch.packet_log p GROUP BY floor(time);

/* The above will be grouped on seconds, if you want to make this more granular
maybe to count by the minute then substring the offset: */

SELECT COUNT(*) AS "# of Packets", substring(DATE_ADD("2010-07-08 15:12:00", 
    INTERVAL FLOOR(time) SECOND), 1, 16) AS "OffsetTime"  
    FROM scratch.packet_log p GROUP BY OffsetTime