My Ideas ,Linux related Links, etc..: January 2013

This is a sample I have written. This may give you an Idea.

#!/bin/sh
BOUNDARY="=== This is the boundary between parts of the message. ==="
DATE=`date +%Y%m%d`

(
#echo "To: ashenavandeh@??????.com"
echo "Subject: last 24 hours high priority Syslog messages"
echo "MIME-Version: 1.0"
echo "Content-Type: MULTIPART/MIXED; "
echo " BOUNDARY="\"$BOUNDARY\"
echo
echo "--${BOUNDARY}"
echo "Content-Type: TEXT/html;"
echo
echo "<html><body>"
echo "<H3>Last 24 hours high priority Syslog messages -"
date
echo "</H1>"
psql -U rsyslog syslog --html -c "select devicereportedtime as Date,Priority,fromhost as Source,syslogtag as Proc_Info,message as Message from systemevents where priority < 3 and devicereportedtime < now() - interval '1 day';"
echo "</body></html>"
echo
echo "--${BOUNDARY}"
echo "Content-Type: application/vnd.ms-excel charset=US-ASCII"
echo "Content-disposition: attachment; filename=syslog-$DATE.csv"
echo
psql -U rsyslog syslog -A -F ',' -c "select devicereportedtime as Date,Priority,fromhost as Source,syslogtag as Proc_Info,message as Message from systemevents where priority < 3 and devicereportedtime < now() - interval '1 day';"
echo
echo "--${BOUNDARY}"
) | /usr/sbin/sendmail -t

I am using rsyslog as it is more common in RHEL enviroments these days but I am sure you can find the equvalent packages in other OS and distributions:

Install postgreSQL module for rsyslog:
# yum install rsyslog-pgsql

In /etc/rsyslog.conf add following lines:

$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
# Provides UDP syslog reception
$ModLoad imudp
$UDPServerRun 514

# Provides TCP syslog reception
$ModLoad imtcp
$InputTCPServerRun 514

# Include all config files in /etc/rsyslog.d/
$IncludeConfig /etc/rsyslog.d/*.conf

Make the /etc/rsyslog.d/psql.conf file with the following contents:

$ModLoad ompgsql.so

$WorkDirectory /var/tmp/rsyslog/work

# This would queue _ALL_ rsyslog messages, i.e. slow them down to rate of DB ingest.
# Don't do that...
# $MainMsgQueueFileName mainq # set file name, also enables disk mode

# We only want to queue for database writes.
$ActionQueueType LinkedList # use asynchronous processing
$ActionQueueFileName dbq    # set file name, also enables disk mode
$ActionResumeRetryCount -1   # infinite retries on insert failure

*.*             :ompgsql:127.0.0.1,syslog,rsyslog,secret;

The format is:

*.*           :ompgsql:<DB HOST>,<DB USERNAME>,<DB NAME>,<PASSWORD>;

Now, to config postgreSQL, do the following changes in postgresql config file:
In /var/lib/pgsql/data/postgresql.conf :

listen_addresses = 'localhost'
port = 5432
max_connections = 100

And following changes to /var/lib/pgsql/data/pg_hba.conf to grant the local accesses:

# "local" is for Unix domain socket connections only
#local   all         all                               ident sameuser
local    all         all                               trust
# IPv4 local connections:
#host    all         all         127.0.0.1/32          ident sameuser
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
#host    all         all         ::1/128               ident sameuser
host    all         all         ::1/128               trust

Now restart the postgreSQL server:

# service postgresql restart

Create the database:

#su - postgres
-bash-4.1$ createuser rsyslog;
Shall the new role be a superuser? (y/n) y
-bash-4.1$ createdb -T template0 -E SQL_ASCII syslog;

-bash-4.1$ psql -l
                                  List of databases
   Name    | Owner   | Encoding | Collation |    Ctype    |   Access privil
eges
-----------+----------+-----------+-------------+-------------+----------------
-------
postgres | postgres | UTF8      | en_US.UTF-8 | en_US.UTF-8 |
syslog    | postgres | SQL_ASCII | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8      | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
(3 rows)

-bash-4.1$

Now we show create the database schema. The package has a file located at /usr/shade/doc//rsyslog-pgsql-5.8.10/createDB.sql which has the requiered schema. But I had to comment out the first line to make it work:

-- CREATE DATABASE Syslog WITH ENCODING 'SQL_ASCII';
\c syslog;
CREATE TABLE SystemEvents
(
        ID serial not null primary key,
        CustomerID bigint,
        ReceivedAt timestamp without time zone NULL,
        DeviceReportedTime timestamp without time zone NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);

CREATE TABLE SystemEventsProperties
(
        ID serial not null primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

Use the following line to apply the table schema assuming you are already in the right path:

#psql -U rsyslog syslog -f ./createDB.sql

Reload the rsyslog service and check if there is any error in /var/log/messages:

# service rsyslog reload

Did I say how to set a password for the rsyslog user in postgreSQL ?

# su - postgres
-bash-4.1$ psql
psql (8.4.13)
Type "help" for help.

postgres=# Alter user rsyslog with password 'secret';
ALTER ROLE
postgres=# \q
-bash-4.1$

This should work. you can see the logs in systemevents table:

# psql -W -Ursyslog syslog
Password for user rsyslog:
psql (8.4.13)
Type "help" for help.

syslog=# select count(*) from systemevents;
count
-------
6596
(1 row)

syslog=#

My Ideas ,Linux related Links, etc..

Tuesday, January 15, 2013

Attach multiple attachments to an email in shell

Outputting to CSV from Postgresql

Monday, January 14, 2013

How to make rsyslog to write syslogs in a database(PostgreSQL):

About Me