MySQL and Snort, DB Huge!!
Results 1 to 6 of 6

Thread: MySQL and Snort, DB Huge!!

  1. #1
    AO übergeek phishphreek's Avatar
    Join Date
    Jan 2002
    Posts
    4,324

    MySQL and Snort, DB Huge!!

    My MySQL DB is huge and BASE is taking a really long time to show me alerts.

    I know it is huge because when I installed it, I was working out all the false positives.
    There were quite a few...

    Is is possible to "archive" that data and start fresh?

    I could probably just delete it and start again, but I'd like to keep it for a while.
    I just want to drop/backup/archive the data but keep all the schema and fields and what not..

    I'm a DB n00b... so please excuse my ignorence.
    Quitmzilla is a firefox extension that gives you stats on how long you have quit smoking, how much money you\'ve saved, how much you haven\'t smoked and recent milestones. Very helpful for people who quit smoking and used to smoke at their computers... Helps out with the urges.

  2. #2
    Senior Member RoadClosed's Avatar
    Join Date
    Jun 2003
    Posts
    3,834
    Copy it over and use any old ODBC compliant front end to look at it and search. You could use access or even Excel if you have an MS platform. phpMyAdmin on Linux. Then delete the data through there This is the reason I don't use SQL to access snort logs, I took Tigers approach with a text file and a stripper to access specific events. It's FAST. But lags in real time. I am changing this approach though .... stay tuned.

    Now is a good time to learn some basic SQL so you can issue statements like mass deletes etc. You may need to copy the database every day or every week to keep it clean and efficient. SQL commands can be issued right on the live database like copy or delete. And then you can store up statements and file them in a procedure or stored proc that's sort of like a batch file. Like perhaps:

    Copy the file then
    DELETE * FROM Table_Whatever (this will delete all rows in a table WITHOUT deleting the table. But I bet there are some procudures already written for snort...

    //EDIT didn't or doesn't ACID have some database tools in it?
    West of House
    You are standing in an open field west of a white house, with a boarded front door.
    There is a small mailbox here.

  3. #3
    AO übergeek phishphreek's Avatar
    Join Date
    Jan 2002
    Posts
    4,324
    Thanks for the tip(s).

    I have been meaning to learn some mysql. I just haven't really had a need until now.
    So much to learn, so little time.
    Quitmzilla is a firefox extension that gives you stats on how long you have quit smoking, how much money you\'ve saved, how much you haven\'t smoked and recent milestones. Very helpful for people who quit smoking and used to smoke at their computers... Helps out with the urges.

  4. #4
    Senior Member kr5kernel's Avatar
    Join Date
    Mar 2004
    Posts
    347
    Acid does have some drop down menus that will execute sql commands for you. This is what I used, when I had a false postive taking up a bunch of space, select DELETE, the Table Name, and a query string based on a keyword from the false positive entry.
    kr5kernel
    (kr5kernel at hotmail dot com)
    Linux: Making Penguins Cool Since 1994.

  5. #5
    AO Ancient: Team Leader
    Join Date
    Oct 2002
    Posts
    5,197
    Puresecure allows you to purge everything before date x as well....
    Don\'t SYN us.... We\'ll SYN you.....
    \"A nation that draws too broad a difference between its scholars and its warriors will have its thinking done by cowards, and its fighting done by fools.\" - Thucydides

  6. #6
    Senior Member
    Join Date
    Mar 2004
    Posts
    557
    Hi

    DB's - I love them

    backing up

    There are two ways how to backup the snort-database:

    1. copy all files from mysql/data/snort/ onto a backup-directory.
    Basically, each table in the database has one of those frm, MYD and MYI files.
    This is, well, when you know what you are doing

    2. perform a dump - this may take a while in your case, because those tables
    can be very huge. Basically, this dump is a set of SQL-commands to recreate the database.
    Note: the database itself is not created. It creates a dump assuming
    that you are logged in that particular database.

    Code:
     > mysqldump -h localhost -u user_snort snort >snort.sql
    If snort is the only database on your mySQL system, you also could perform a complete dump

    Code:
     > mysqldump -h localhost -u root  -A >complete.sql
    This dump also creates the databases.

    restoring (test!)

    The idea of the restoration is to reproduce the original database.
    Here, I would recommend to create a new database snort_backup (see below).
    I assume that you want to restore the original entries, hence I continue
    using the database name `snort`.

    Code:
      > mysql -u root <complete.sql
    or

    Code:
      > mysql -u user_snort snort <snort.sql
    assuming that the database `snort` exists. Otherwise, add the above
    command using "-u root" and add at the beginning of snort.sql
    Code:
    CREATE DATABASE `snort`;
    USE `snort`;
    Passwords can be handed over using the "--password=password_root" option

    deleting table entries

    for each table, you can run the command

    Code:
    > mysql -u user_snort snort
    mysql> delete from table_name
    where table_name in a generic snort installation is one of


    Code:
      data 
      detail 
      encoding 
      event 
      icmphdr 
      iphdr 
      opt 
      reference 
      reference_system 
      schema 
      sensor 
      sig_class 
      sig_reference 
      signature 
      tcphdr 
      udphdr
    /edit: You obtain a list of the tables performing an

    Code:
    > grep "CREATE TABLE" snort.sql
    resp.
    > type snort.sql | find "CREATE TABLE"

    Usually, one tries to perform transactions, which can be
    committed or allow for a rollback[1]. I won't comment on them
    here. Have also a read at the disaster recovery page of mysql[2].

    Good luck!

    Cheers

    [1] http://dev.mysql.com/doc/mysql/en/an...nsactions.html
    [2] http://dev.mysql.com/doc/mysql/en/di...revention.html
    If the only tool you have is a hammer, you tend to see every problem as a nail.
    (Abraham Maslow, Psychologist, 1908-70)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •