Installing PERL Script(Linux/Win) for Snort-MySQL Database Cleanup.

MySQL-Snort-Cleanup.pl is very simple and useful perl script with which we can manage cleanup activity for IDS Database SNORT, This will delete the data from all the respective tables, we have to provide the command line arguments to this script i.e. StartDate and EndDate

For windows OS we need to install the Active Perl, and mysql db drivers, Once it is done we can run this MySQL-Snort-Cleanup.pl for cleaning database with specific time frames. Database Name,UserName, Passwords to be supplied to the MySQL-Snort-Cleanup.pl can be modified by any text editor.

Script Usage for Win/Linux:-
perl MySQL-Snort-Cleanup.pl "Start Time" "End Time"
e.g. perl MySQL-Snort-Cleanup.pl "2010-12-18 15:00:00" "2010-12-18 16:07:00"

Active Perl installation is not installing the DB drivers for MySQL DB by default hence same must be installed using Perl Package Manager(PPM) It is defined below.

1).Active Perl Installation with the default settings for windows. (for linux it is installed by default
2).MySQL driver can be installed on windows using PPM as shown below.
  
ppm install DBD-mysql

Perl Script MySQL-Snort-Cleanup.pl

MySQL Database Exporting/Importing

we can use Perl scripts to automate the MySQL database in connection with the mysqldump utility specially for large databases.
mysqldump -u UserName -pPassword DBName | gzip > DBName.sql.gz
Once the DatabaseName.sql.gz is completed the same file can be imported to the new created database but gz file needs to be decompressed first it can be done as shown below.
gzip -d DatabaseName.sql.gz


Database Restoring Steps.
Create new Database
Connect to the newly created database
Import the decompressed/extracted file DatabaseName.sql using below option
Create database NewDBName
Connect NewDBName
source D:\DatabaseName.sql


This will import the entire sql dump file to this new database.

Perl scripts for checking Database Connetions

print("Script Started ...\n");
use strict;
use DBI;
my $ds = "dbi:mysql:snort";
my $db_user = "idsuser";
my $db_pass = "idspasswd1";
my $db = DBI->connect($ds, $db_user, $db_pass) or die $DBI::errstr;
print("\n Script Execution Completed ..\n");


Click to Download

IBM DB2 Database Transaction Logs full

This can be solved by increasing the Database Transaction Logs size.

Log file size (4KB)                        (LOGFILSIZ) = 1000
Number of primary log files                (LOGPRIMARY) = 3
Number of secondary log files              (LOGSECOND) = 2

db2 update db cfg for databaseName using LOGFILSIZ 5000
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
DB21026I  For most configuration parameters, all applications must disconnect
from this database before the changes become effective.

Log file size (4KB)                        (LOGFILSIZ) = 5000
Number of primary log files                (LOGPRIMARY) = 3
Number of secondary log files              (LOGSECOND) = 2

Perl scripts for cleaning snort database

#!/usr/bin/perl -w
#----------------------------------------
# name: snort_db_cleanup.pl
# description: script to cleanup snort/acid db (only tested w/mysql)
# goal: allows you to schedule db cleanup without using php frontend
# usage: snort_db_cleanup.pl "StartDate" "EndDate"
#----------------------------------------
print("Script Started ...\n");
use strict;
use DBI;

my $ds = "dbi:mysql:snort";
my $db_user = "idsuser";
my $db_pass = "idspasswd";
my $db = DBI->connect($ds, $db_user, $db_pass) or die $DBI::errstr;


my ($cid,$sid,$sql,$time_select,$exec_time_select);
my
($event,$iphdr,$tcphdr,$udphdr,$icmphdr,$opt,$data,$acid_ag_alert,$acid_event);
my
($exec_event,$exec_iphdr,$exec_tcphdr,$exec_udphdr,$exec_icmphdr,$exec_opt,$exec_data,$exec_acid_ag_alert,$exec_acid_event);
my %timeframe;

$timeframe{start} = $ARGV[0];
$timeframe{finish} = $ARGV[1];
chomp $timeframe{start};
chomp $timeframe{finish};

$time_select = "select sid,cid from event where timestamp >= '$timeframe{start}' and timestamp <= '$timeframe{finish}'";
$exec_time_select = $db->prepare($time_select);

$exec_time_select->execute();
$exec_time_select->bind_columns(undef,\$sid,\$cid);

