How to use MRTG to generate web traffic for a router

Objective: use MRTG to generate web traffic for a router
Environment:  CentOS 4 and cisco 2501 router


Steps:

1. Enable SNMP server for cisco router and set the bandwidth for the serial interface
ssh/telnlet into the router with pasword
en>
conf t
snmp-server community public ro

interface serial 0/0
#bandwidth 2048
wr

2. Make sure the udp 161 port is not blocked on cisco router


3. Install MRTG (either through rpm or manual install)

# yum install mrtg

4. Go to MRTG website documention - unix guide at 
http://oss.oetiker.ch/mrtg/doc/mrtg-unix-guide.en.html

use the first example command to generate mrtg.cfg

 cfgmaker --global "WorkDir: /home/tobi"           \
          --global "Options[_]: growright,bits"    \
          --ifref=ip                               \
          public@router.place.xyz > mrtg.cfg

note: replace workdir, remove --ifref=ip line.
You might use this command:
/usr/bin/cfgmaker --global 'WorkDir: /var/www/html/mrtg' --global 'Options[_]: bits,growright' --output /etc/mrtg/mrtg.cfg public@10.0.0.1

5. setup cronjob (if you install MRTG through rpm , no need, because there's a file under /etc/cron.d/mrtg already, run rpm -qil mrtg to check which are the files installed by rpm)





6. Access the serial port MRTG graphic

http://10.0.0.1/mrtg/10.0.0.1_2.html

note: DO NOT configure cronjob for mrtg again if you install mrtg through yum install rpm command, otherwise, it will conflict, you will always receive warning email saying 'cannot rename ....' or ' cannot find primary log file...' etc

How to use db2move and db2look to duplicate IBM DB2 database

Objective: use db2 control center to create another testing database and tablespace, then use db2look and db2move to export then import the production database data into this testing database.
Environment: RHEL5, db2 Informational tokens are "DB2 v9.1.0.3", "s070719", "MI00202", and Fix Pack "3". Database name is DB1, the os and schema user name is user1

Steps:

1. Install same version of IBM db2 database on testing server

note: Setup database runtime client side parameters, such as db2 node directory and db directory

db2 ? catalog node
db2 ? catalog db

db2 catalog tcpip node db1 remote 10.0.0.10 server 50000
db2 catalog db db1 at node db1

db2 list node directory
db2 list db directory

2. Create a new database called DB1 also then create tablespace tb_user1 and OS user user1, please refer to the article http://linuxtechres.blogspot.com/2010/01/how-to-setup-new-schema-in-db2-database.html

3. Use db2look to generate DDL statements for later use
db2look -d db1 -e -z user1 -o user1.sql

Note: for db2look, you might need to separate the file into 2 files called db2look-1.sql and db2look-2.sql. db2look-2.sql contains those ‘adding constrain’ statements.
Also take note the schema name and tablespace name might need to be changed also.

4. Preparing table and view granting script

cat db2look.sql | grep 'CREATE TABLE' | awk -F\" '{print $4}' > tablelist
cat db2look.sql | grep 'CREATE VIEW' | awk '{print $3}' | sed -e 's#(.*##g' > viewlist

after that, generate the 2 files which content such as this:
grant select,insert,update,delete,alter,index,references on table STUDENT to user1 ;
and this:
grant select,insert,update,delete on to user1 ;

5. use db2move to export the schema
Run the following db2move command on production database server:

db2move db1 export -sn user1

6. Run the first part of db2look-1.sql to create tables and views first
7. Use command db2move to import/load the content:

db2move db1 import

note:
1. You can modify db2move.lst file to exclude certain unnecessary big tables.
2. Actually db2move is a wrapper of db2 “import xxxx” or db2 “load xxxx”, you just don’t have to type so many times for importing/loading each table, use db2move instead.

When you use db2move to import db2 version 9 Linux db2move dump to solaris db2 version 8, you might encounter codepage error like this:

Code page option is incompatible with the lobsinfile option

This is because you use db2move ‘import’ option, if you use db2move ‘load’ option, then the problem will be solved.

Search: db2move load import lobsinfile incompatible

Problem encountered during using db2move load to load tables

Search: db2move load –lo replace ,insert

Due to tablespace has no free pages left. During db2move load operation period, it failed with the following errors:

You can use the following command to verify that:
# db2 “list tablespaces show detail”

LOAD: table " ".""
*** ERROR -289. Check message file tab365.msg!
*** SQLCODE: -289 - SQLSTATE: 57011
*** SQL0289N Unable to allocate new pages in table space "". SQLSTATE=57011

Note: this error might also happen during db2look period also:


After failed to load, then you cannot load again, you have to clear it using the following command:
Otherwise, the error message for accessing those fail-loaded tables are :

SQL0668N operation not allowed for reason code “3” on table “xxxx.yyyy”. SQLSTATE=57016

# db2 “load from /dev/null of ixf replace into .tablename [nonrecoverable]”

Regarding how to recover from a failed LOAD operation in DB2, you can refer to article

Recovering from a failed LOAD operation in DB2 for Linux, UNIX and Windows
At http://www.ibm.com/developerworks/data/library/techarticle/0202kline.html

8. After importing data, import the second db2look-2.sql script to enable constrain

9. Test. You can test the connection from client to server from runtime client application server.
db2 connect to db1 user user1

How to setup a new schema in a DB2 database

Objective: Create a new client schema on the existing DB2 database
Environment: RHEL5 and DB2 V9


Steps:

1. create operating system user
[root@db1 ~]# useradd -c 'DB2 account for jephe' -m jephe
[root@db1 ~]# passwd jephe
Changing password for user jephe
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
[root@db1 ~]# chage jephe
Changing the aging information for jephe
Enter the new value, or press ENTER for the default

        Minimum Password Age [7]:
        Maximum Password Age [90]: 99999
        Last Password Change (YYYY-MM-DD) [2010-01-11]:
        Password Expiration Warning [7]:
        Password Inactive [-1]:
        Account Expiration Date (YYYY-MM-DD) [1969-12-31]:

2. create tablespace directory
su - db2inst1
cd /db2/db2inst1/db/DB1 (assuming db2 database directory is /db2/db2inst1/db)
mkdir tb_jephe

3. create database and tablespace using db2 control center

Create database by using control center, you'd better to choose 16k tablespace page size instead of 4k, and choose UTF-8 as codeset

CREATE DATABASE JEPHE AUTOMATIC STORAGE NO  ON '/home/db2inst1' USING CODESET UTF-8 TERRITORY US COLLATE USING SYSTEM PAGESIZE 16384;


CONNECT TO DB1;
CREATE  REGULAR  TABLESPACE TB_JEPHE PAGESIZE 16 K  MANAGED BY SYSTEM  USING ('/db2/db2inst1/db/DB1/tb_jephe' ) EXTENTSIZE 16 OVERHEAD 10.67 PREFETCHSIZE 16 TRANSFERRATE 0.04 BUFFERPOOL  IBMDEFAULTBP  DROPPED TABLE RECOVERY ON;
CONNECT RESET;

4. add user jephe to tablespace tb_jephe
CONNECT TO DB1;
GRANT  CREATETAB,CONNECT,IMPLICIT_SCHEMA ON DATABASE  TO USER JEPHE;
GRANT USE OF TABLESPACE TB_JEPHE TO USER JEPHE;
CONNECT RESET;

5. use db2look to duplicate schema from the existing ones

 db2look -d db1 -e -z existingschemaname -o existingschema.sql

then use vi to batch change the existingschemaname to jephe

6. use db2 control center to assign table and view privileges for the new user

# TABLES
CONNECT TO DB1;
GRANT  SELECT,INSERT,UPDATE,DELETE,ALTER,INDEX,REFERENCES ON TABLE JEPHE.USERNAME TO USER JEPHE;
...
CONNECT RESET

#VIEWS
CONNECT TO DB1;
GRANT  SELECT,INSERT,UPDATE,DELETE ON VIEW JEPHE.USERNAME TO USER JEPHE;
...
CONNECT RESET

7. login as new client jephe and test
db2 connect to db1 user jephe
db2 "select * from certaintablename"

How to use rsyslog as centralized log server for Linux and Windows clients

Jephe Wu - http://linuxtechres.blogspot.com

Objective: Use TLS-encrypted TCP connection for Linux client and normal UDP port 514 for Windows client to send all syslog to centralized rsyslog RHEL5 server

Environment: Windows 2003 server with Snare for Windows(freware) installed. CentOS5 servers for Linux rsyslog server and clients.



Steps:

1. CentOS 5 rsyslog rpm is quite old and without SSL support, download rsyslog latest stable version and compile (version 5.4.0), don't use 5.2.0 which is quite buggy.

rsyslog compliation needs mysql and gnutls devel packages
yum install gnutls*
yum install mysql*

then
compile rsyslog:
./configure --enable-mysql --enable-gnutls --enable-mail 
make
make install

Note:
1) when compiling rsyslog 5.4.0 stable version on RHEL5.1, I encountered this issue:
runtime/glbl.c:134: undefined reference to `ATOMIC_STORE_1_TO_INT'

then it is solved this way:

a) In the "configure" file, I simply added the "-march=i686" option in bold

...
elif test $ac_cv_prog_cc_g = yes; then
if test "$GCC" = yes; then
CFLAGS="-g -O2 -march=i686"
else
CFLAGS="-g"
fi
else
...

b) I executed ./configure again, then ran make clean and make, and I passed this error.

2. Configure rsyslogd on the CentOS5 server
2.1) configure TLS encryption
Refer to http://www.rsyslog.com/doc-rsyslog_tls.html  and http://www.rsyslog.com/doc/rsyslog_secure_tls.html to generate CA and individual peer certificates, put them under /etc/rsyslog/ (create folder first)

2.2) /etc/rsyslog.conf
# chkconfig syslog off
# service syslog stop
put /usr/local/sbin/rsyslogd into /etc/rc.local

then configure /etc/rsylog.conf as follows:
==============
# for Snare
$EscapeControlCharactersOnReceive off

#load modules
$ModLoad immark # provides --MARK-- message capability
$ModLoad imudp # provides UDP syslog reception
$ModLoad imtcp # provides TCP syslog reception
$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog # provides kernel logging support (previously done by rklogd)
$ModLoad ommail # direct email sending

# discard some messages
:msg, regex, ".*Failure Audit.*Account Logon.*Logon attempt by: MICROSOFT_AUTHEN
TICATION_PACKAGE_V1.*eporeplro" ~

# email sending
$ModLoad ommail
$ActionMailSMTPServer mailrelay.domain.com
$ActionMailFrom rsyslog@domain.com
$ActionMailTo jephe.wu@domain.com
$template mailSubject,"Rsyslog Alert for %hostname%"
$template mailBody,"%msg%"
$ActionMailSubject mailSubject
$ActionExecOnlyOnceEveryInterval 60
:msg, regex, ".*Audit.*Account Management.*"    :ommail:;mailBody

$ActionMailSMTPServer mailrelay.domain.com
$ActionMailFrom rsyslog@domain.com
$ActionMailTo jephe.wu@domain.com
$template mailSubject,"Rsyslog Alert for %hostname%"
$template mailBody,"%msg%"
$ActionMailSubject mailSubject
$ActionExecOnlyOnceEveryInterval 60
:msg, regex, ".*Failure Audit.*Account Logon.*Logon attempt by: MICROSOFT_AUTHEN
TICATION_PACKAGE_V1.*"  :ommail:;mailBody

# note: fore each :msg, regex, command, you have to use a complete set of mail setting first.

# make gtls driver the default
$DefaultNetstreamDriver gtls

# certificate files
$DefaultNetstreamDriverCAFile /etc/rsyslog/ca.pem
# note: this ca.pem should be distributed to all clients for tls connection, ca.pem is only needed for rsyslog tcp client, not the other cert.pem and key.pem files.
$DefaultNetstreamDriverCertFile /etc/rsyslog/cert.pem
$DefaultNetstreamDriverKeyFile /etc/rsyslog/key.pem

$InputTCPServerStreamDriverMode 1 # run driver in TLS-only mode
$InputTCPServerStreamDriverAuthMode anon # client is NOT authenticated
$InputTCPServerRun 10514 # start up listener at port 10514
$UDPServerRun 514
$AllowedSender UDP, 10.0.0.1,10.0.0.2 # permit only Snare from Windows server, so normal Linux syslog client which runs at udp port 514 cannot connect to it insecurely.

# create directories automatically and give permission
$CreateDirs on
$DirCreateMode 0755

# templates
$template DailyPerHostLogs,"/var/log/rsyslog/%$YEAR%/%$MONTH%/%$DAY%/%HOSTNAME%.log"
*.* -?DailyPerHostLogs

# Other usages examples
# $template 1,"/var/log/rsyslog/%$YEAR%/%$MONTH%/%$DAY%/%HOSTNAME%-maillog.log"
# if $source == 'app2' and $syslogfacility-text == 'mail' then -?1

--------
note:
a. optionally, you can also use allowedsender tcp such as this

$AllowedSender TCP, 127.0.0.1, 10.0.0.0/24 
to only allow certain or some hosts to connect to rsyslog server through tcp protocol.

Refer to rsyslog.conf configuration directive for $allowedsender at 
http://www.rsyslog.com/doc/rsconf1_allowedsender.html


b. If a host is disallowed to connect to rsylog server, on the rsyslog server itself, the log will be showing as follows:

TCP message from disallowed sender jephe.domain.com discarded
====================

3. configure rsyslog on Linux as syslog client (Sequence is important for remote message logging when rsyslog server is shut down)
note: you  can refer to http://www.rsyslog.com/doc/rsyslog_secure_tls.html to configure the mutual authentication for real secure.

3.1) stop syslog
# chkconfig syslog off
# service syslog stop
put /usr/local/sbin/rsyslogd into /etc/rc.local

3.2) /etc/rsyslog.conf
# certificate files - just CA for a client
$DefaultNetstreamDriverCAFile /etc/rsyslog/ca.pem

# set up the action  (put SSL first before *.* message forwarding)
$DefaultNetstreamDriver gtls # use gtls netstream driver
$ActionSendStreamDriverMode 1 # require TLS for the connection
$ActionSendStreamDriverAuthMode anon # server is NOT authenticated

# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
# check website: http://www.rsyslog.com/doc-rsyslog_reliable_forwarding.html 
# this reliable log forwarding function is different with RELP protocol which is more reliable

$CreateDirs on
$DirCreateMode 0755

$ModLoad imuxsock             # local message reception
$WorkDirectory /var/log/rsyslog # default location for work (spool) files (create this folder first)
$ActionQueueType LinkedList   # run asynchronously
$ActionQueueFileName rsyslog  # set file name, also enables disk mode
$ActionQueueMaxDiskSpace 1g   # 1gb space limit (use as much as possible)
$ActionResumeRetryCount -1    # infinite retries if host is down
$ActionQueueSaveOnShutdown on # save in-memory data if rsyslog shuts down
*.*              @@log1.jephe.com:10514 # send (all) messages

#note:
#The rsyslog queueing subsystem tries to buffer to memory. So even if the remote server goes #offline, no disk file is generated. File on disk are created only if there is need to, for example if rsyslog #runs out of (configured) memory queue space or needs to shutdown (and thus persist yet unsent messages). #Using main memory and going to the disk when needed is a huge performance benefit. You do not need to #care about it, because, all of it is handled automatically and transparently by rsyslog.

# Logging much else clutters up the screen.
#kern.*                                                 /dev/console
# Log anything (except mail) of level info or higher.
# Don't log private authentication messages!
*.info;mail.none;authpriv.none;cron.none                /var/log/messages
# The authpriv file has restricted access.
authpriv.*                                              /var/log/secure
# Log all the mail messages in one place.
mail.*                                                  -/var/log/maillog
# Log cron stuff
cron.*                                                  /var/log/cron
# Everybody gets emergency messages
*.emerg                                                 *
# Save news errors of level crit and higher in a special file.
uucp,news.crit                                          /var/log/spooler
# Save boot messages also to boot.log
local7.*                                                /var/log/boot.log

note:
a. If you are not using yum to install rsyslog, you should also add rsyslog to /etc/logrotate.d/syslog . that file should look similar to this, otherwise, once the /var/log/secure etc files are rotated weekly, the log will not go to that file anymore if someone tried to ssh and failed.

/var/log/messages /var/log/secure /var/log/maillog /var/log/spooler /var/log/boot.log /var/log/cron {
    sharedscripts
    postrotate
        /bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
        /bin/kill -HUP `cat /var/run/rsyslogd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}
