-----Original Message-----
From: Jacob, Raymond A Jr
Sent: Monday, August 02, 2004 21:17
To: [EMAIL PROTECTED]
Subject: archive db daily- Rough Draft
I am new to Databases and would appreciate a second set of eyes looking over my sql
script.
The script takes the existing snort database and copies it to a database named
snort-2004-08-02. Then copies over a day's worth of information to the database
snort-2004-08-02. Once the database has been copied over, mysqlhotcopy is
used to copy the database to another directory. The database is then archived
and compressed to a file with a .tgz extension. After a week the database
is removed. I assume that I can find the age of the snort-2004-08-02 database
from mysql without checking a table in the snort-2004-08-02 database
When I determine that the database is over 7-days old I will drop the database?
thank you,
Raymond
-----------copySnort.sql-----------
#copySnort.sql copy snortdb database to snort-<yyyy>-<mm>-<dd>
CREATE DATABASE snort-2004-08-02;
USE snort-2004-08-02;
# Not sure if this is even necessary Database may use my default information.
GRANT INSERT,SELECT on snort-2004-08-02.* to [EMAIL PROTECTED];
CREATE TABLE acid_event ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
signature INT UNSIGNED NOT NULL,
sig_name VARCHAR(255),
sig_class_id INT UNSIGNED,
sig_priority INT UNSIGNED,
timestamp DATETIME NOT NULL,
ip_src INT UNSIGNED,
ip_dst INT UNSIGNED,
ip_proto INT,
layer4_sport INT UNSIGNED,
layer4_dport INT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX (signature),
INDEX (sig_name),
INDEX (sig_class_id),
INDEX (sig_priority),
INDEX (timestamp),
INDEX (ip_src),
INDEX (ip_dst),
INDEX (ip_proto),
INDEX (layer4_sport),
INDEX (layer4_dport)
);
INSERT snort-2004-08-02.acid_event.*
SELECT snort.acid_event.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01
AND (snort.event.cid = snort.acid_event.cid);
CREATE TABLE acid_ag ( ag_id INT UNSIGNED NOT NULL
AUTO_INCREMENT,
ag_name VARCHAR(40),
ag_desc TEXT,
ag_ctime DATETIME,
ag_ltime DATETIME,
PRIMARY KEY (ag_id),
INDEX (ag_id));
INSERT snort-2004-08-02.acid_ag.*
SELECT snort.acid_ag.*, snort.acid_ag_alert.ag_id, snort.acid_ag_alert.ag_cid
FROM snort.acid_ag, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
'2004-08-01'
AND (snort.event.cid = snort.acid_ag.cid);
CREATE TABLE acid_ag_alert( ag_id INT UNSIGNED NOT NULL,
ag_sid INT UNSIGNED NOT NULL,
ag_cid INT UNSIGNED NOT NULL,
PRIMARY KEY (ag_id, ag_sid, ag_cid),
INDEX (ag_id),
INDEX (ag_sid, ag_cid));
INSERT snort-2004-08-02.acid_ag_alert.*
SELECT snort.acid_ag_alert.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND (snort.event.cid = snort.acid_ag.cid);
CREATE TABLE acid_ip_cache( ipc_ip INT UNSIGNED NOT NULL,
ipc_fqdn VARCHAR(50),
ipc_dns_timestamp DATETIME,
ipc_whois TEXT,
ipc_whois_timestamp DATETIME,
PRIMARY KEY (ipc_ip),
INDEX (ipc_ip) );
INSERT snort-2004-08-02.acid_ip_cache.*
SELECT snort.acid_ip_cache.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND (snort.event.timestamp = snort.acid_ip_cache.ip_dns_timestamp);
CREATE TABLE protocols(
protocol INT,
name VARCHAR(50),
description VARCHAR(50)
);
INSERT snort-2004-08-02.protocols.*
SELECT snort.protocols.*
FROM snort.protocols.*;
CREATE TABLE services(
port INT,
protocol INT,
name VARCHAR(50),
description VARCHAR(255)
);
INSERT snort-2004-08-02.services.*
SELECT snort.services.*
FROM snort.services.*;
CREATE TABLE flags(
number INT,
RES1 INT,
RES2 INT,
URG INT,
ACK INT,
PSH INT,
RST INT,
SYN INT,
FIN INT,
valid INT,
description VARCHAR(255)
);
INSERT snort-2004-08-02.flags.*
SELECT snort.flags.*
FROM snort.flags.*;
CREATE TABLE schema ( vseq INT UNSIGNED NOT NULL,
ctime DATETIME NOT NULL,
PRIMARY KEY (vseq));
INSERT INTO schema (vseq, ctime) VALUES ('106', now());
CREATE TABLE event ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
signature INT UNSIGNED NOT NULL,
timestamp DATETIME NOT NULL,
PRIMARY KEY (sid,cid),
INDEX sig (signature),
INDEX time (timestamp));
INSERT snort-2004-08-02.event.*
SELECT snort.event.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01);
CREATE TABLE signature ( sig_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sig_name VARCHAR(255) NOT NULL,
sig_class_id INT UNSIGNED NOT NULL,
sig_priority INT UNSIGNED,
sig_rev INT UNSIGNED,
sig_sid INT UNSIGNED,
PRIMARY KEY (sig_id),
INDEX sign_idx (sig_name(20)),
INDEX sig_class_id_idx (sig_class_id));
INSERT snort-2004-08-02.signature.*
SELECT snort.signature.*
FROM snort.signature;
CREATE TABLE sig_reference (sig_id INT UNSIGNED NOT NULL,
ref_seq INT UNSIGNED NOT NULL,
ref_id INT UNSIGNED NOT NULL,
PRIMARY KEY(sig_id, ref_seq));
INSERT snort-2004-08-02.sig_ref.*
SELECT snort.sig_ref.*
FROM snort.sig_ref;
CREATE TABLE reference ( ref_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ref_system_id INT UNSIGNED NOT NULL,
ref_tag TEXT NOT NULL,
PRIMARY KEY (ref_id));
CREATE TABLE reference_system ( ref_system_id INT UNSIGNED NOT NULL
AUTO_INCREMENT,
ref_system_name VARCHAR(20),
PRIMARY KEY (ref_system_id));
INSERT snort-2004-08-02.reference_system.*
SELECT snort.reference_system.*
FROM snort.reference_system;
CREATE TABLE sig_class ( sig_class_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sig_class_name VARCHAR(60) NOT NULL,
PRIMARY KEY (sig_class_id),
INDEX (sig_class_id),
INDEX (sig_class_name));
INSERT snort-2004-08-02.sig_class.*
SELECT snort.sig_class.*
FROM snort.sig_class;
# store info about the sensor supplying data
CREATE TABLE sensor ( sid INT UNSIGNED NOT NULL AUTO_INCREMENT,
hostname TEXT,
interface TEXT,
filter TEXT,
detail TINYINT,
encoding TINYINT,
last_cid INT UNSIGNED NOT NULL,
PRIMARY KEY (sid));
INSERT snort-2004-08-02.sensor.*
SELECT snort.sensor.*
FROM snort.sensor;
# All of the fields of an ip header
CREATE TABLE iphdr ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
ip_src INT UNSIGNED NOT NULL,
ip_dst INT UNSIGNED NOT NULL,
ip_ver TINYINT UNSIGNED,
ip_hlen TINYINT UNSIGNED,
ip_tos TINYINT UNSIGNED,
ip_len SMALLINT UNSIGNED,
ip_id SMALLINT UNSIGNED,
ip_flags TINYINT UNSIGNED,
ip_off SMALLINT UNSIGNED,
ip_ttl TINYINT UNSIGNED,
ip_proto TINYINT UNSIGNED NOT NULL,
ip_csum SMALLINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX ip_src (ip_src),
INDEX ip_dst (ip_dst));
INSERT snort-2004-08-02.iphdr.*
SELECT snort.iphdr.*
FROM snort.iphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.iphdr.cid) ;
# All of the fields of a tcp header
CREATE TABLE tcphdr( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
tcp_sport SMALLINT UNSIGNED NOT NULL,
tcp_dport SMALLINT UNSIGNED NOT NULL,
tcp_seq INT UNSIGNED,
tcp_ack INT UNSIGNED,
tcp_off TINYINT UNSIGNED,
tcp_res TINYINT UNSIGNED,
tcp_flags TINYINT UNSIGNED NOT NULL,
tcp_win SMALLINT UNSIGNED,
tcp_csum SMALLINT UNSIGNED,
tcp_urp SMALLINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX tcp_sport (tcp_sport),
INDEX tcp_dport (tcp_dport),
INDEX tcp_flags (tcp_flags));
INSERT snort-2004-08-02.tcphdr.*
SELECT snort.tcphdr.*
FROM snort.tcphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.tcphdr.cid) ;
# All of the fields of a udp header
CREATE TABLE udphdr( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
udp_sport SMALLINT UNSIGNED NOT NULL,
udp_dport SMALLINT UNSIGNED NOT NULL,
udp_len SMALLINT UNSIGNED,
udp_csum SMALLINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX udp_sport (udp_sport),
INDEX udp_dport (udp_dport));
INSERT snort-2004-08-02.updhdr.*
SELECT snort.udphdr.*
FROM snort.udphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.udphdr.cid) ;
# All of the fields of an icmp header
CREATE TABLE icmphdr( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
icmp_type TINYINT UNSIGNED NOT NULL,
icmp_code TINYINT UNSIGNED NOT NULL,
icmp_csum SMALLINT UNSIGNED,
icmp_id SMALLINT UNSIGNED,
icmp_seq SMALLINT UNSIGNED,
PRIMARY KEY (sid,cid),
INDEX icmp_type (icmp_type));
INSERT snort-2004-08-02.icmphdr.*
SELECT snort.icmphdr.*
FROM snort.icmphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.icmphdr.cid) ;
# Protocol options
CREATE TABLE opt ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
optid INT UNSIGNED NOT NULL,
opt_proto TINYINT UNSIGNED NOT NULL,
opt_code TINYINT UNSIGNED NOT NULL,
opt_len SMALLINT,
opt_data TEXT,
PRIMARY KEY (sid,cid,optid));
INSERT snort-2004-08-02.opt.*
SELECT snort.opt.*
FROM snort.opt, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.opt.cid) ;
# Packet payload
CREATE TABLE data ( sid INT UNSIGNED NOT NULL,
cid INT UNSIGNED NOT NULL,
data_payload TEXT,
PRIMARY KEY (sid,cid));
INSERT snort-2004-08-02.data.*
SELECT snort.data.*
FROM snort.data, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.data.cid) ;
# encoding is a lookup table for storing encoding types
CREATE TABLE encoding(encoding_type TINYINT UNSIGNED NOT NULL,
encoding_text TEXT NOT NULL,
PRIMARY KEY (encoding_type));
INSERT INTO encoding (encoding_type, encoding_text) VALUES (0, 'hex');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (1, 'base64');
INSERT INTO encoding (encoding_type, encoding_text) VALUES (2, 'ascii');
INSERT snort-2004-08-02.encoding.*
SELECT snort.encoding.*
FROM snort.encoding;
# detail is a lookup table for storing different detail levels
CREATE TABLE detail (detail_type TINYINT UNSIGNED NOT NULL,
detail_text TEXT NOT NULL,
PRIMARY KEY (detail_type));
INSERT INTO detail (detail_type, detail_text) VALUES (0, 'fast');
INSERT INTO detail (detail_type, detail_text) VALUES (1, 'full');
INSERT snort-2004-08-02.detail.*
SELECT snort.detail.*
FROM snort.detail;
#-----end copySnort.sql--------
#-----PurgeSnortDaily.sql----------
use snort
DELETE snort.acid_event.*
SELECT snort.acid_event.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01
AND (snort.event.cid = snort.acid_event.cid);
DELETE snort.acid_ag.*
SELECT snort.acid_ag.*, snort.acid_ag_alert.ag_id, snort.acid_ag_alert.ag_cid
FROM snort.acid_ag, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
'2004-08-01'
AND (snort.event.cid = snort.acid_ag.cid);
DELETE snort.acid_ag_alert.*
SELECT snort.acid_ag_alert.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND (snort.event.cid = snort.acid_ag.cid);
DELETE snort.acid_ip_cache.*
SELECT snort.acid_ip_cache.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND (snort.event.timestamp = snort.acid_ip_cache.ip_dns_timestamp);
DELETE snort.event.*
SELECT snort.event.*
FROM snort.acid_event, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01);
DELETE snort.iphdr.*
SELECT snort.iphdr.*
FROM snort.iphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.iphdr.cid) ;
DELETE snort.tcphdr.*
SELECT snort.tcphdr.*
FROM snort.tcphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.tcphdr.cid) ;
DELETE snort.updhdr.*
SELECT snort.udphdr.*
FROM snort.udphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.udphdr.cid) ;
DELETE snort.icmphdr.*
SELECT snort.icmphdr.*
FROM snort.icmphdr, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.icmphdr.cid) ;
DELETE snort.opt.*
SELECT snort.opt.*
FROM snort.opt, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.opt.cid) ;
DELETE snort.data.*
SELECT snort.data.*
FROM snort.data, snort.event
WHERE (snort.event.timestamp between DATE_ADD('2004-08-01', interval -1 day ) and
2004-08-01)
AND ( snort.event.cid = snort.data.cid) ;
#----end-PurgeSnortDaily.sql----------
#-------dropSnortDaily.sql---------
DROP DATABASE snort-2004-08-02
#-------end-dropSnortDaily.sql---------
Script:
mysql -u eddie -p murphy < copySnort.sql
mysqlhotcopy snort-2004-08-02 /var/snort/db-backups/
cd /var/snort/db-backups/
tar cvf snort-2004-08-02 | gzip -9 > /var/log/db/snort-2004-08-02.tgz
mysql -u eddie -p murphy < PurgeSnortDaily.sql
#rotate database( delete database that is 7days old)
find . -name 'snort-*' -type d -ctime 7 exec rm -r {}\;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