while ($exec_time_select->fetch) {

 $event = "delete from event where sid='$sid' and cid='$cid'";
 $iphdr = "delete from iphdr where sid='$sid' and cid='$cid'";
 $tcphdr = "delete from tcphdr where sid='$sid' and cid='$cid'";
 $udphdr = "delete from udphdr where sid='$sid' and cid='$cid'";
 $icmphdr = "delete from icmphdr where sid='$sid' and cid='$cid'";
 $opt = "delete from opt where sid='$sid' and cid='$cid'";
 $data = "delete from data where sid='$sid' and cid='$cid'";
 $acid_ag_alert = "delete from acid_ag_alert where ag_sid='$sid' and ag_cid='$cid'";
 $acid_event = "delete from acid_event where sid='$sid' and cid='$cid'";

 $exec_event = $db->prepare($event);
 $exec_iphdr = $db->prepare($iphdr);
 $exec_tcphdr = $db->prepare($tcphdr);
 $exec_udphdr = $db->prepare($udphdr);
 $exec_icmphdr = $db->prepare($icmphdr);
 $exec_opt = $db->prepare($opt);
 $exec_data = $db->prepare($data);
 $exec_acid_ag_alert = $db->prepare($acid_ag_alert);
 $exec_acid_event = $db->prepare($acid_event);

 $exec_event->execute();
 $exec_iphdr->execute();
 $exec_tcphdr->execute();
 $exec_udphdr->execute();
 $exec_icmphdr->execute();
 $exec_opt->execute();
 $exec_data->execute();
 $exec_acid_ag_alert->execute();
 $exec_acid_event->execute();

 $exec_event->finish();
 $exec_iphdr->finish();
 $exec_tcphdr->finish();
 $exec_udphdr->finish();
 $exec_icmphdr->finish();
 $exec_opt->finish();
 $exec_data->finish();
 $exec_acid_ag_alert->finish();
}

$exec_time_select->finish;

print("\n Script Execution Completed ..\n");

Click Here to Download

Perl scripts to automate MySQL Backup

it requires simple modifications like Folder Path Details / user & database details and we can schedule this on need basis.

#!/usr/bin/perl
($Second, $Minute, $Hour, $Day, $Month, $Year, $WeekDay, $DayOfYear, $IsDST) = localtime(time) ;

$Year += 1900 ; $Month += 1;

$dt = sprintf("%02d-%02d-%04d-%02d-%02d-%02d",$Day , $Month, $Year, $Hour, $Minute, $Second,) ;

exec "mysqldump -u UserName -pPassword DatabaseName | gzip > FilePath\\$dt.gz";

Click to Download

WebSphere 6.0 - DataSource java.sql.SQLException: java.lang.UnsupportedClassVersion

While creating MySQL JDBC Data source for WebSphere 6.0 the database connection is failing with the error of DataSource java.sql.SQLException: java.lang.UnsupportedClassVersion. Unsupported major.minor version 49.0

This is b'cos datasource is created with the mysql-connector-java-5.1.14 instead of this try the lower version connector for establishing connection with MySQL database and it is mysql-connector-java-3.1.6-bin.jar
after creating the datasource websphere application server must be restarted to load the newly loaded connecters.

Default Server Not getting install with WebSphere 4.0.4

With the default installation of WebSphere Application Server 4.0.1 there is a table which is not being created due to some Constraints of Database and this is the reason that the Default Server is not getting installed for the default server Node. Therefor create this table manually and then Restart the Server from services.

CREATE TABLE INC (
PRIMARYKEY  VARCHAR2 (64)  NOT NULL,
THEVALUE    INTEGER,
PRIMARY KEY ( PRIMARYKEY ) )

Error While starting gsk7 of WAS 5.1 Start Key Management Utility

Error While starting gsk7 of WAS 5.1 Start Key Management Utility

Starting the IBM Key Management application an error occurs
stating the following:
"The Java Cryptographic Extension(JCE) files were not found.
Please check that the JCE files have been installed in the correct
directory"/

Web Solution
The Global Security Kit environment needs to be prepared to
work properly.
1. Open up a Command Prompt window
2. cd %JAVA_HOME%\lib\ext
3. copy ..\..\..\..\GSK7\classes\jre\lib\ext\*.jar
4. del ibmjcaprovider.jar
5. cd ..\security
6. copy ..\..\..\..\GSK7\classes\gsk_java.security java.security
7. Select 'YES' to overwrite the files
8. Start the gsk7ikm application.

you can try the below one also.
From GSK 7 folder structure copy this
D:\Program Files\ibm\gsk7\classes\jre\lib\ext\ibmpkcs.jar
D:\Program Files\ibm\gsk7\classes\jre\lib\ext\ibmjceprovider.jar
to JavaHome D:\j2sdk1.4.2_03\jre\lib\ext
Register its entry into the java.security OF javaHome by adding these below code to
your java.security file.
security.provider.6=com.ibm.spi.IBMCMSProvider
security.provider.7=com.ibm.crypto.provider.IBMJCE
policy.url.2=file:${java.home}/lib/security/java.pol