Upgrade Procedures for Logzilla 3.x
From Network Management Wiki
(Redirected from Upgrade Procedures for Logzilla 3.0)
Contents
|
About Upgrades
Prior to version 3.0.79, upgrade procedures were typically not provided for Logzilla, due to time constraints within the development team. Since 3.0.79, this has changed, but there may come a day when an upgrade procedure either is not, or cannot be provided. If this is the case, and you need to save your old data, then it is recommended that you install the new version of Logzilla next to the existing installation, by installing it to a different URL and a different database, and leave the old version running until you no longer have a use for it.
New/Easier upgrade procedures
After v3.0.91, the following upgrade procedures can be used (and may work on older versions as well).
This procedure should work for future versions as well - as long as no new tables have been added to the database.
Assumptions for the following, please change to match your installation accordingly.
- LogZilla is install to /var/www/logzilla
- Your CURRENTLY installed database is named 'syslog'
- Your NEW installation database will be called 'newdb'
Change to the www directory
cd /var/www
Rename existing LogZilla directory
mv logzilla logzilla.old
Extract the new tarball
(download from [the LogZilla Website]):
tar xzvf logzilla_x.x.tgz
Change to the LogZilla directory
cd logzilla
Copy your license from the old directory
cp ../logzilla.old/license.txt .
cd to scripts and install
NOTE: DO NOT TRY TO INSTALL TO THE SAME DB AS YOUR OLD ONE
cd scripts
./install
========================================
LogZilla Installation
========================================
Enter the MySQL root username [root]:
Note: Mysql passwords with a ' in them may not work
Enter the password for root [mysql]:
Database to install to [syslog]: newdb # install to OTHER database than what you currently have, such as "newdb"
Update new DB
After the install completes, jump into the database and perform the following tasks:
mysql newdb insert into logs (host,facility,severity,program,msg,mne,suppress,counter,fo,lo) select host,facility,severity,program,msg,mne,suppress,counter,fo,lo from syslog.logs; insert into logs_archive (host,facility,severity,program,msg,mne,suppress,counter,fo,lo) select host,facility,severity,program,msg,mne,suppress,counter,fo,lo from syslog.logs_archive; REPLACE into cache select * from syslog.cache; REPLACE into programs select * from syslog.programs; REPLACE into mne select * from syslog.mne; REPLACE into hosts select * from syslog.hosts; REPLACE INTO settings SELECT * FROM syslog.settings where name not like 'VERSION%' and name not like 'PATH%'; REPLACE INTO ui_layout SELECT * FROM syslog.ui_layout; REPLACE INTO users SELECT * FROM syslog.users; REPLACE INTO groups SELECT * FROM syslog.groups;
Set up Sphinx
Two options here, recompile or copy.
Recompile:
cd /var/www/logzilla/sphinx/src tar xzvf sphinx-0.9.9.tar.gz cd sphinx-0.9.9 ./configure --prefix `pwd`/../.. make && make install
Copy (much faster/easier):
cd /var/www/logzilla/sphinx cp -rp /var/www/logzilla.old/sphinx/bin .
Run Sphinx
cd to Sphinx, kill the old search daemon and re-index:
cd /var/www/logzilla/sphinx pkill searchd (or use ps -ef to find the process and kill searchd) ./indexer.sh full /www/logzilla/sphinx/bin/searchd -c /www/logzilla/sphinx/sphinx.conf
Restart syslog-ng
/etc/init.d/syslog-ng restart
Log in and test!
(if you were already logged in, logout to reset your session variables)
Upgrading from 3.0.75 to 3.0.79
Patching your current Installation
The following patch will upgrade you from 3.0.75 to 3.0.79, not that this will not create a perfect patch, you will need to EDIT the patch file and update the proper paths defined (change occurrences of "path_to_log*"
I can't make any guarantees that this will work, so backup your logzilla/ directory and database before you begin.
You can run the patch using (assuming you named the file logzilla_3.0.75_to_3.0.79.patch):
patch -p0 < logzilla_3.0.75_to_3.0.79.patch
NOTE: In the text below, you will need to replace 4 occurrences of "/var/www/logzilla" with your installed path if you installed it in a location other than that. Contents of the patch:
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/html/CHANGELOG logzilla.3.0.79/html/CHANGELOG
--- logzilla/html/CHANGELOG 2010-05-16 17:01:37.000000000 -0400
+++ logzilla.3.0.79/html/CHANGELOG 2010-06-21 00:25:20.000000000 -0400
@@ -1,5 +1,30 @@
CHANGELOG
+3.0.79 - update
+ - Added regex for PIX firewalls to db_insert
+ - Added regex to remove HEX 7f Characters in NT Event Logs
+
+3.0.78 - update
+ - Added test to make sure the json_encode() function was available. Some Gentoo systems don't have it enabled.
+
+3.0.77 - updates
+ - Finished working out the auto-partitioning and archive table routines
+ SPECIAL NOTE: on larger systems, you may need to adjust the timing of each called procedure.
+ This can be changed in install.pl in the "# Create Partition events" section.
+ For now, they are staggered at midnight, 12:10 and 12:15
+
+3.0.76 - updates
+ - Modified contribs/logreplay to allow a limit on number of hosts that will be generated,
+ default is 50 so you don't overrun the demo license during testing.
+ - Modified contribs/genreport to allow the types of reports to generate (total messages, average per hour, etc.).
+ - Updated Demo License to allow for 500kmpd, 50 hosts, Adhoc Charts and LDAP auth. Expires July 9th, 2010.
+ - Added quotes to dbrootpass in install.pl to help with passwords (see http://forum.logzilla.info/index.php/topic,179.0.html)
+ - DB: Modified the way events and partitions get automatically created/dropped.
+ - DB: Added an ARCHIVE table to move old events to before dropping them.
+ - DB: Added an index to the FO and LO columns
+ - Fixed bug in db_insert.pl that was not importing any remaining messages at the end of STDIN (program exit)
+ - Fixed bug in db_insert.pl that would sometimes insert a duplicate of the same message
+
3.0.75 - *RELEASE VERSION*
- Released May 16th, 2010
- Install Guide: http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.0
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/html/includes/portlets/portlet-table.php logzilla.3.0.79/html/includes/portlets/portlet-table.php
--- logzilla/html/includes/portlets/portlet-table.php 2010-05-18 16:06:50.000000000 -0400
+++ logzilla.3.0.79/html/includes/portlets/portlet-table.php 2010-06-15 00:28:20.000000000 -0400
@@ -533,11 +533,11 @@
}
// Link to LZECS if info is available
if($_SESSION['MSG_EXPLODE'] == "1") {
- // echo "<td class=\"s_td wide\"><a onclick=\"lzecs(this); return false\" id='$msg' href=\"javascript:void(0);\">[LZECS] </a>$explode_url</td>\n";
+ // echo "<td class=\"s_td wide\"><a onclick=\"lzecs(this); return false\" id='$msg' href=\"javascript:void(0);\">[LZECS] </a>$explode_url</td>\n";
echo "<td class=\"s_td wide\">$explode_url</td>\n";
} else {
- // echo "<td class=\"s_td wide\"><a onclick=\"lzecs(this); return false\" id='$msg' href=\"javascript:void(0);\">[LZECS] </a>$msg</td>\n";
- echo "<td class=\"s_td wide\">$msg</td>\n";
+ // echo "<td class=\"s_td wide\"><a onclick=\"lzecs(this); return false\" id='$msg' href=\"javascript:void(0);\">[LZECS] </a>$msg</td>\n";
+ echo "<td class=\"s_td wide\">$msg</td>\n";
}
if ($_SESSION['DEDUP'] == 1) {
echo "<td class=\"s_td\">$row[fo]</td>\n";
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/html/index.php logzilla.3.0.79/html/index.php
--- logzilla/html/index.php 2010-05-16 16:59:50.000000000 -0400
+++ logzilla.3.0.79/html/index.php 2010-06-17 16:00:03.000000000 -0400
@@ -14,21 +14,12 @@
include_once ("includes/modules/functions.security.php");
}
-
session_start();
$_SERVER = cleanArray($_SERVER);
$_POST = cleanArray($_POST);
$_GET = cleanArray($_GET);
$_COOKIE = cleanArray($_COOKIE);
-if(!extension_loaded('ionCube Loader')){
- echo "The IONCube loader is missing, please follow the instructions below for your architecture:<br>";
- echo "<a href=\"".$_SESSION['SITE_URL']."ioncube\">32 bit OS</a><br>";
- echo "<a href=\"".$_SESSION['SITE_URL']."ioncube_64\">64 bit OS</a><br>";
- echo "<a href=\"http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.0#IONCube_Licensing\">Online Guide</a>";
- exit;
-}
-
secure();
$time_start = get_microtime();
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/html/KNOWN_BUGS_and_TODO logzilla.3.0.79/html/KNOWN_BUGS_and_TODO
--- logzilla/html/KNOWN_BUGS_and_TODO 2010-05-09 18:27:16.000000000 -0400
+++ logzilla.3.0.79/html/KNOWN_BUGS_and_TODO 2010-06-14 16:17:15.000000000 -0400
@@ -3,7 +3,3 @@
* Can't set height on chart portlets to 100% so I have to set a hard number - this results in being unable to resize vertically.
* Time picker doesn't recognize a date flip (i.e. if you select from 23:00 to 01:00 the next day, the timepicker does not update the datepicker to the next day)
* Can't drag a scroll bar down - it tries to move the portal - you have to use the mouse scroll.
-
-
-TODO:
-* Create documentation for an Administration Guide.
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/html/login.php logzilla.3.0.79/html/login.php
--- logzilla/html/login.php 2010-04-27 18:18:08.000000000 -0400
+++ logzilla.3.0.79/html/login.php 2010-06-17 16:00:03.000000000 -0400
@@ -44,6 +44,18 @@
//End security update v0.1
+if(!extension_loaded('ionCube Loader')){
+ echo "The IONCube loader is missing, please follow the instructions below for your architecture:<br>";
+ echo "<a href=\"".$_SESSION['SITE_URL']."ioncube\">32 bit OS</a><br>";
+ echo "<a href=\"".$_SESSION['SITE_URL']."ioncube_64\">64 bit OS</a><br>";
+ echo "<a href=\"http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.0#IONCube_Licensing\">Online Guide</a>";
+ exit;
+}
+
+if (!function_exists('json_encode')) {
+ echo "<div style='align: center; text-align: center; border: 3px dotted red;'>ERROR!<br>Your version of PHP does not have json_encode(), which LogZilla requires.</div>\n";
+}
+
if($_SESSION['AUTHTYPE'] == "none") {
$username = "local_noauth";
$sessionId = session_id();
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/README logzilla.3.0.79/README
--- logzilla/README 2010-04-27 21:21:45.000000000 -0400
+++ logzilla.3.0.79/README 2010-06-14 16:17:15.000000000 -0400
@@ -4,3 +4,13 @@
An installation guide can be found at http://nms.gdd.net/index.php/Install_Guide_for_LogZilla_v3.0
Forums are located at http://forum.logzilla.info
+You can get a temporary license from http://code.google.com/p/php-syslog-ng/downloads/list
+I'm still working on the store (http://logzilla.info) which will allow you to download a license for 1 year.
+
+Please be sure to join the forum for announcements - http://forum.logzilla.info
+Alternatively, you can join the Google email list by sending an email to php-syslog-ng-support+subscribe@googlegroups.com
+
+This software is made available to end users under two licenses:
+A free, open-source version and a commercial version.
+For inquiries about purchasing a commercial license, please email cdukes@cdukes.com
+
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/contrib/daily_text_report/instructions.txt logzilla.3.0.79/scripts/contrib/daily_text_report/instructions.txt
--- logzilla/scripts/contrib/daily_text_report/instructions.txt 1969-12-31 19:00:00.000000000 -0500
+++ logzilla.3.0.79/scripts/contrib/daily_text_report/instructions.txt 2010-06-14 16:17:15.000000000 -0400
@@ -0,0 +1,2 @@
+Please see http://forum.logzilla.info/index.php?topic=174.new for full
+details on using this script.
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/contrib/daily_text_report/mail_daily_counts.pl logzilla.3.0.79/scripts/contrib/daily_text_report/mail_daily_counts.pl
--- logzilla/scripts/contrib/daily_text_report/mail_daily_counts.pl 1969-12-31 19:00:00.000000000 -0500
+++ logzilla.3.0.79/scripts/contrib/daily_text_report/mail_daily_counts.pl 2010-06-14 16:17:15.000000000 -0400
@@ -0,0 +1,128 @@
+#!/usr/bin/perl
+
+#
+# LogZilla Daily Report
+#
+# Developed by Clayton Dukes <cdukes@cdukes.com>
+# Copyright (c) 2010 LogZilla, LLC
+# All rights reserved.
+#
+# Changelog:
+# 2010-05-24 - created
+#
+# Note that this requires the MIME::Lite and Text::Tabulate packages which can easily be installed by typing:
+# cpan MIME::Lite Text::Tabulate
+# from the linux command line.
+
+use strict;
+use warnings;
+use POSIX qw/strftime/;
+use DBI;
+use MIME::Lite;
+use Text::Tabulate;
+my $tab = new Text::Tabulate();
+$tab->configure(-tab => "\t", gutter => ' = ');
+
+
+my $now = strftime("%Y-%m-%d %H:%M:%S", localtime);
+my $today = strftime("%Y-%m-%d", localtime);
+
+####### MODIFY below to suit your needs ##############
+my $smtphost = "localhost";
+my $from = 'root@localhost.com';
+my $to = 'cdukes@cdukes.com';
+my $subject = "LogZilla Daily Report - $now";
+my $body;
+my $basepath = "/path_to_logzilla"; ## CHANGE THIS!
+####### MODIFY above to suit your needs ##############
+
+my ($db, $dbhost, $dbport, $dbuser, $dbpass, $dbtable, @ids, $dbids);
+my $config = "$basepath/html/config/config.php";
+
+open( CONFIG, $config );
+my @config = <CONFIG>;
+close( CONFIG );
+
+foreach my $var (@config) {
+ next unless $var =~ /DEFINE/; # read only def's
+ $dbuser = $1 if ($var =~ /'DBADMIN', '(\w+)'/);
+ $dbpass = $1 if ($var =~ /'DBADMINPW', '(\w+)'/);
+ $db = $1 if ($var =~ /'DBNAME', '(\w+)'/);
+ $dbhost = $1 if ($var =~ /'DBHOST', '(\w+.*|\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})'/);
+ $dbport = $1 if ($var =~ /'DBPORT', '(\w+)'/);
+}
+my $dbh = DBI->connect( "DBI:mysql:$db:$dbhost", $dbuser, $dbpass );
+if (!$dbh) {
+ print "Can't connect to $db database: ", $DBI::errstr, "\n";
+ exit;
+}
+my $sth = $dbh->prepare("SELECT name,value FROM settings");
+$sth->execute();
+if ($sth->errstr()) {
+ print "FATAL: Unable to execute SQL statement: ", $sth->errstr(), "\n";
+ exit;
+}
+while (my @settings = $sth->fetchrow_array()) {
+ $dbtable = $settings[1] if ($settings[0] =~ /^TBL_MAIN$/);
+}
+
+my $q_msgs_total= $dbh->prepare("SELECT value FROM cache WHERE name='msg_sum'");
+my $q_msgs_last24= $dbh->prepare("SELECT SUM(value) AS last24 FROM cache WHERE name LIKE 'chart_mph_%' AND updatetime BETWEEN NOW() - INTERVAL 23 HOUR and NOW() - INTERVAL 0 HOUR");
+my $q_msgs_today= $dbh->prepare("SELECT SUM(value) FROM cache WHERE name LIKE 'chart_mph_%'");
+my $q_msgs_lasthour= $dbh->prepare("SELECT SUM(counter) AS lasthour FROM $dbtable WHERE lo BETWEEN NOW() - INTERVAL 2 HOUR and NOW() - INTERVAL 1 HOUR");
+my $q_msgs_thishour= $dbh->prepare("SELECT SUM(counter) AS thishour FROM $dbtable WHERE lo BETWEEN NOW() - INTERVAL 1 HOUR and NOW() - INTERVAL 0 HOUR");
+my $q_msgs_avg_perhour= $dbh->prepare("SELECT ROUND(SUM(value)/24) AS avg_last24 FROM cache WHERE name LIKE 'chart_mph_%' AND updatetime BETWEEN NOW() - INTERVAL 23 HOUR and NOW() - INTERVAL 0 HOUR;");
+my $q_top20_hosts_today = $dbh->prepare("SELECT host,SUM(counter) as count FROM $dbtable WHERE lo BETWEEN CONCAT(CURDATE(), ' 00:00:00') AND CONCAT(CURDATE(), ' 23:59:59') GROUP BY host ORDER BY count DESC LIMIT 20");
+
+$q_msgs_total->execute();
+$q_msgs_last24->execute();
+$q_msgs_today->execute();
+$q_msgs_lasthour->execute();
+$q_msgs_thishour->execute();
+$q_msgs_avg_perhour->execute();
+$q_top20_hosts_today->execute();
+my $total = $q_msgs_total->fetchrow_array();
+my $total24 = $q_msgs_last24->fetchrow_array();
+my $total_today = $q_msgs_today->fetchrow_array();
+my $lasthour = $q_msgs_lasthour->fetchrow_array();
+my $thishour = $q_msgs_thishour->fetchrow_array();
+my $mph_avg = $q_msgs_avg_perhour->fetchrow_array();
+my @top20;
+while (my $ref = $q_top20_hosts_today->fetchrow_hashref()) {
+ push(@top20, $ref->{'host'} ."\t". $ref->{'count'});
+}
+$total = "No Data" if (!$total);
+$total24 = "No Data" if (!$total24);
+$total_today = "No Data" if (!$total_today);
+$lasthour = "No Data" if (!$lasthour);
+$thishour = "No Data" if (!$thishour);
+$mph_avg = "No Data" if (!$mph_avg);
+$body .= "Messages received - Total: $total\n";
+$body .= "Messages received - Last 24 hours: $total24\n";
+$body .= "Messages received - Since Midnight: $total_today\n";
+$body .= "Messages received - Last hour: $lasthour\n";
+$body .= "Messages received - This hour: $thishour\n";
+$body .= "Messages per hour - Average: $mph_avg\n";
+$body .= "\n";
+$body .= "Top 20 Hosts Today\n";
+$body .= "-------------------------------------\n";
+my @out = $tab->format (@top20);
+foreach my $l (@out) {
+ $body .= "$l\n";
+}
+$body .= "-------------------------------------\n";
+
+### Start with a simple text message:
+my $msg = MIME::Lite->new(
+ From =>"$from",
+ To =>"$to",
+ Subject =>"$subject",
+ Type =>'TEXT',
+ Data =>"$body"
+);
+
+#$msg->send('smtp','localhost', Debug=>1 );
+$msg->send('smtp',"$smtphost");
+print "Mail Sent\n";
+
+# END
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/contrib/genreport/genreport.pl logzilla.3.0.79/scripts/contrib/genreport/genreport.pl
--- logzilla/scripts/contrib/genreport/genreport.pl 2010-05-26 14:33:03.000000000 -0400
+++ logzilla.3.0.79/scripts/contrib/genreport/genreport.pl 2010-06-21 22:33:56.000000000 -0400
@@ -32,6 +32,7 @@
my $subject = 'LogZilla Excel Report';
my $body = "Report generated on $now";
my $basepath = "/var/www/logzilla";
+my $smtphost = "localhost";
####### MODIFY above to suit your needs ##############
my ($db, $dbhost, $dbport, $dbuser, $dbpass, $dbtable, @ids, $dbids);
@@ -43,7 +44,7 @@
close( CONFIG );
foreach my $var (@config) {
- next unless $var =~ /^DEFINE/; # read only def's
+ next unless $var =~ /DEFINE/; # read only def's
$dbuser = $1 if ($var =~ /'DBADMIN', '(\w+)'/);
$dbpass = $1 if ($var =~ /'DBADMINPW', '(\w+)'/);
$db = $1 if ($var =~ /'DBNAME', '(\w+)'/);
@@ -113,7 +114,7 @@
);
#$msg->send('smtp','localhost', Debug=>1 );
-$msg->send('smtp','localhost');
+$msg->send('smtp',"$smtphost");
print "Mail Sent\n";
# Delete the temp file
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/contrib/logreplay/logreplay.pl logzilla.3.0.79/scripts/contrib/logreplay/logreplay.pl
--- logzilla/scripts/contrib/logreplay/logreplay.pl 2010-05-16 16:59:50.000000000 -0400
+++ logzilla.3.0.79/scripts/contrib/logreplay/logreplay.pl 2010-06-15 00:28:20.000000000 -0400
@@ -23,7 +23,7 @@
#
# Declare variables to use
#
-my ($InputFileName, $host, $mne, $msg, $sev, @sevs, $month, @month, $day, $time, $seq, $spoof, $randhost, , $sleep_end, $sleep, $desthost);
+my ($InputFileName, $host, $mne, $msg, $sev, @sevs, $month, @month, $day, $time, $seq, $spoof, $randhost, , $sleep_end, $sleep, $desthost, $hostlimit);
use vars qw/ %opt /;
#
# Command line options processing
@@ -31,14 +31,15 @@
sub init()
{
use Getopt::Std;
- my $opt_string = 'hrvt:f:s:e:d:';
+ my $opt_string = 'hrvt:f:s:e:d:l:';
getopts( "$opt_string", \%opt ) or usage();
usage() if $opt{h};
$InputFileName = $opt{'f'} or usage();
$spoof = $opt{'s'} or usage();
$randhost = $opt{'r'};
- $sleep = defined($opt{'t'}) ? $opt{'t'} : '1';
- $desthost = defined($opt{'d'}) ? $opt{'d'} : '127.0.0.1';
+ $hostlimit = defined($opt{'l'}) ? $opt{'l'} : '50';
+ $sleep = defined($opt{'t'}) ? $opt{'t'} : '1';
+ $desthost = defined($opt{'d'}) ? $opt{'d'} : '127.0.0.1';
$sleep_end = $opt{'e'};
}
#
@@ -63,6 +64,17 @@
}
init();
+my @hosts;
+sub array_unique
+{
+ my @list = @_;
+ my %finalList;
+ foreach(@list)
+ {
+ $finalList{$_} = 1; # delete double values
+ }
+ return (keys(%finalList));
+}
# My syslog looks like this, you may need to change the regex below to match yours
#Jun 19 05:10:58 netcontrol_3750.some.domain 117475: Jun 19 05:10:57: %DUAL-5-NBRCHANGE: IP-EIGRP(0) 1024: Neighbor 10.15.213.61 (Vlan40) is down: Interface Goodbye received
my $regex = qr/([\w\.\_\-]+\.[\w\.\_\-]+|\d+\.\d+\.\d+\.\d+)(.*)(%.*?:)(.*)/;
@@ -70,99 +82,106 @@
open(FILE, $InputFileName) || die("Can't open $InputFileName : $!\nTry $0 -h\n");
my $count = 0;
while(<FILE>) {
- if ($_ =~ m/$regex/) {
- $host = $1;
- # As of this version of the script, the following vars aren't used:
- # $month
- # $day
- # $time
- # I would like to figure out a way to rewite the syslog packet to show these fields but can't figure out how just yet.
- # if you know how, please tell me :-)
- $mne = $3;
- $msg = $4;
- $seq = $2; #try to get a sequence # (some messages won't have them)
- $seq =~ s/(\d+).*:/$1/;
- if ($seq !~ /\d+/) {
- $seq = "";
- }
- print STDOUT "SEQ: $seq\n" if $opt{v};
- print STDOUT "HOST: $host\n" if $opt{v};
- print STDOUT "MNE: $mne\n" if $opt{v};
- print STDOUT "MSG: $msg\n" if $opt{v};
- @month = split(' ', $_);
- @sevs = split('-', $mne);
- $day = $month[1];
- $time = $month[2];
- switch ($month[0]) {
- case "Jan" { $month = "01" }
- case "Feb" { $month = "02" }
- case "Mar" { $month = "03" }
- case "Apr" { $month = "04" }
- case "May" { $month = "05" }
- case "Jun" { $month = "06" }
- case "Jul" { $month = "07" }
- case "Aug" { $month = "08" }
- case "Sep" { $month = "09" }
- case "Oct" { $month = "10" }
- case "Nov" { $month = "11" }
- case "Dec" { $month = "12" }
- else { print STDOUT "Unable to determine Month!" }
- }
- if ($sevs[1] != /\d+/) {
- $sev = $sevs[2]; # some messages contain 2 dashes such as: %PM-SP-4-ERR_RECOVER:
- }
- switch ($sevs[1]) {
- case 0 { $sev = 0 }
- case 1 { $sev = 1 }
- case 2 { $sev = 2 }
- case 3 { $sev = 3 }
- case 4 { $sev = 4 }
- case 5 { $sev = 5 }
- case 6 { $sev = 6 }
- case 7 { $sev = 7 }
- else { print STDOUT "Unable to determine Severity! $sevs[1]\n$_" }
- exit;
- }
- if ($host !~ /^([\d]+)\.([\d]+)\.([\d]+)\.([\d]+)$/) {
- print "$host not an IP address\n";
- next;
- }
- my $notIP = 0;
- foreach my $s (($1, $2, $3, $4)) {
- #print "s=$s;";
- if (0 > $s || $s > 255) {
- $notIP = 1;
- last;
+ if (&array_unique(@hosts) < $hostlimit) {
+ if ($_ =~ m/$regex/) {
+ $host = $1;
+ # As of this version of the script, the following vars aren't used:
+ # $month
+ # $day
+ # $time
+ # I would like to figure out a way to rewite the syslog packet to show these fields but can't figure out how just yet.
+ # if you know how, please tell me :-)
+ $mne = $3;
+ $msg = $4;
+ $seq = $2; #try to get a sequence # (some messages won't have them)
+ $seq =~ s/(\d+).*:/$1/;
+ if ($seq !~ /\d+/) {
+ $seq = "";
}
- }
- if ($notIP) {
- print "\n$host is not a valid IP address\n";
- } else {
- #print "\n$host is an IP address\n";
- if ($randhost) {
- $host = "$1.$2.$3." . int(rand(254));
- #print "\nNewIp = $host\n";
+ print STDOUT "SEQ: $seq\n" if $opt{v};
+ print STDOUT "HOST: $host\n" if $opt{v};
+ print STDOUT "MNE: $mne\n" if $opt{v};
+ print STDOUT "MSG: $msg\n" if $opt{v};
+ @month = split(' ', $_);
+ @sevs = split('-', $mne);
+ $day = $month[1];
+ $time = $month[2];
+ switch ($month[0]) {
+ case "Jan" { $month = "01" }
+ case "Feb" { $month = "02" }
+ case "Mar" { $month = "03" }
+ case "Apr" { $month = "04" }
+ case "May" { $month = "05" }
+ case "Jun" { $month = "06" }
+ case "Jul" { $month = "07" }
+ case "Aug" { $month = "08" }
+ case "Sep" { $month = "09" }
+ case "Oct" { $month = "10" }
+ case "Nov" { $month = "11" }
+ case "Dec" { $month = "12" }
+ else { print STDOUT "Unable to determine Month!" }
+ }
+ if ($sevs[1] != /\d+/) {
+ $sev = $sevs[2]; # some messages contain 2 dashes such as: %PM-SP-4-ERR_RECOVER:
+ }
+ switch ($sevs[1]) {
+ case 0 { $sev = 0 }
+ case 1 { $sev = 1 }
+ case 2 { $sev = 2 }
+ case 3 { $sev = 3 }
+ case 4 { $sev = 4 }
+ case 5 { $sev = 5 }
+ case 6 { $sev = 6 }
+ case 7 { $sev = 7 }
+ else { print STDOUT "Unable to determine Severity! $sevs[1]\n$_" }
+ exit;
+ }
+ if ($host !~ /^([\d]+)\.([\d]+)\.([\d]+)\.([\d]+)$/) {
+ print "$host not an IP address\n";
+ next;
}
+ my $notIP = 0;
+ foreach my $s (($1, $2, $3, $4)) {
+ #print "s=$s;";
+ if (0 > $s || $s > 255) {
+ $notIP = 1;
+ last;
+ }
+ }
+ if ($notIP) {
+ print "\n$host is not a valid IP address\n";
+ } else {
+ #print "\n$host is an IP address\n";
+ if ($randhost) {
+ $host = "$1.$2.$3." . int(rand(254));
+ #print "\nNewIp = $host\n";
+ }
+ }
+ print STDOUT "Month: $month\n" if $opt{v};
+ print STDOUT "Day: $day\n" if $opt{v};
+ print STDOUT "Time: $time\n" if $opt{v};
+ print STDOUT "SEV: $sev\n" if $opt{v};
+ print STDOUT "FULL TEXT:\n$_\n" if $opt{v};
+ system("$spoof $host $desthost \"NMS_Replay[$$]: $mne $msg\" " . $sev);
+ my $sleeptime;
+ if ($sleep_end) {
+ $sleeptime = ($sleep + rand($sleep_end));
+ } else {
+ $sleeptime = $sleep;
+ }
+ print "Sleeping for $sleeptime\n";
+ select( undef, undef, undef, $sleeptime );
+ push (@hosts, $host);
+ } else {
+ # If something goes wrong
+ print "INVALID MESSAGE FORMAT:\n$_\n" if $opt{v};
}
- print STDOUT "Month: $month\n" if $opt{v};
- print STDOUT "Day: $day\n" if $opt{v};
- print STDOUT "Time: $time\n" if $opt{v};
- print STDOUT "SEV: $sev\n" if $opt{v};
- print STDOUT "FULL TEXT:\n$_\n" if $opt{v};
- system("$spoof $host $desthost \"NMS_Replay[$$]: $mne $msg\" " . $sev);
- my $sleeptime;
- if ($sleep_end) {
- $sleeptime = ($sleep + rand($sleep_end));
- } else {
- $sleeptime = $sleep;
- }
- print "Sleeping for $sleeptime\n";
- select( undef, undef, undef, $sleeptime );
+ $count++;
} else {
- # If something goes wrong
- print STDOUT "INVALID MESSAGE FORMAT:\n$_\n" if $opt{v};
+ print "\n\nHost limit of $hostlimit reached, use $0 -l to set a higher limit\n";
+ print "Sent $count messages out\n";
+ exit;
}
- $count++;
}
print "Sent $count messages out\n";
close (FILE);
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/db_insert.pl logzilla.3.0.79/scripts/db_insert.pl
--- logzilla/scripts/db_insert.pl 2010-05-26 14:33:03.000000000 -0400
+++ logzilla.3.0.79/scripts/db_insert.pl 2010-06-21 22:34:18.000000000 -0400
@@ -46,6 +46,7 @@
# 2010-04-14 - REMOVED Tail::File and daemonize. Calling db_insert.pl directly from syslog-ng provided much better insert rates (now at 20kmps)
# 2010-04-20 - Replaced re_pipe with better fields from syslog-ng (only need host, pri, ts, prg and msg)
# 2010-04-29 - Added regex for Snare windows events
+# 2010-06-13 - Fixed bug that was inserting duplicate messages
#
@@ -66,7 +67,7 @@
use vars qw/ %opt /;
# Set command line vars
-my ($debug, $config, $logfile, $verbose, $dbh, $selftest, $qsize);
+my ($debug, $config, $logfile, $verbose, $dbh);
#
# Command line options processing
@@ -74,14 +75,12 @@
sub init()
{
use Getopt::Std;
- my $opt_string = 'hd:c:l:svq:';
+ my $opt_string = 'hd:c:l:v';
getopts( "$opt_string", \%opt ) or usage();
usage() if $opt{h};
$debug = defined($opt{'d'}) ? $opt{'d'} : '0';
$logfile = $opt{'l'} if $opt{'l'};
$verbose = $opt{'v'} if $opt{'v'};
- $qsize = $opt{'q'} if $opt{'q'};
- $selftest = $opt{'s'} if $opt{'s'};
$config = defined($opt{'c'}) ? $opt{'c'} : "/var/www/logzilla/html/config/config.php";
}
@@ -101,11 +100,6 @@
-l : log file (default used from config.php if not set here)
-c : config file (overrides the default config.php file location set in the '\$config' variable in this script)
example: $0 -l /var/log/foo.log -d 5 -c /var/www/logzilla/html/config/config.php -v -t /var/log/syslog
-
- -s : **Special Option**:
- This option may be used to run a self test
- You can run a self test by typing:
- $0 -s -c /var/www/logzilla/html/config/config.php (replace with the path to your config)
EOF
exit;
}
@@ -153,6 +147,9 @@
$q_time = $settings[1] if ($settings[0] =~ /^Q_TIME$/);
$q_limit = $settings[1] if ($settings[0] =~ /^Q_LIMIT$/);
}
+# cdukes: 2010-06-07: Manually set q_time and q_limit for testing
+#$q_time = 15;
+#$q_limit = 10;
# If debug is set in the settings table, then increment debug to at least 1
if ($DEBUG > "0") {
@@ -200,7 +197,7 @@
}
my ($host, %host_cache, $facility, $pri, $prg, %program_cache, $prg32, $msg, $mne, %mne_cache, $mne32, $severity);
-my $re_pipe = qr/(\S+)\t(\d+)\t(\S+)\t(.*)/;
+my $re_pipe = qr/(\S+)\t(\d+)\t(\S+)?\t(.*)/;
# v3.0 Fields are: Host, PRI, Program, and MSG
# the $severity and $facility fields are split from the $pri coming in so that they can be stored as integers into 2 separate db columns
# re_mne is used to capture Cisco Mnemonics
@@ -242,37 +239,9 @@
my $sumcount;
my $now;
-open (DUMP, ">$dumpfile") or die "can't open $dumpfile: $!\n";
-close (DUMP);
+#open (DUMP, ">$dumpfile") or die "can't open $dumpfile: $!\n";
+#close (DUMP);
$db_load->{TraceLevel} = 4 if (($debug > 4) and ($verbose));
-if ($selftest) {
- my $sth = $dbh->prepare("
- DELETE from $dbtable where host='dbins_testhost';
- ") or die "Could not delete old test results: $DBI::errstr";
- $sth->execute;
- my $cmd = "$0";
- $cmd .= " -d 3"; # Force debug on so test results are shown
- $cmd .= " -c " . $opt{'c'} if $opt{'c'};
- $cmd .= " -l " . $opt{'l'} if $opt{'l'};
- $cmd .= " -v "; # Force verbose mode so results are printed to screen
- print STDOUT "\nPERFORMING SELF TEST USING COMMAND: $cmd\n\n";
- my $res = `printf "\n" | $cmd -q 1`;
- print STDOUT "Database Lookup (resulting message should be from Fred):\n";
- my $sth = $dbh->prepare("
- SELECT msg FROM $dbtable ORDER BY id DESC LIMIT 1;
- ") or die "Could not fetch last row: $DBI::errstr";
- $sth->execute;
- if (my $res = $sth->fetchrow_array() ) {
- print STDOUT "$res\n";
- print STDOUT "SELF TEST COMPLETE!\n";
- } else {
- print STDOUT "Test Failed\n";
- }
- exit;
-}
-if ($qsize) {
- $q_limit = $qsize;
-}
# Pre-populate cache's with db values
my $prg_select = $dbh->prepare("SELECT * FROM programs");
$prg_select->execute();
@@ -290,17 +259,28 @@
$mne_cache{$ref->{'name'}} = $ref->{'crc'};
}
while (my $msg = <STDIN>) {
- chomp($msg);
- my $now = strftime("%Y-%m-%d %H:%M:%S", localtime);
- print LOG "\n\n-=-=-=-=-=-=-=\nLOOP START: $now\n" if ($debug > 10);
- if ($qsize) {
- for (my $i=0; $i <= 5; $i++) {
- push (@dumparr, "dbins_testhost\t86\tdb_insert.pl\tdb_insert.pl[$$]: %SYS-5-CONFIG_I: Configured from 172.16.0.123 by Fred Flinstone <fred\@flinstone.com>\tSYS-5-CONFIG_I\t$datetime\t$datetime\t\n");
+ push(@dumparr, do_msg($msg));
+ if (eof()) { # check for end of last file
+ open (DUMP, ">$dumpfile") or die "can't open $dumpfile: $!\n";
+ print LOG "EOF - Flushing buffer\n" if ($debug > 0);
+ print STDOUT "EOF - Flushing buffer\n" if ($debug > 0);
+ print STDOUT "Importing $#dumparr messages into the database\n" if ($debug > 0);
+ print LOG "Importing $#dumparr messages into the database\n" if ($debug > 0);
+ print DUMP @dumparr;
+ undef (@dumparr);
+ close (DUMP);
+ $db_load->execute();
+ if ($db_load->errstr()) {
+ print STDOUT "FATAL: Unable to execute SQL statement: ", $db_load->errstr(), "\n" if ($debug > 0);
}
+ print LOG "Ending insert: " . strftime("%H:%M:%S", localtime) ."\n" if ($debug > 0);
+ print STDOUT "Ending insert: " . strftime("%H:%M:%S", localtime) ."\n" if (($debug > 0) and ($verbose));
}
+ my $now = strftime("%Y-%m-%d %H:%M:%S", localtime);
+ print LOG "\n\n-=-=-=-=-=-=-=\nLOOP START: $now\n" if ($debug > 10);
$mps++;
- if (($#dumparr < $q_limit) && ($start_time <= $time_limit)) {
- push(@dumparr, do_msg($msg));
+ #print STDOUT $#dumparr."\n";
+ if ((($#dumparr + 1) < $q_limit) && ($start_time <= $time_limit)) {
print LOG "DEBUG: Pushing message into the array because either the size of the dumparr is < Q_limit or the start time <= time limit\n" if ($debug > 10);
print LOG "DEBUG: Dump array size = ".$#dumparr."\n" if ($debug > 10);
print LOG "DEBUG: Q Limit set to ".$q_limit."\n" if ($debug > 10);
@@ -310,46 +290,40 @@
print LOG "DEBUG: *NEW* Start Time is ".$start_time."\n" if ($debug > 10);
} else {
print LOG "DEBUG: Limit reached, processing queue\n" if ($debug > 10);
- if ($#dumparr >= 0 ) {
- if ($start_time >= $time_limit) {
- print STDOUT "\n\nQueue time limit reached ($q_time seconds)\n" if ($debug > 0) ;
- print LOG "\n\nQueue time limit reached ($q_time seconds)\n" if ($debug > 0);
+ if ($start_time >= $time_limit) {
+ print STDOUT "\n\nQueue time limit reached ($q_time seconds)\n" if ($debug > 0) ;
+ print LOG "\n\nQueue time limit reached ($q_time seconds)\n" if ($debug > 0);
+ } else {
+ my $t = ($end_time - $start_time);
+ if ($t > 0) {
+ $tmp_mps = round($q_limit / $t);
} else {
- my $t = ($end_time - $start_time);
- if ($t > 0) {
- $tmp_mps = round($q_limit / $t);
- } else {
- $tmp_mps = round($q_limit / 1);
- }
- print LOG "\n\nQueue Limit Reached: $q_limit messages in $t seconds ($tmp_mps MPS)\n" if ($debug > 0);
- print STDOUT "\n\nQueue Limit Reached: $q_limit messages in $t seconds ($tmp_mps MPS)\n" if ($debug > 0);
- }
- foreach my $var (@mps) {
- if ($var =~ m/(.*),(.*),(.*)/) {
- $db_insert_mpX->execute("$1", "$2", "$3");
- print STDOUT "Inserting MPS string: $1, $2, $3\n" if ($debug > 1);
- }
+ $tmp_mps = round($q_limit / 1);
}
- open (DUMP, ">$dumpfile") or die "can't open $dumpfile: $!\n";
- print LOG "Starting insert: " . strftime("%H:%M:%S", localtime) ."\n" if ($debug > 0);
- print STDOUT "Starting insert: " . strftime("%H:%M:%S", localtime) ."\n" if (($debug > 0) and ($verbose));
- print STDOUT "Importing $#dumparr messages into the database\n" if ($debug > 0);
- print LOG "Importing $#dumparr messages into the database\n" if ($debug > 0);
- print DUMP @dumparr;
- close (DUMP);
- $db_load->execute();
- if ($db_load->errstr()) {
- print STDOUT "FATAL: Unable to execute SQL statement: ", $db_load->errstr(), "\n" if ($debug > 0);
- }
- print LOG "Ending insert: " . strftime("%H:%M:%S", localtime) ."\n" if ($debug > 0);
- print STDOUT "Ending insert: " . strftime("%H:%M:%S", localtime) ."\n" if (($debug > 0) and ($verbose));
- @dumparr = ();
- $time_limit = ($start_time + $q_time);
- } else {
- push(@dumparr, do_msg($msg));
- print LOG "DEBUG: SHOULD NOT HIT THIS\nDEBUG:Dump array size = ".$#dumparr."\nDEBUG: Contents = " . @dumparr ."\n" if ($debug > 10);
- print LOG "DEBUG: Current Message is $msg\n" . @dumparr if ($debug > 10);
+ print LOG "\n\nQueue Limit Reached: $q_limit messages in $t seconds ($tmp_mps MPS)\n" if ($debug > 0);
+ print STDOUT "\n\nQueue Limit Reached: $q_limit messages in $t seconds ($tmp_mps MPS)\n" if ($debug > 0);
}
+ foreach my $var (@mps) {
+ if ($var =~ m/(.*),(.*),(.*)/) {
+ $db_insert_mpX->execute("$1", "$2", "$3");
+ print STDOUT "Inserting MPS string: $1, $2, $3\n" if ($debug > 1);
+ }
+ }
+ open (DUMP, ">$dumpfile") or die "can't open $dumpfile: $!\n";
+ print LOG "Starting insert: " . strftime("%H:%M:%S", localtime) ."\n" if ($debug > 0);
+ print STDOUT "Starting insert: " . strftime("%H:%M:%S", localtime) ."\n" if (($debug > 0) and ($verbose));
+ print STDOUT "Importing $#dumparr messages into the database\n" if ($debug > 0);
+ print LOG "Importing $#dumparr messages into the database\n" if ($debug > 0);
+ print DUMP @dumparr;
+ undef (@dumparr);
+ close (DUMP);
+ $db_load->execute();
+ if ($db_load->errstr()) {
+ print STDOUT "FATAL: Unable to execute SQL statement: ", $db_load->errstr(), "\n" if ($debug > 0);
+ }
+ print LOG "Ending insert: " . strftime("%H:%M:%S", localtime) ."\n" if ($debug > 0);
+ print STDOUT "Ending insert: " . strftime("%H:%M:%S", localtime) ."\n" if (($debug > 0) and ($verbose));
+ $time_limit = ($start_time + $q_time);
}
my $mps_timer_end = (time);
my $secs = ($mps_timer_end - $mps_timer_start);
@@ -435,11 +409,8 @@
}
sub do_msg {
$msg = shift;
- if (!$qsize) {
- print LOG "\n\nINCOMING MESSAGE:\n$msg\n" if ($debug > 0);
- print STDOUT "\n\nINCOMING MESSAGE:\n$msg\n" if (($debug > 2) and ($verbose));
- }
-
+ print LOG "\n\nINCOMING MESSAGE:\n$msg\n" if ($debug > 0);
+ print STDOUT "\n\nINCOMING MESSAGE:\n$msg\n" if (($debug > 2) and ($verbose));
# Get current date and time
$datetime_now = strftime("%Y-%m-%d %H:%M:%S", localtime);
@@ -455,6 +426,7 @@
$severity = $pri - ($facility * 8 );
$prg = $3;
$msg = $4;
+ $prg = "Cisco ASA" if ($msg =~ /^%PIX/);
# Handle Snare Format
if ($prg =~ m/MSWinEventLog\\011.*\\011(.*)\\011.*\\011.*/) {
my $facilityname = $1;
@@ -504,6 +476,7 @@
}
$msg =~ s/\\//; # Some messages come in with a trailing slash
$msg =~ s/\t/ /g; # remove any TABs (gotta love windows...)
+ $msg =~ s/\177/ /g; # Fix for NT Events Logs (they send 0x7f with the message)
if ($msg =~ m/$re_mne/) {
$mne = $1;
} else {
@@ -512,7 +485,8 @@
if ($prg =~ m/$re_mne_prg/) { # Attempt to capture Cisco Firewall Mnemonics (they send the mne's as a program)
$mne = $1;
}
- $msg =~ s/[\x00-\x1F\x80-\xFF]//; # Remove any non-printable characters
+ # 2010-05-20: CDUKES - had to remove the non-printable filter below, it was killing German Umlauts.
+ # $msg =~ s/[\x00-\x1F\x80-\xFF]//; # Remove any non-printable characters
$prg =~ s/%ACE.*\d+/Cisco ACE/; # Added because ACE modules don't send their program field properly
$prg =~ s/%ASA.*\d+/Cisco ASA/; # Added because ASA's don't send their program field properly
$prg =~ s/%FWSM.*\d+/Cisco FWSM/; # Added because FWSM's don't send their program field properly
@@ -573,10 +547,8 @@
} else {
# If something gets inserted wrong from the PIPE we'll set host = blank so we can error out later
$host = "";
- if (!$qsize) {
- print LOG "INVALID MESSAGE FORMAT:\n$msg\n" if ($debug > 0);
- print STDOUT "INVALID MESSAGE FORMAT:\n$msg\n" if (($debug > 0) and ($verbose));
- }
+ print LOG "INVALID MESSAGE FORMAT:\n$msg\n" if ($debug > 0);
+ print STDOUT "INVALID MESSAGE FORMAT:\n$msg\n" if (($debug > 0) and ($verbose));
}
# If the SQZ feature is enabled, continue, if not we'll just insert the record afterward
if($dedup eq 1) {
@@ -659,11 +631,9 @@
if ($host ne "") {
$queue = "$host\t$facility\t$severity\t$prg32\t$msg\t$mne32\t$datetime_now\t$datetime_now\t\n";
} else {
- if (!$qsize) {
- $do_msg_mps++;
- print LOG "Error inserting record $msg\n" if ($debug > 3);
- print STDOUT "Error inserting record $msg\n" if (($debug > 3) and ($verbose));
- }
+ $do_msg_mps++;
+ print LOG "Error inserting record $msg\n" if ($debug > 3);
+ print STDOUT "Error inserting record $msg\n" if (($debug > 3) and ($verbose));
}
} else {
print LOG "insert = $insert, Skipping insert of this message since it was a duplicate\n" if ($debug > 3);
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/install.pl logzilla.3.0.79/scripts/install.pl
--- logzilla/scripts/install.pl 2010-05-26 14:32:26.000000000 -0400
+++ logzilla.3.0.79/scripts/install.pl 2010-06-21 00:25:20.000000000 -0400
@@ -11,6 +11,7 @@
# Changelog:
# 2009-11-15 - created
# 2010-10-10 - Modified to work with LogZilla v3.0
+# 2010-06-07 - Modified partitioning and events
#
use strict;
@@ -38,7 +39,7 @@
}
my $version = "3.0";
-my $subversion = ".75";
+my $subversion = ".79";
# Grab the base path
my $lzbase = getcwd;
@@ -51,6 +52,7 @@
my $dbroot = &p("Enter the MySQL root username", "root");
$dbroot = qq{$dbroot};
+print "\nNote: Mysql passwords with a ' in them may not work\n";
my $dbrootpass = &p("Enter the password for $dbroot", "mysql");
$dbrootpass = qq{$dbrootpass};
my $dbname = &p("Database to install to", "syslog");
@@ -59,6 +61,7 @@
my $dbport = &p("Enter the port of the MySQL server", "3306");
my $dbadmin = &p("Enter the name to create as the owner of the $dbtable database", "syslogadmin");
$dbadmin = qq{$dbadmin};
+print "Note that a password containing ' may not work.\n";
my $dbadminpw = &p("Enter the password for the $dbadmin user", "$dbadmin");
$dbadminpw = qq{$dbadminpw};
my $siteadmin = &p("Enter the name to create as the WEBSITE owner", "admin");
@@ -166,49 +169,55 @@
KEY severity (severity),
KEY mne (mne),
KEY program (program),
- KEY suppress (suppress)
+ KEY suppress (suppress),
+ KEY lo (lo),
+ KEY fo (fo)
) ENGINE=MyISAM
") or die "Could not create $dbtable table: $DBI::errstr";
$sth->execute;
# Create sphinx table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/sph_counter.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/sph_counter.sql`;
print $res;
# Create cache table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/cache.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/cache.sql`;
print $res;
# Create hosts table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/hosts.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/hosts.sql`;
print $res;
# Create mnemonics table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/mne.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/mne.sql`;
print $res;
# Create programs table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/programs.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/programs.sql`;
print $res;
# Create suppress table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/suppress.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/suppress.sql`;
print $res;
# Create facilities table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/facilities.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/facilities.sql`;
print $res;
# Create severities table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/severities.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/severities.sql`;
print $res;
# Create ban table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/banned_ips.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/banned_ips.sql`;
+ print $res;
+
+# Create archive table
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/logs_archive.sql`;
print $res;
# Insert settings data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/settings.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/settings.sql`;
print $res;
my $sth = $dbh->prepare("
update settings set value='$url' where name='SITE_URL';
@@ -250,7 +259,7 @@
# Insert user data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/users.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/users.sql`;
print $res;
my $sth = $dbh->prepare("
update users set username='$siteadmin' where username='admin';
@@ -266,31 +275,31 @@
$sth->execute;
# Groups
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/groups.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/groups.sql`;
print $res;
# Insert totd data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/totd.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/totd.sql`;
print $res;
# Insert LZECS data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/lzecs.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/lzecs.sql`;
print $res;
# Insert Suppress data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/suppress.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/suppress.sql`;
print $res;
# Insert ui_layout data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/ui_layout.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/ui_layout.sql`;
print $res;
# Insert help data
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/help.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/help.sql`;
print $res;
# Insert history table
- my $res = `mysql -u$dbroot -p$dbrootpass -h $dbhost -P $dbport $dbname < sql/history.sql`;
+ my $res = `mysql -u$dbroot -p'$dbrootpass' -h $dbhost -P $dbport $dbname < sql/history.sql`;
print $res;
@@ -312,18 +321,36 @@
# Create Partition events
my $event = qq{
- CREATE EVENT `logs_add_partition` ON SCHEDULE EVERY 1 DAY STARTS '$dateTomorrow 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO
- BEGIN
- DECLARE new_partition CHAR(32) DEFAULT
- CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'));
- DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +1;
-
- SET \@s =
- CONCAT('ALTER TABLE $dbtable ADD PARTITION (PARTITION ', new_partition,
- ' VALUES LESS THAN (', max_day, '))');
- PREPARE stmt FROM \@s;
- EXECUTE stmt;
- DEALLOCATE PREPARE stmt;
+ CREATE EVENT logs_add_partition ON SCHEDULE EVERY 1 DAY STARTS '$dateTomorrow 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_add_part_proc();
+ };
+ my $sth = $dbh->prepare("
+ $event
+ ") or die "Could not create partition events: $DBI::errstr";
+ $sth->execute;
+
+ my $event = qq{
+ CREATE EVENT logs_add_archive ON SCHEDULE EVERY 1 DAY STARTS '$dateTomorrow 00:10:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_add_archive_proc();
+ };
+ my $sth = $dbh->prepare("
+ $event
+ ") or die "Could not create partition events: $DBI::errstr";
+ $sth->execute;
+
+ my $event = qq{
+ CREATE EVENT logs_del_partition ON SCHEDULE EVERY 1 DAY STARTS '$dateTomorrow 00:15:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_delete_part_proc();
+ };
+ my $sth = $dbh->prepare("
+ $event
+ ") or die "Could not create partition events: $DBI::errstr";
+ $sth->execute;
+
+ my $event = qq{
+ CREATE PROCEDURE logs_add_archive_proc()
+ SQL SECURITY DEFINER
+ COMMENT 'Creates archive for messages older than $retention days'
+ BEGIN
+ INSERT INTO `logs_archive` SELECT * FROM `$dbtable`
+ WHERE `$dbtable`.`lo` < DATE_SUB(CURDATE(), INTERVAL $retention DAY);
END
};
my $sth = $dbh->prepare("
@@ -332,22 +359,45 @@
$sth->execute;
my $event = qq{
- CREATE EVENT `logs_del_partition` ON SCHEDULE EVERY 1 DAY STARTS '$dateTomorrow 00:00:02' ON COMPLETION NOT PRESERVE ENABLE DO
- BEGIN
- DECLARE old_partitions CHAR(64) DEFAULT '';
- SELECT CONCAT( 'ALTER TABLE $dbtable DROP PARTITION ',
- GROUP_CONCAT( PARTITION_NAME ))
+ CREATE PROCEDURE logs_add_part_proc()
+ SQL SECURITY DEFINER
+ COMMENT 'Creates partitions for tomorrow'
+ BEGIN
+ DECLARE new_partition CHAR(32) DEFAULT
+ CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'));
+ DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +1;
+ SET \@s =
+ CONCAT('ALTER TABLE `logs` ADD PARTITION (PARTITION ', new_partition,
+ ' VALUES LESS THAN (', max_day, '))');
+ PREPARE stmt FROM \@s;
+ EXECUTE stmt;
+ DEALLOCATE PREPARE stmt;
+ END
+ };
+ my $sth = $dbh->prepare("
+ $event
+ ") or die "Could not create partition events: $DBI::errstr";
+ $sth->execute;
+
+ my $event = qq{
+ CREATE PROCEDURE logs_delete_part_proc()
+ SQL SECURITY DEFINER
+ COMMENT 'Deletes partitions older than $retention days'
+ BEGIN
+ SELECT CONCAT( 'ALTER TABLE `$dbtable` DROP PARTITION ',
+ GROUP_CONCAT(`partition_name`))
INTO \@s
- FROM information_schema.PARTITIONS
- WHERE TABLE_SCHEMA='$dbname' AND
- TABLE_NAME='$dbtable' AND
- CREATE_TIME < DATE_SUB( CURDATE(), INTERVAL $retention DAY )
+ FROM `information_schema`.`partitions`
+ WHERE `table_schema` = '$dbname'
+ AND `table_name` = '$dbtable'
+ AND `partition_description` <
+ TO_DAYS(DATE_SUB(CURDATE(), INTERVAL $retention DAY))
GROUP BY TABLE_NAME;
PREPARE stmt FROM \@s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
- END
+ END
};
my $sth = $dbh->prepare("
$event
diff -Nur -I 'Last*' -x '*config.php' -x '*sphinx*' -x license.txt -x '*system_configs*' logzilla/scripts/sql/logs_archive.sql logzilla.3.0.79/scripts/sql/logs_archive.sql
--- logzilla/scripts/sql/logs_archive.sql 1969-12-31 19:00:00.000000000 -0500
+++ logzilla.3.0.79/scripts/sql/logs_archive.sql 2010-06-17 16:00:03.000000000 -0400
@@ -0,0 +1,50 @@
+-- MySQL dump 10.13 Distrib 5.1.31, for debian-linux-gnu (x86_64)
+--
+-- Host: localhost Database: syslog
+-- ------------------------------------------------------
+-- Server version 5.1.31-1ubuntu2-log
+
+/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
+/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
+/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
+/*!40101 SET NAMES utf8 */;
+/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
+/*!40103 SET TIME_ZONE='+00:00' */;
+/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
+/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
+/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
+/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
+
+--
+-- Table structure for table `logs_archive`
+--
+
+DROP TABLE IF EXISTS `logs_archive`;
+SET @saved_cs_client = @@character_set_client;
+SET character_set_client = utf8;
+CREATE TABLE `logs_archive` (
+ `id` bigint(20) unsigned NOT NULL,
+ `host` varchar(128) NOT NULL,
+ `facility` enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','100','101','102','103') NOT NULL,
+ `severity` enum('0','1','2','3','4','5','6','7') NOT NULL,
+ `program` int(10) unsigned NOT NULL,
+ `msg` varchar(2048) NOT NULL,
+ `mne` int(10) unsigned NOT NULL,
+ `suppress` datetime NOT NULL DEFAULT '2010-03-01 00:00:00',
+ `counter` int(11) NOT NULL DEFAULT '1',
+ `fo` datetime NOT NULL,
+ `lo` datetime NOT NULL,
+ `notes` varchar(255) NOT NULL
+) ENGINE=ARCHIVE DEFAULT CHARSET=latin1;
+SET character_set_client = @saved_cs_client;
+/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
+
+/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
+/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
+/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
+/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
+/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
+/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
+/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
+
+-- Dump completed on 2010-06-16 15:59:27
Upgrading Your Database
Upgrading the Database Tables
To upgrade your database tables, do the following from inside your mysql syslog database:
Note that I have added an archive table to house all old data.
This is new as of v3.0.77 and, for now, there's no web interface to search it, but at least you can keep long term log archives this way
Note that some assumptions are made below. For example, that you are using the database name of 'syslog' and a table name of 'logs'
ALTER table logs add index fo (fo);
ALTER table logs add index lo (lo);
DROP TABLE IF EXISTS `logs_archive`;
SET @saved_cs_client = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `logs_archive` (
`id` bigint(20) unsigned NOT NULL,
`host` varchar(128) NOT NULL,
`facility` enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','100
','101','102','103') NOT NULL,
`severity` enum('0','1','2','3','4','5','6','7') NOT NULL,
`program` int(10) unsigned NOT NULL,
`msg` varchar(2048) NOT NULL,
`mne` int(10) unsigned NOT NULL,
`suppress` datetime NOT NULL DEFAULT '2010-03-01 00:00:00',
`counter` int(11) NOT NULL DEFAULT '1',
`fo` datetime NOT NULL,
`lo` datetime NOT NULL,
`notes` varchar(255) NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1;
Upgrading the Events/Partitioning Schema
Next, we need to change the event and partitioning schema.
In the code below, you will need to replace anything with a $variable with your settings/dates, etc. Variables to change below are:
1. $TOMORROW - change to tomorrow's day (e.g. 17)
2. $DAYSBACK - change to the number of days to keep "live" searchable data (default is 30)
note: make this number as low as possible, the longer you keep "live" data, the longer it will take to search it. If you aren't planning to search 90 day old data every day, then why burden your server with it? Let the auto-archiver move it to the logs_archive table where you can manually search for data that old when needed.
3. $DBNAME - change to your db name (default is syslog)
4. $DBTABLE - change to your table name (default is logs)
You may also want to adjust the event timers if you are receiving a lot of data - they are set below to fire and midnight, 12:10 and 12:25am
Make sure you execute this code in the syslog db itself (mysql>use syslog) - assuming the logzilla db is called "syslog"
DROP EVENT IF EXISTS logs_add_partition;
CREATE EVENT logs_add_partition ON SCHEDULE EVERY 1 DAY STARTS '2010-06-$TOMORROW 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_add_part_proc();
DROP EVENT IF EXISTS logs_add_archive;
CREATE EVENT logs_add_archive ON SCHEDULE EVERY 1 DAY STARTS '2010-06-$TOMORROW 00:10:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_add_archive_proc();
DROP EVENT IF EXISTS logs_del_partition;
CREATE EVENT logs_del_partition ON SCHEDULE EVERY 1 DAY STARTS '2010-06-$TOMORROW 00:25:00' ON COMPLETION NOT PRESERVE ENABLE DO CALL logs_delete_part_proc();
DELIMITER //
CREATE PROCEDURE logs_delete_part_proc()
SQL SECURITY DEFINER
COMMENT 'Deletes partitions older than $DAYSBACK days'
BEGIN
SELECT CONCAT( 'ALTER TABLE `$DBTABLE` DROP PARTITION ',
GROUP_CONCAT(`partition_name`))
INTO @s
FROM `information_schema`.`partitions`
WHERE `table_schema` = '$DBNAME'
AND `table_name` = '$DBTABLE'
AND `partition_description` <
TO_DAYS(DATE_SUB(CURDATE(), INTERVAL $DAYSBACK DAY))
GROUP BY TABLE_NAME;
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE logs_add_archive_proc()
SQL SECURITY DEFINER
COMMENT 'Creates archive for messages older than $DAYSBACK days'
BEGIN
INSERT INTO `logs_archive` SELECT * FROM `$DBTABLE`
WHERE `$DBTABLE`.`lo` < DATE_SUB(CURDATE(), INTERVAL $DAYSBACK DAY);
END //
DELIMITER ;
DELIMITER //
CREATE PROCEDURE logs_add_part_proc()
SQL SECURITY DEFINER
COMMENT 'Creates partitions for tomorrow'
BEGIN
DECLARE new_partition CHAR(32) DEFAULT
CONCAT ('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'));
DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +1;
SET @s =
CONCAT('ALTER TABLE `$DBTABLE` ADD PARTITION (PARTITION ', new_partition,
' VALUES LESS THAN (', max_day, '))');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;
DROP TABLE IF EXISTS `logs_archive`;
CREATE TABLE `logs_archive` (
`id` bigint(20) unsigned NOT NULL,
`host` varchar(128) NOT NULL,
`facility` enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','100
','101','102','103') NOT NULL,
`severity` enum('0','1','2','3','4','5','6','7') NOT NULL,
`program` int(10) unsigned NOT NULL,
`msg` varchar(2048) NOT NULL,
`mne` int(10) unsigned NOT NULL,
`suppress` datetime NOT NULL DEFAULT '2010-03-01 00:00:00',
`counter` int(11) NOT NULL DEFAULT '1',
`fo` datetime NOT NULL,
`lo` datetime NOT NULL,
`notes` varchar(255) NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1;
Upgrading from 3.0.79 to 3.0.84
To upgrade from 3.0.79 to 3.0.84, we will need to backup your current MySQL database, then re-import it after the upgrade. This can be accomplished by completing the following six steps. First, lets change your working directory to a safe location that won't be affected when it comes time to remove your current Logzilla directory.
cd ~
Now that we're in a safe location, let's begin the upgrade process.
Back up your Database
1. Dump your Current MySQL Database to file
mysqldump -p syslog > lz.sql
2. Delete your now backed up database. PLEASE make sure you have executed the above command prior to completing this step.
mysqladmin -p drop syslog
3. Now we will re-import your old data to a temporary storage location. For the purposes of this document, we will call this database lz.
mysqladmin -p create lz mysql -p lz < lz.sql
Install Logzilla 3.0.84
4. Now that you have the temporary holding database set up, delete your current installation of Logzilla, and download and install the new version as normal.
cd /var/www mv /var/www/logzilla /var/www/logzilla.backup wget http://php-syslog-ng.googlecode.com/files/logzilla_3.0.84.tgz tar xzvf logzilla_3.0.84.tgz cd /var/www/logzilla/scripts ./install.pl
Restore Your Log Data
5. Now we will transfer your existing data into the new 3.0.84 database. Copy the following, and save it as "upgrade.sql"
truncate banned_ips; insert into banned_ips (select * from lz.banned_ips); truncate cache; insert into cache (select * from lz.cache); truncate facilities; insert into facilities (select * from lz.facilities); truncate groups; insert into groups (select * from lz.groups); truncate help; insert into help (select * from lz.help); truncate history; insert into history (select * from lz.history); truncate hosts; insert into hosts (select * from lz.hosts); truncate logs; insert into logs (select * from lz.logs); truncate logs_archive; insert into logs_archive (select * from lz.logs_archive); truncate lzecs; insert into lzecs (select * from lz.lzecs); truncate mne; insert into mne (select * from lz.mne); truncate programs; insert into programs (select * from lz.programs); truncate settings; insert into settings (select * from lz.settings); truncate severities; insert into severities (select * from lz.severities); truncate sph_counter; insert into sph_counter (select * from lz.sph_counter); truncate suppress; insert into suppress (select * from lz.suppress); truncate totd; insert into totd (select * from lz.totd); truncate ui_layout; insert into ui_layout (select * from lz.ui_layout); truncate users; insert into users (select * from lz.users);
Now execute the transition.
mysql -p syslog < upgrade.sql
Validate everything works
6. Login to Logzilla, and validate that the data has been successfully moved. Run several searches, make sure to check that your users are still listed, and any custom settings are intact. If after extensive testing, everything is functioning properly, drop your temporary database, and remove the backup directory.
mysqladmin -p drop lz rm -rf /var/www/logzilla.backup
You're done! Enjoy the new features of 3.0.84 :)