Leaving the syslog line in that file won't hurt anything.

 b. you can put the following into /etc/rc.local
/usr/local/sbin/rsyslogd -c4 -f /etc/rsyslog.conf

4) Install Snare for Windows on Windows 2003 servers
use snare for windows with rsyslog : 
$EscapeControlCharactersOnReceive off 
note: see http://wiki.rsyslog.com/index.php/Using_Snare_as_a_client_on_Windows

5 Testing and debug
5.1) run 'logger testing' on Linux command line to test
5.2) check log1 server /var/log/messages or /var/log/rsyslog/YEAR/MONTH/DAY/logfilename to see if there's any error after manually running /usr/local/sbin/rsyslog

6 Other usage examples

6.1)A solution to rotate logs on a daily basis is to use dynamic files and after a while rotate the log files away. Quick sample:

/etc/rsyslog.conf:

#####################################################
# Log everything to a per host daily logfile        #
#####################################################
$template DailyPerHostLogs,"/var/log/rsyslog/%HOSTNAME%/%$YEAR%/%$MONTH%/%$DAY%/%HOSTNAME%.log"
*.* -?DailyPerHostLogs

/etc/cron.hourly/syslog-bzip2:

# Compress *.log-files not changed in more than 24 hours:
find /var/log/syslog/2008 -type f -mtime +1  -name "*.log" -exec bzip2 '{}' \;

