New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sguil-db-purge - add DAYSTOREPAIR option #362

Closed
GoogleCodeExporter opened this Issue Mar 24, 2015 · 3 comments

Comments

Projects
None yet
1 participant
@GoogleCodeExporter
https://groups.google.com/d/topic/security-onion-testing/sKtU3gi2hE8/discussion

Original issue reported on code.google.com by doug.bu...@gmail.com on 9 Jul 2013 at 1:27

@GoogleCodeExporter

This comment has been minimized.

Show comment
Hide comment
@GoogleCodeExporter

GoogleCodeExporter Mar 24, 2015

Perhaps start with just DAYSTOREPAIR change:

# How many days of data do you want to keep?
DAYSTOKEEP=365

# How many days of data do you want to repair?
DAYSTOREPAIR=365

# You can override the above settings in:
source /etc/nsm/securityonion.conf

##############################################
# No need to change anything below this point
##############################################

# Check to see if there is a valid DB.  If not, exit silently.
if [ ! -d /var/lib/mysql/$DATABASE/ ]; then exit; fi

KEEPDAY=`/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e "SELECT
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $DAYSTOKEEP DAY), '%Y%m%d');" -D
$DATABASE`
REPAIRDAY=`/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e "SELECT
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $DAYSTOREPAIR DAY), '%Y%m%d');"
-D $DATABASE`

echo "Retention policy set to $DAYSTOKEEP days (deleting data prior to
$KEEPDAY)."

# Define a cleanup function
cleanup() {

        for TABLEPREFIX in "data" "event" "icmphdr" "sancp" "tcphdr" "udphdr"
        do
                /usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e
"DROP TABLE $TABLEPREFIX;" -D $DATABASE
                TABLES=(`/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION
-BN -e "SHOW TABLES LIKE '$TABLEPREFIX%';" -D $DATABASE`)
                for TABLE in "${TABLES[@]}"
                do
                        TABLEDAY=`echo "$TABLE" | awk -F_ '{print($3)}'`
                        if [ "$TABLEDAY" -lt "$KEEPDAY" ]
                        then
                                /usr/bin/mysql -u$DB_USER
$PASSWORD_OPTION -BN -e "DROP TABLE \`$TABLE\`;" -D $DATABASE
                        else
                                [ "$TABLEDAY" -gt "$REPAIRDAY" ] &&
/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e "REPAIR TABLE
\`$TABLE\`;" -D $DATABASE
                        fi
                done
        done

Original comment by doug.bu...@gmail.com on 15 Nov 2013 at 7:09

  • Added labels: ****
  • Removed labels: ****
Perhaps start with just DAYSTOREPAIR change:

# How many days of data do you want to keep?
DAYSTOKEEP=365

# How many days of data do you want to repair?
DAYSTOREPAIR=365

# You can override the above settings in:
source /etc/nsm/securityonion.conf

##############################################
# No need to change anything below this point
##############################################

# Check to see if there is a valid DB.  If not, exit silently.
if [ ! -d /var/lib/mysql/$DATABASE/ ]; then exit; fi

KEEPDAY=`/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e "SELECT
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $DAYSTOKEEP DAY), '%Y%m%d');" -D
$DATABASE`
REPAIRDAY=`/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e "SELECT
DATE_FORMAT(DATE_SUB(NOW(), INTERVAL $DAYSTOREPAIR DAY), '%Y%m%d');"
-D $DATABASE`

echo "Retention policy set to $DAYSTOKEEP days (deleting data prior to
$KEEPDAY)."

# Define a cleanup function
cleanup() {

        for TABLEPREFIX in "data" "event" "icmphdr" "sancp" "tcphdr" "udphdr"
        do
                /usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e
"DROP TABLE $TABLEPREFIX;" -D $DATABASE
                TABLES=(`/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION
-BN -e "SHOW TABLES LIKE '$TABLEPREFIX%';" -D $DATABASE`)
                for TABLE in "${TABLES[@]}"
                do
                        TABLEDAY=`echo "$TABLE" | awk -F_ '{print($3)}'`
                        if [ "$TABLEDAY" -lt "$KEEPDAY" ]
                        then
                                /usr/bin/mysql -u$DB_USER
$PASSWORD_OPTION -BN -e "DROP TABLE \`$TABLE\`;" -D $DATABASE
                        else
                                [ "$TABLEDAY" -gt "$REPAIRDAY" ] &&
/usr/bin/mysql -u$DB_USER $PASSWORD_OPTION -BN -e "REPAIR TABLE
\`$TABLE\`;" -D $DATABASE
                        fi
                done
        done

Original comment by doug.bu...@gmail.com on 15 Nov 2013 at 7:09

  • Added labels: ****
  • Removed labels: ****
@GoogleCodeExporter

This comment has been minimized.

Show comment
Hide comment
@GoogleCodeExporter

GoogleCodeExporter Mar 24, 2015

Original comment by doug.bu...@gmail.com on 15 Nov 2013 at 7:12

  • Changed title: sguil-db-purge - add DAYSTOREPAIR option
  • Added labels: ****
  • Removed labels: ****

Original comment by doug.bu...@gmail.com on 15 Nov 2013 at 7:12

  • Changed title: sguil-db-purge - add DAYSTOREPAIR option
  • Added labels: ****
  • Removed labels: ****
@GoogleCodeExporter

This comment has been minimized.

Show comment
Hide comment
@GoogleCodeExporter

GoogleCodeExporter Mar 24, 2015

Published:
http://blog.securityonion.net/2013/12/bro-22-and-elsa-15-packages-now.html

Original comment by doug.bu...@gmail.com on 10 Dec 2013 at 9:01

  • Changed state: Verified
  • Added labels: ****
  • Removed labels: ****
Published:
http://blog.securityonion.net/2013/12/bro-22-and-elsa-15-packages-now.html

Original comment by doug.bu...@gmail.com on 10 Dec 2013 at 9:01

  • Changed state: Verified
  • Added labels: ****
  • Removed labels: ****
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment