ver the past few years, the number of Windows-based worm attacks has grown dramatically. Companies have implemented full-scale virus scanning and Windows update facilities to help stop the worm attacks. But, what do you do when you aren’t entirely certain that your virus definitions are up to date, or some worm slips under the radar and infects hosts on your network? This article will show a method of using access lists on Cisco routers with a centralized logging database to find infected hosts quickly on your network.

This method was designed to work with Cisco routers, but can work with virtually any layer-3 device with syslog and access list capabilities. This method also will be using a Unix-based system and a relational database to store information. There are much more elaborate configurations out there that do similar things. This method is based on the syslog-ng configuration at http://vermeer.org/syslog/, although modified for Postgres and my own front end.

Since this solution deals with router configurations, make sure you fully understand what is happening with the configuration changes that are outlined here. Though nothing in this article is malicious, some parts need to be read carefully because a misunderstanding could have a severe impact on your network.

Basic Theory
This isn’t the most difficult thing to configure and set up, however, it is a bit lengthy, and there are many parts to it. This section will give an overview of what will need to be done, and what you can expect out of this on your network.

Figure 1. An Example Network: This example network demonstrates how the centralized syslog server concept works.

Assume you have a basic network (like the one shown in Figure 1). There’s one central office with three branch offices. Each office has 50-100 PCs and a few servers. Each branch office is connected to the central office via a frame relay T-1 connection. The central office has a 3MB frame relay connection to the branch offices. This example network explains how the centralized syslog server concept works, but this can extend to many different varieties of network configurations.

Virus Strikes!
Here’s the scenario: You notice that there’s a new virus or worm variant out there. The Windows System Administrators in your organization have pushed out the new virus definitions, however, you start noticing a huge amount of latency on the wide area network. Your strategy is to apply selective access lists on the ethernet interfaces of the branch office and central office routers, so you can see the hosts on those networks that are originating the traffic by querying the database on the syslog server. This gives your helpdesk people the capability to determine these hosts without any additional manpower from the network administrators.

Another strategy is to simply monitor for these new virus or worm variants, or anything else an for which an access list can be created. Even the best virus protection out there gives some window for the spread of new viruses. By crafting a special access list, you can easily ‘monitor’ the outgoing ports that your hosts are looking for. This, too, can be sent to your syslog server, and can be monitored for new infections.

Setting this up doesn’t solve all the network problems out there, and it isn’t foolproof. It’s simply a very valuable tool for doing network firefighting to keep your network up and functioning through virus and worm attacks.

Pre-Requisites
This article presumes you have a working, Unix-like host on your network?like a Linux or FreeBSD host. These instructions for installing each package are fairly generic, since various Unix variants have their own package management systems. Simply install the package via your package manager, and then do any post configuration that may be required.

First, you must have a working NTP server on your network, or your routers must have external NTP servers. An NTP server helps correlate events on your network across all your networking devices. An example of an NTP server is the ntp server at ntp.org. There are others out there. Take your pick.

Next, you need a working RDBMS to log to. Really, this should work with any RDBMS that you are familiar with, if it has a Unix-based client. The examples in this article uses PostgreSQL, which can be downloaded here. The current version as of this writing is 7.4.2.

You’ll also need to have a Web-server that allows for the running of CGI scripts. Apache 1.3 or 2.0 should work just fine. It’s not imperative that the Web server be installed on the same host as the RDBMS, since Perl should be able to access the database over the network. If the hosts are separate, you need to intall a client on the unix host.

Because you’ll be writing CGI’s in Perl to access the information in the database, your perl installation needs to have the DBI and CGI modules. Specifically, you need the DBI::Pg module to in order to perform reports on the syslog database. While I recommend installing these modules via packages (so that they automatically upgrad with your OS), you can also manually install them on your system. As root, run these commands on the system you expect to host the CGI scripts:

perl -MCPAN -e 'install DBI'perl -MCPAN -e 'install DBD::Pg'perl -MCPAN -e 'install CGI'

Finally, you’ll need to install syslog-ng (click here) to actually perform the logging of the data. Get version 1.6.x, since anything newer is still heavily under development. Current version as of this writing is 1.6.2 If you are compiling syslog-ng, you’ll also need to get libol from http://www.balabit.hu/downloads/syslog-ng/libol/0.3/. Also, if compiling, you’ll need a syslog-ng.conf?there are ones in the contrib directory of syslog-ng. Copy the one that best suits your system to /usr/local/etc/syslog-ng/syslog-ng.conf. You will probably have to make /usr/local/etc/syslog-ng.

Configure Your Router Base
Before you can get started, there are some basic things you need to configure on your routers. Keep in mind that this only outlines a basic NTP configuration. Also, I have my clocks set within a few seconds of each other, however, this may not be acceptable for your needs.

Make sure that your routers can freely access your NTP server by allowing any access that’s required through any firewalls existing on your network. To configure your router to use ntp, go into configure mode on your router and type:

RouterA#config tEnter configuration commands, one per line.  End with CNTL/Z.RouterA(config)#ntp server 10.0.37.15RouterA(config)#end

At this point, ‘show ntp status’ will show the status of the NTP synchronization:

RouterA#sh ntp statusClock is synchronized, stratum 3, reference is 10.0.37.15nominal freq is 250.0000 Hz, actual freq is 250.0000 Hz, precision is 2**18reference time is C44215A4.FC1FC6D7 (13:14:12.984 UTC Tue May 4 2004)clock offset is 0.0982 msec, root delay is 46.88 msecroot dispersion is 20.89 msec, peer dispersion is 0.08 msec

Type ‘sh clock’ at the prompt, and the time in UTC will be shown:

RouterA#sh clock13:16:13.344 UTC Tue May 4 2004

Ideally, there should be at least two NTP servers on your network so that the NTP clients can figure out how far off they may be.

The network that I support spans many timezones, so it’s easier for me to have everything in a single timezone, and I chose UTC for that timezone. If you prefer to use a different time zone, use this command to set it:

RouterA#config tEnter configuration commands, one per line.  End with CNTL/Z.RouterA(config)#clock timezone EST -5! set your timezone in UTC -x or +x hours above. I'm in US EST, so that's! my timezone.RouterA(config)#clock summer-time EDT recurring ! We do daylight savings time, so I'm configuring that as well.

Now when I type ‘sh clock’, I’m shown my current local time:

RouterA#sh clock09:23:00.968 EDT Tue May 4 2004

By default, Cisco routers use the router’s uptime to display network events, instead of ‘real’ time. This makes it somewhat difficult to use the syslog facilities, because it’s hard to determine how long ago certain events occurred. To fix this, configure the router to use timestamps instead of router uptime:

RouterA#config tEnter configuration commands, one per line.  End with CNTL/Z.RouterA(config)#service timestamps debug datetime msec localtime show-timezoneRouterA(config)#service timestamps log datetime msec localtime show-timezoneRouterA(config)#end

Another nice feature is the local buffering of logs. By default, only a few lines are buffered, and having more data immediately available is helpful in diagnosing network problems:

RouterA#config tEnter configuration commands, one per line.  End with CNTL/Z.RouterA(config)# logging buffered 9128 debuggingRouterA(config)#end

Now, if you type ‘show log’ at the prompt, you should see the last configuration. Type ‘copy run start’ at the prompt, and the ‘show log,’ and the timestamps should be shown for save event:

RouterA#copy run startDestination filename [startup-config]? Building configuration...[OK]RouterA#sh logSyslog logging: enabled (10 messages dropped, 2 messages rate-limited, 0 flushe)    Console logging: level debugging, 49 messages logged, xml disabled    Monitor logging: level debugging, 0 messages logged, xml disabled    Buffer logging: level debugging, 49 messages logged, xml disabled    Logging Exception size (4096 bytes)    Count and timestamp logging messages: disabled    Trap logging: level informational, 49 message lines logged          Log Buffer (9128 bytes):...cut...May  4 09:21:08.109 EDT: %SYS-5-CONFIG_I: Configured from console by consoleMay  4 09:22:59.992 EDT: %SYS-5-CONFIG_I: Configured from console by console