6.2) Other examples

$template tplSiteID,"<%PRI%>%TIMESTAMP:::date-rfc3339% %HOSTNAME% %syslogtag:1:32%,siteID,%msg%"
*.* @@centralsrv.example.net;tplSiteID


$template 1,"/var/log/rsyslog/%HOSTNAME%/%$YEAR%/%$MONTH%/%$DAY%/%HOSTNAME%-maillog.log"
if $source == 'hpay1' and $syslogfacility-text == 'mail' then -?1
:msg, regex, ".*Failure Audit.*Account Logon.*Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1.*"  -?1

7.  References:
a. Replace sysklogd with rsyslog as the default in Fedora: - http://fedoraproject.org/wiki/Releases/FeatureRsyslog
b. You might want to take a look at Octopussy: Perl/XML Logs Analyzer at
http://sourceforge.net/projects/syslog-analyzer/files/

c. debug rsyslog -  rsyslogd -c4 -dn   (interactive mode)

d. rsyslogd -v  (to show which are the things compiled in, SSL?)

e. http://www.homershut.net/wiki/rsyslogd%20client/server  for an working example

g. http://www.rsyslog.com/doc/rsconf1_actionexeconlywhenpreviousissuspended.html  for fallback

*.* @@primary-syslog.example.com
$ActionExecOnlyWhenPreviousIsSuspended on
& @@secondary-1-syslog.example.com # & is used to have more than one action for
& @@secondary-2-syslog.example.com # the same selector - the mult-action feature
& /var/log/localbuffer
$ActionExecOnlyWhenPreviousIsSuspended off # to re-set it for the next selector


================
Added by Jephe on 30 Oct 2012
h. example of /etc/rsyslog.conf for rsyslog server:


[root@log02 ~]# cat /etc/rsyslog.conf | grep -v ^# | grep -v ^$
$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
# Turn Off "Last message repeated..."
# If set to on, repeated messages are reduced. If set to off, every message is logged. $RepeatedMsgReduction off
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
$ModLoad imudp
$UDPServerRun 514
$ModLoad imtcp
$InputTCPServerRun 514
$AllowedSender TCP, 127.0.0.1, 10.0.0.0/8
$AllowedSender UDP, 127.0.0.1, 10.0.0.0/8
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$IncludeConfig /etc/rsyslog.d/*.conf
LogRsyslogStatusMessages off
#If set to on (the default), rsyslog emits message on startup and shutdown 
#as well as when it is HUPed. This information might be needed by some log analyzers. If set to off, no such #status messages are logged, what may be useful for other scenarios. [available since 4.7.0 and 5.3.0]
$CreateDirs on
$DirCreateMode 0755
$template DYNmessages,"/var/log/rsyslog/%HOSTNAME%/messages"
$template DYNsecure,"/var/log/rsyslog/%HOSTNAME%/secure"
$template DYNmaillog,"/var/log/rsyslog/%HOSTNAME%/maillog"
$template DYNcron,"/var/log/rsyslog/%HOSTNAME%/cron"
$template DYNspooler,"/var/log/rsyslog/%HOSTNAME%/spooler"
$template DYNboot,"/var/log/rsyslog/%HOSTNAME%/boot.log"

# Log anything (except mail) of level info or higher. # Don't log private authentication messages! # *.info;mail.none;authpriv.none;cron.none /var/log/messages
# info is level 6
if $syslogseverity <= '6' and ( $syslogfacility-text != 'mail' \
         and $syslogfacility-text != 'authpriv' and $syslogfacility-text != 'cron' ) \
                then ?DYNmessages
if $syslogfacility-text == 'authpriv' then ?DYNsecure
if $syslogfacility-text == 'mail' then -?DYNmaillog
if $syslogfacility-text == 'cron' then ?DYNcron
if ( $syslogfacility-text == 'uucp' or \
        $syslogfacility-text == 'news' ) and $syslogseverity-text == 'crit' \
                then ?DYNspooler
if $syslogfacility-text == 'local7' then ?DYNboot

----------

LOG LEVELS ^

There are eigth levels available:
    7   debug
    6   info
    5   notice
    4   warning, warn
    3   error, err
    2   critical, crit
    1   alert
    0   emergency, emerg
debug is the highest and emergency is the lowest level.
Level debug is the highest level because it basically says to log every peep.

default /etc/rsyslog.conf content:

[root@web ~]# cat /etc/rsyslog.conf| grep -v ^# | grep -v ^$
$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$IncludeConfig /etc/rsyslog.d/*.conf
*.info;mail.none;authpriv.none;cron.none                /var/log/messages
authpriv.*                                              /var/log/secure
mail.*                                                  -/var/log/maillog
cron.*                                                  /var/log/cron
*.emerg                                                 *
uucp,news.crit                                          /var/log/spooler
local7.*                                                /var/log/boot.log


----------

[root@log02 logrotate.d]# more syslog 
/var/log/rsyslog/*/cron
/var/log/rsyslog/*/maillog
/var/log/rsyslog/*/messages
/var/log/rsyslog/*/secure
/var/log/rsyslog/*/spooler
{
missingok
# missingok is required if any of above files might not exist such as /var/log/rsyslog/*/spooler, otherwise, it won't work.
    sharedscripts
    postrotate
/bin/kill -HUP `cat /var/run/syslogd.pid 2> /dev/null` 2> /dev/null || true
    endscript
}

manually rotate log:

logrotate -f /etc/rsyslog.conf



How to use yum to update RHEL5 servers

Objective: use yum to install/update packages through RHN for RHEL5 servers

Steps:

  • user rhnreg_ks to register the server with RHN
rhnreg_ks --profilename=my_server_name --username=myusername --password=password [--proxy=http://10.0.0.1:8080]
 
note: you must make sure your server is able to reach Internet, either directly or through proxy server