There is some more configuring to be done later, but for now, this sets everything up. Copy the configuration to each router from which you plan on centralizing the syslogs.

Syslog-ng Configuration
Traditionally, Unix systems use a syslog daemon to log system events. This is analogous to the Window’s Event Viewer, except that on a Unix system, a system log is typically in raw text format. The Event Viewer under Windows is actually modeled after the syslog daemon under Unix systems, so some of the concepts are the same. Different types of programs running on a Unix system can be logged differently and at varying levels. Another cool feature of a syslog daemon is that it can be configured to log across the network as well. This allows you to centralize logging for all of the Unix systems in an enterprise, making it easy to run various parsing scripts on these logs looking for security and hardware problems, as well as whatever else.

The syslog daemon typically shipped with Unix systems is somewhat inflexible, only providing for logging to text files on the drive. Syslog-ng, as it’s name implies, is the “next generation” of syslog daemons. Two of the concepts in syslog-ng that you’ll be using are ‘templates’ for your data, and extremely flexible ‘destination’ configuration.

First, you need to modify the syslog-ng.conf (usually in /etc/syslog-ng or /usr/local/etc/syslog-ng). Add the destination and the template for the Postgres database:

destination d_pgsql { pipe("/tmp/.syslogd.pipe" template("INSERT INTO syslog (ip, facility, priority, level, tag, date,time, program, msg) VALUES( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG','$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );
") template-escape(yes) owner("root") group("c-syslog") perm(0660)); };

Keep in mind that this can be used with any SQL client that is available on the system on which you’ve configured syslog-ng, so make sure that your SQL is valid for that particular RDBMS.

Next, configure the source/destination of the syslog to allow traffic via the network:

source net { udp(); };

Configure this to go to the previously configured destination:

log { source(net); destination(d_pgsql); };

Don’t start or restart your running syslog-ng yet.

Script Configuration
On Unix systems, the fifo file type provides a way to ensure that the first amount of data sent into the file comes out first and so on. In this section, you’ll create a fifo that will be writable via the syslog-ng process, running as root, and readable by a user in a group to send the syslog data to your RDBMS.

Create a user on your system (usually adduser or useradd) and call it c-syslog. It’s not absolutely necessary to give the user a password?if no password is set, nobody can log in as that user. Also create a syslog group, and put the c-syslog user into the syslog group.

To create a fifo, use the mkfifo command and run mkfifo /tmp/.syslogd.pipe. Set permissions on the fifo by using chmod 660 /tmp/.syslogd.pipe, then set chown root:syslog /tmp/.syslogd.pipe. Finally, type ls?al /tmp/.syslogd.pipe to make sure it was created properly:

prw-rw----  1 root  c-syslog  0 May  4 13:20 /tmp/.syslogd.pipe

Next, you’ll need a simple looping script. Cut and paste these contents and create a file in /usr/local/bin, call it syslog-db.sh, save it, and run chmod 755 syslog-db.sh:

#!/bin/bashPIPE="/tmp/.syslogd.pipe";if [ -e ${PIPE} ]; then  while [ -e ${PIPE} ]  do	# Edit this line to work with your sql client of choice.     /usr/local/pgsql/bin/psql -q -h  syslog < ${PIPE} > /dev/null 2>&1  doneelse  # Since some systems clean out /tmp on bootup, you would    # probably want to include the steps to automatically re-create   # the fifo here, but since those steps vary a bit, i haven't   # included them. Assuming you followed my instructions above   # they'd look like this:   # mkfifo /tmp/.syslogd.pipe   # chmod 660 /tmp/.syslogd.pipe   # chown root:syslog /tmp/.syslogd.pipe  echo “ERROR: fifo not created in ${PIPE}. Please create.”  exit(1)	fi

Change to reflect the host on your network running Postgres. It’s important to not restart syslog-ng or start running the looping script mentioned aboveuntil everything is ready.

Postgres Configuration
You’ll need to make some configuration changes in Postgres, as well as in the database schema and in the users that can access the data.

Postgres can be configured to allow connections from a certain host implicitly. Inside the $PGDATA directory inside your installation, there will be a pg_hba.conf file. Add a line to that file to allow connections from the host that’s sending the syslogs to the syslog server:

host    syslog      c-syslog         255.255.255.255  trusthost    syslog      c-syslog-rept    255.255.255.255  password

After editing the file, issue a pg_ctl reload to reload the configuration files. This isn’t the most secure method of doing this, but since those users can only select or insert data, and someone would have to take over those accounts, it should be relatively secure. Next, you’ll need to create the Postgres database that will be holding the data, followed by the creation of the table to hold the data:

bash-2.03$ createdb syslogCREATE DATABASE

Then, create the PL/pgsql language in the database we just created. This will be used for triggers and functions:

bash-2.03$ createlang plpgsql syslog

Now create the tables and functions surrounding the syslog table. Use the command-line client psql to connect to the database psql syslog and create the tables and functions shown below:

CREATE TABLE syslog (    r_id serial NOT NULL,    ip cidr NOT NULL,    facility character varying(10),    priority character varying(10),    level character varying(10),    tag character varying(10),    date date,    time time without time zone,    program character varying(15),    msg character varying(2048),    ins_time timestamp without time zone DEFAULT now(),    last_mod timestamp without time zone);

Postgres has a CIDR data type that understands network IP addresses. This allows you to to do things like find all of the IP addresses in a given subnet. The CIDR data type stores IP address in ‘ip address/netmask’ notation. The main thing to understand about this field is that your network devices in the ‘ip’ field are going to appear as a.b.c.d/32. Simply ignore the /32 at this point.

CREATE FUNCTION syslog_lm_f()RETURNS TRIGGERAS 'BEGIN   new.last_mod=now();   RETURN new;END; 'LANGUAGE 'plpgsql';CREATE TRIGGER syslog_lm_t    BEFORE INSERT OR UPDATE ON syslog    FOR EACH ROW    EXECUTE PROCEDURE syslog_lm_f();

Next, set permissions on the table. You’ll be creating two users. One user will only be able to insert data into the table. The second user will only be able to select from the table. Here are the commands for the insert-only user:

bash-2.03$ createuser -A -D -P c-syslogEnter password for new user: Enter it again: CREATE USER

These are the commands for the read-only user:

bash-2.03$ createuser -A -D -P c-syslog-reptEnter password for new user: Enter it again: CREATE USER

Now, set the permissions on the table. Access the database using psql syslog:

REVOKE ALL ON TABLE syslog FROM PUBLIC;GRANT INSERT ON TABLE syslog TO "c-syslog";GRANT SELECT ON TABLE syslog TO "c-syslog-rept";

Next, set permissions on the sequence to allow the c-syslog user to increment the counters.

REVOKE ALL ON TABLE syslog_r_id_seq FROM PUBLIC;GRANT SELECT,UPDATE ON TABLE syslog_r_id_seq TO "c-syslog";

For future reference, open up a psql session to the database by running psql syslog from the host as the postgres user and leave it running. On the host that’s running the syslog-db.sh script mentioned above, su to c-syslog (by running su c-syslog as root), and type psql -h syslog for a remote database host or psql syslog for a local database host to verify connectivity.

Syslog-ng Startup
It’s finally time to start up the syslog server. First, run the shell script you previously created as the c-syslog user:

(as root) su c-syslog -c /usr/local/bin/syslog-db.sh &

Second, start up syslog-ng, by either running its init script, or simply running syslog-ng at the prompt as root. Your system log should be up and running at this point. There should be an entry in /var/log/messages as well regarding the syslog-ng successful start-up.

Router Configuration, Phase Two
The routers need to have their system logging capabilities modified to allow for logging to the syslog database above. Add these lines to your configuration:

RouterA#config tEnter configuration commands, one per line.  End with CNTL/Z.RouterA(config)#logging rate-limit 5RouterA(config)#logging trap debuggingRouterA(config)#logging source-interface FastEthernet0/0RouterA(config)#logging  10.0.37.15RouterA(config)#end

The most important line of this configuration code is the logging rate-limit command. This command limits the number of syslog events that can be sent to your syslog server per second. This is ESPECIALLY important when dealing with virus traffic because of the high number of syslog events that it’s possible to receive. I have mine set very low, which means that there’s no guarantee that every system log event is going to hit the syslog server. However, I’m am assured that it’s not going to saturate my network.

Secondly, the source-interface is important. By default, IOS will pick whichever interface it desires from which to send the syslogs. It will be a bit easier to find these devices if you use the most commonly known IP address, such as the Ethernet interface. The router used in these scripts is a Cisco 2621 router, so it has a FastEthernet 0/0 port, but your router may have a different port.

Once the configuration has been modified, save the configuration. This will do two things. First, it’s going to save your changes, secondly, it should send an event to the syslog server, so you can check to see if the data is working properly.

Now run select msg from syslog to see if that syslog event that you just created is getting logged properly. The output should show all of the current syslog messages.

After you verify that one router is working properly, repeat the above steps on all of the routers that you would like to have logged to your centralized syslog.

The Fun Part
There are other front ends for system logs that are stored in SQL servers. However, I chose to write my own for a variety of reasons. Mainly, my processing needs are fairly simple, and I have some customized colorization needs to make it easier to parse through the data. I wrote a customized front end as a simple Perl CGI that runs under the Apache Web server.

First off, you need to verify that your CGI environment is configured properly. Apache comes with a simple CGI called test-cgi in the cgi-bin that Apache installs. Rename it to make it parse properly and make it executable:

	mv test-cgi test.cgi	chmod 755 test.cgi

Next, make sure your httpd.conf will process .cgi files by adding:

AddHandler cgi-script .cgiAddHandler cgi-script .pl

Now, access your Web server in a browser. The url should be http://server-name/cgi-bin/test.cgi. There should be a response like this:

CGI/1.0 test script report:argc is 0. argv is .SERVER_SOFTWARE = Apache/1.3.26 (Unix) mod_perl/1.27 PHP/4.2.3 mod_ssl/2.8.10 OpenSSL/0.9.6aSERVER_NAME = server-nameGATEWAY_INTERFACE = CGI/1.1SERVER_PROTOCOL = HTTP/1.1SERVER_PORT = 80...

That means that you’re ready to start with your CGI!

You’ll need to get the attached syslog.cgi script, and modify it a bit to suit your environment. First, you’ll need to edit the list of your IP addresses for your routers (look for the first FIXME section). If you’re not entirely certain which IP addresses the devices are using to send data to the syslog server, run the select distinct(ip) from syslog; on the syslog database. This will show all of the unique IP addresses. Make sure that each of these IP addresses are in this list.

Secondly, you’ll need to configure your Postgres logging information to the correct username and password for doing queries on the database (Look for the second FIXME section).

Finally, you’ll need to copy it to your Web server’s cgi-bin directory and set permissions on it. Type chmod 755 search.cgi on the cgi. I usually run the CGI from the command line to make sure I didn’t type-o anything the first time. If you run the CGI and all that’s shown is clear-counters.sh and put it in /usr/local/bin. Then run chmod 700 /usr/local/bin/clear-counters.sh on the file. You’ll need to edit the first section with your router IP address, username, password, and enable password. In the current incarnation of the script, a separate one will need to be created for each host.

#!/bin/sh######################################### Router's IP address#IP_ADDRESS=''# router username & password/enable passwordUSERNAME=''PASSWORD=''EPASS=''#### Collect data from the router# If your device doesn't have username/password for authentication, # you may need to remove  the ${USERNAME} here.###(echo "${USERNAME}";echo "${PASSWORD}";echo "term len 0";#### Go into enable mode.###echo "en";echo "${EPASS}";#### Everything beyond this point will be in enable mode.# USE CAUTION HERE!###echo "clear ip access-list counters";echo -e "
";echo "q";sleep 30)|telnet ${IP_ADDRESS}		Next, as root, run 'crontab -e' and enter in:	15 0 * * * /usr/local/bin/clear-counters.sh

The root user will get mail from that, but once you’re sure it’s running properly, add > /dev/null 2>&1 after it. This will eat the messages and stop the mail from coming. If you’d like to run it every hour, change the 15 0 * * *’ to ‘0 * * * * at the beginning of the line.

Your database will quickly grow with a lot of events. I run a script nightly for basic database maintenance to delete old syslog messages. I run this script as the postgres user on the system. In the postgres user’s home directory, I’ve created a bin directory, and the script resides inside that directory. As that user, create a script called cron_syslog_maint.sh and cut and paste the contents of this script into it. Then run chmod 700 cron_syslog_maint.sh on it. My syslog server receives a lot of events on a daily basis, so I only keep 14 days worth of data. On a network with less or more data you may want to change this. You’ll also need to edit this script to reflect the PGDATA and PGHOME on your system.

#!/bin/sh##PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/etc:.LD_LIBRARY_PATH=/usr/local/lib:/usr/local/ssl/lib:/usr/local/pgsql/libPGHOME=/usr/local/pgsqlPATH=$PATH:$PGHOME/binPGDATA=$PGHOME/dataexport PATH LD_LIBRARY_PATH  PGHOME PGDATA/usr/local/pgsql/bin/psql -c "delete from syslog where date < current_date-14" syslog

Next, add an entry to the user's cron tab to run the delete nightly. Edit the path according to where the postgres user home path is.

	15 0 * * * 

Postgres uses a method called vacuum on databases to clean out old records and old bits of information. This should be done on a regular basis to avoid slowingyour databases down with old unsed data. I recommend doing it at least once a day:

#!/bin/sh##PATH=$PATH:/usr/bin:/usr/sbin:/usr/local/bin:/etc:.LD_LIBRARY_PATH=/usr/local/lib:/usr/local/ssl/lib:/usr/local/pgsql/libPGHOME=/usr/local/pgsqlPATH=$PATH:$PGHOME/binPGDATA=$PGHOME/dataexport PATH LD_LIBRARY_PATH  PGHOME PGDATA/usr/local/pgsql/bin/vacuumdb -f ${1}

Run this cronjob as your postgres user a few times a day to clean out your database.

30 0  * * * /export/home/postgres/bin/cron_vacuum.sh nmt_db

Startup Scripts
Lastly, you'll need to create startup scripts for syslog-ng and the logging user. Paste the following script in your init scripts on your operating system, and call it c-syslog.sh. Under Linux systems, create the script in /etc/init.d. Set permissions on the script by chmod 755 /etc/init.d/c-syslog.sh. Then, link it into /etc/rc3.d by running 'ln -s /etc/init.d/c-syslog.sh /etc/rc3.d/c-syslog.sh.

#!/bin/shcase "$1" instart)        su syslog -c /home/syslog/bin/pipeme.sh &         sleep 5        /usr/local/sbin/syslog-ng        ;;stop)        echo "ERROR: Not Implemented"        ;;*)        echo "Usage: $0 {start|stop}"        ;;esac

Using a centralized syslog server can be very helpful generally, but it is especially handy in finding virus infected hosts?when used properly. Hopefully, at this point, you have a centralized syslog server with a Web-based interface, and can find it useful in your environment.