Gheek.net

November 8, 2011

Syslog-ng :: expanded-syslog-ng.conf (from campin.net)

Filed under: syslog-ng — lancevermilion @ 1:50 pm

Today Campin.net was unavailable so I captured a few pages i used frequently from the google cache.

#----------------------------------------------------------------------
#  Program:  syslog-ng.conf
#  Notes:    Embedded most of the manual notes within the configuration
#            file.  The original manual can be found at:
#
#            http://www.balabit.com/products/syslog_ng/reference/book1.html
#            http://www.campin.net/syslog-ng/faq.html
#            or
#            http://gheeknet.wordpress.com/2011/11/08/syslog-ng-faq-from-campin-net/
#
#            Many people may find placing all of this information in a
#            configuration file a bit redundant, but I have found that
#            with a little bit of extra comments and reference, 
#            maintaining these beasties is much easier.
#
#            This particular log file was taken from the examples that
#            are given at the different web sites, and made to emulate
#            the logs of a Mandrake Linux system as much as possible.
#            Of course, Unix is Unix, is Linux.  It should be generic
#            enough for any Unix system.
#----------------------------------------------------------------------
#  16-Mar-03 - REP - Added some extra definitions to the file.
#  15-Mar-03 - REP - Added back the comments on filtering.
#  27-Feb-03 - REP - Further modified for local environment.
#  27-Feb-03 - REP - Updated for new configuration and version 1.6.0
#  12-Dec-02 - REP - Continued updates for writing to databases.
#  30-Nov-02 - REP - Initial creation for testing.

#----------------------------------------------------------------------
#  Options
#----------------------------------------------------------------------
#
#  Name                       Values   Description
#  -------------------------  -------  ------------------------------------
#  bad_hostname               reg exp  A regexp which matches hostnames 
#                                      which should not be taken as such.
#  chain_hostnames            y/n      Enable or disable the chained 
#                                      hostname format.
#  create_dirs                y/n      Enable or disable directory creation 
#                                      for destination files.
#  dir_group                  groupid
#  dir_owner                  userid
#  dir_perm                   perm
#  dns_cache                  y/n      Enable or disable DNS cache usage.
#  dns_cache_expire           num      Number of seconds while a successful 
#                                      lookup is cached.
#  dns_cache_expire_failed    num      Number of seconds while a failed 
#                                      lookup is cached.
#  dns_cache_size             num      Number of hostnames in the DNS cache.
#  gc_busy_threshold          num      Sets the threshold value for the 
#                                      garbage collector, when syslog-ng is 
#                                      busy. GC phase starts when the number 
#                                      of allocated objects reach this 
#                                      number. Default: 3000.
#  gc_idle_threshold          num      Sets the threshold value for the 
#                                      garbage collector, when syslog-ng is 
#                                      idle. GC phase starts when the number 
#                                      of allocated objects reach this 
#                                      number. Default: 100.
#  group                      groupid
#  keep_hostname              y/n      Enable or disable hostname rewriting.
#                                      This means that if the log entry had
#                                      been passed through at least one other
#                                      logging system, the ORIGINAL hostname
#                                      will be kept attached to the log.  
#                                      Otherwise the last logger will be
#                                      considered the log entry owner and
#                                      the log entry will appear to have 
#                                      come from that host.
#  log_fifo_size              num      The number of lines fitting to the 
#                                      output queue
#  log_msg_size               num      Maximum length of message in bytes.
#  long_hostnames             on/off   This options appears to only really
#                                      have an affect on the local system.
#                                      which removes the source of the log.
#                                      As an example, normally the local
#                                      logs will state src@hostname, but
#                                      with this feature off, the source
#                                      is not reported.
#  mark                       num      The number of seconds between two 
#                                      MARK lines. NOTE: not implemented 
#                                      yet.
#  owner                      userid
#  perm                       perm
#  stats                      num      The number of seconds between two 
#                                      STATS.
#  sync                       num      The number of lines buffered before 
#                                      written to file
#  time_reap                  num      The time to wait before an idle 
#                                      destination file is closed.
#  time_reopen                num      The time to wait before a died 
#                                      connection is reestablished
#  use_dns                    y/n      Enable or disable DNS usage. 
#                                      syslog-ng blocks on DNS queries, 
#                                      so enabling DNS may lead to a 
#                                      Denial of Service attack. To 
#                                      prevent DoS, protect your 
#                                      syslog-ng network endpoint with 
#                                      firewall rules, and make sure that 
#                                      all hosts, which may get to 
#                                      syslog-ng is resolvable.
#  use_fqdn                   y/n      Add Fully Qualified Domain Name 
#                                      instead of short hostname.
#  use_time_recvd             y/n      Use the time a message is 
#                                      received instead of the one 
#                                      specified in the message.
#----------------------------------------------------------------------
#  15-Mar-03 - REP - Since some of the clocks are not quite right, we
#                    are going to go ahead and just use the local time
#                    as the master time.
#  12-Mar-03 - REP - We have taken a few configuration options from the
#                    newer Solaris configuration because some of the 
#                    reasons are valid for us as well.  We have increased
#                    the log_msg_size and log_fifo_size to increase the
#                    amount of buffering that we do.  While for most
#                    systems this may not have a noticeable affect, it
#                    will for systems that are at the end of a lot of
#                    logging systems.
#  20-Dec-02 - REP - Changed the stat() time from the default of 10
#                    minutes to once an hour.
#----------------------------------------------------------------------
options 
  {
    chain_hostnames(no);
    create_dirs (no);
    dir_perm(0755); 
    dns_cache(yes);
    keep_hostname(yes);
    log_fifo_size(2048);
    log_msg_size(8192);
    long_hostnames(on);
    perm(0644); 
    stats(3600);
    sync(0);
    time_reopen (10);
    use_dns(yes);
    use_fqdn(yes);
  };

#----------------------------------------------------------------------
#  Sources
#----------------------------------------------------------------------
#
#  fifo/pipe     - The pipe driver opens a named pipe with the 
#                  specified name, and listens for messages. It's 
#                  used as the native message getting protocol on 
#                  HP-UX. 
#  file          - Usually the kernel presents its messages in a 
#                  special file (/dev/kmsg on BSDs, /proc/kmsg on 
#                  Linux), so to read such special files, you'll need 
#                  the file() driver. Please note that you can't use 
#                  this driver to follow a file like tail -f does.
#  internal      - All internally generated messages "come" from this 
#                  special source. If you want warnings, errors and 
#                  notices from syslog-ng itself, you have to include 
#                  this source in one of your source statements. 
#  sun-streams   - Solaris uses its STREAMS API to send messages to 
#                  the syslogd process. You'll have to compile 
#                  syslog-ng with this driver compiled in (see 
#                  ./configure --help).
#
#                  Newer versions of Solaris (2.5.1 and above), uses a 
#                  new IPC in addition to STREAMS, called door to 
#                  confirm delivery of a message. Syslog-ng supports 
#                  this new IPC mechanism with the door() option.
#
#                  The sun-streams() driver has a single required 
#                  argument, specifying the STREAMS device to open and 
#                  a single option. 
#  tcp/udp       - These drivers let you receive messages from the 
#                  network, and as the name of the drivers show, you 
#                  can use both UDP and TCP as transport. 
#
#                  UDP is a simple datagram oriented protocol, which 
#                  provides "best effort service" to transfer 
#                  messages between hosts. It may lose messages, and 
#                  no attempt is made to retransmit such lost 
#                  messages at the protocol level. 
#
#                  TCP provides connection-oriented service, which 
#                  basically means a flow-controlled message pipeline. 
#                  In this pipeline, each message is acknowledged, and 
#                  retransmission is done for lost packets. Generally 
#                  it's safer to use TCP, because lost connections can 
#                  be detected, and no messages get lost, but 
#                  traditionally the syslog protocol uses UDP. 
#
#                  None of tcp() and udp() drivers require positional 
#                  parameters. By default they bind to 0.0.0.0:514, 
#                  which means that syslog-ng will listen on all 
#                  available interfaces, port 514. To limit accepted 
#                  connections to one interface only, use the 
#                  localip() parameter as described below. 
#
#    Options:
#
#    Name            Type    Description                       Default
#    --------------  ------  --------------------------------  --------
#    ip or local ip  string  The IP address to bind to. Note   0.0.0.0
#                            that this is not the address 
#                            where messages are accepted 
#                            from.
#    keep-alive     y/n      Available for tcp() only, and     yes
#                            specifies whether to close 
#                            connections upon the receival 
#                            of a SIGHUP signal.
#    max-connections number  Specifies the maximum number of   10
#                            simultaneous connections.
#    port or local   port    number The port number to bind     514
#                            to.
#    --------------  ------  --------------------------------  --------
#
#  unix-stream   -  unix-dgram - These two drivers behave similarly: 
#                   they open the given AF_UNIX socket, and start 
#                   listening on them for messages. unix-stream() is 
#                   primarily used on Linux, and uses SOCK_STREAM 
#                   semantics (connection oriented, no messages are 
#                   lost), unix-dgram() is used on BSDs, and uses 
#                   SOCK_DGRAM semantics, this may result in lost 
#                   local messages, if the system is overloaded.
#
#                   To avoid denial of service attacks when using 
#                   connection-oriented protocols, the number of 
#                   simultaneously accepted connections should be 
#                   limited. This can be achieved using the 
#                   max-connections() parameter. The default value of 
#                   this parameter is quite strict, you might have to 
#                   increase it on a busy system.
#
#                   Both unix-stream and unix-dgram has a single 
#                   required positional argument, specifying the 
#                   filename of the socket to create, and several 
#                   optional parameters. 
#
#    Options:
#
#    Name            Type    Description                       Default
#    --------------  ------  --------------------------------  --------
#    group           string  Set the gid of the socket.        root
#    keep-alive      y/n     Selects whether to keep           yes
#                            connections opened when 
#                            syslog-ng is restarted, can be 
#                            used only with unix-stream().
#    max-connections numb    Limits the number of              10
#                            simultaneously opened 
#                            connections. Can be used only 
#                            with unix-stream().
#    owner           string  Set the uid of the socket.        root
#    perm            num     Set the permission mask. For      0666
#                            octal numbers prefix the number 
#                            with '0', e.g. use 0755 for 
#                            rwxr-xr-x.
#----------------------------------------------------------------------
#  Notes:    For Linux systems (and especially RedHat derivatives), 
#            they have a second logging process for kernel messages.  
#            This source is /proc/kmsg.  If you are running this on a 
#            system that is not Linux, then the source entry for this 
#            should be removed.
#
#            It seems that there is some performance questions related
#            to what type of source stream should be used for Linux 
#            boxes.  The documentation states the /dev/log should use
#            unix-stream, but from the mailing list it has been
#            strongly suggested that unix-dgram be used.
#
#  WARNING:  TCP wrappers has been enabled for this system, and unless
#            you also place entries in /etc/hosts.allow for each of the
#            devices that will be delivering logs via TCP, you will
#            NOT receive the logs.
#
#            Also note that if there is any form of a local firewall,
#            this will also need to be altered such that the incoming
#            and possibly outgoing packets are allowed by the firewall
#            rules.
#----------------------------------------------------------------------
#  There has been a lot of debate on whether everything should be put
#  to a single source, or breakdown all the sources into individual
#  streams.  The greatest flexibility would be in many, but the most
#  simple is the single.  Since we wrote this file, we have chosen the
#  route of maximum flexibility.
#
#  For those of you that like simplicity, this could have also been
#  done as the follows:
#
#  source src 
#    {
#      internal();
#      pipe("/proc/kmsg" log_prefix("kernel: "));
#      tcp(ip(127.0.0.1) port(4800) keep-alive(yes));
#      udp();
#      unix-stream("/dev/log");
#    };
#
#  You would also have to change all the log statements to only 
#  reference the now single source stream.
#----------------------------------------------------------------------
#  16-Mar-03 - REP - The default number of allowed TCP connects is set
#                    very low for a logserver.  This value should only
#                    be set greater than the default for servers that
#                    will actually be serving that many systems.
#----------------------------------------------------------------------
source s_dgram
  { unix-dgram("/dev/log"); };

source s_internal
  { internal(); };

source s_kernel
  { pipe("/proc/kmsg" log_prefix("kernel: ")); };

source s_tcp
  { tcp(port(4800) keep-alive(yes) max_connections(100)); };

#----------------------------------------------------------------------
#  Destinations
#----------------------------------------------------------------------
#
#  fifo/pipe    - This driver sends messages to a named pipe like 
#                 /dev/xconsole
#
#                 The pipe driver has a single required parameter, 
#                 specifying the filename of the pipe to open, and 
#                 no options. 
#  file         - The file driver is one of the most important 
#                 destination drivers in syslog-ng. It allows you to 
#                 output messages to the named file, or as you'll see 
#                 to a set of files.
#
#                 The destination filename may include macros which 
#                 gets expanded when the message is written, thus a 
#                 simple file() driver may result in several files 
#                 to be created. Macros can be included by prefixing 
#                 the macro name with a '$' sign (without the quotes), 
#                 just like in Perl/PHP.
#
#                 If the expanded filename refers to a directory 
#                 which doesn't exist, it will be created depending 
#                 on the create_dirs() setting (both global and a per 
#                 destination option)
#
#                 WARNING: since the state of each created file must 
#                 be tracked by syslog-ng, it consumes some memory 
#                 for each file. If no new messages are written to a 
#                 file within 60 seconds (controlled by the time_reap 
#                 global option), it's closed, and its state is freed.
#
#                 Exploiting this, a DoS attack can be mounted against 
#                 your system. If the number of possible destination 
#                 files and its needed memory is more than the amount 
#                 your logserver has.
#
#                 The most suspicious macro is $PROGRAM, where the 
#                 possible variations is quite high, so in untrusted 
#                 environments $PROGRAM usage should be avoided. 
#
#    Macros:
#
#    Name               Description
#    -----------------  -----------------------------------------------
#    DATE               Date of the transaction.
#    DAY                The day of month the message was sent.
#    FACILITY           The name of the facility, the message is tagged 
#                       as coming from.
#    FULLDATE           Long form of the date of the transaction.
#    FULLHOST           Full hostname of the system that sent the log.
#    HOST               The name of the source host where the message 
#                       is originated from. If the message traverses 
#                       several hosts, and chain_hostnames() is on, 
#                       the first one is used.
#    HOUR               The hour of day the message was sent.
#    ISODATE            Date in ISO format.
#    MIN                The minute the message was sent.
#    MONTH              The month the message was sent.
#    MSG or MESSAGE     Message contents. 
#    PRIORITY or LEVEL  The priority of the message. 
#    PROGRAM            The name of the program the message was sent by.
#    SEC                The second the message was sent.
#    TAG                The priority and facility encoded as a 2 digit 
#                       hexadecimal number.
#    TZ                  The time zone or name or abbreviation. e.g. 'PDT'
#    TZOFFSET           The time-zone as hour offset from GMT. e.g. 
#                       '-0700'
#    WEEKDAY            The 3-letter name of the day of week the 
#                       message was sent, e.g. 'Thu'.
#    YEAR               The year the message was sent. Time expansion 
#                       macros can either use the time specified in 
#                       the log message, e.g. the time the log message 
#                       is sent, or the time the message was received 
#                       by the log server. This is controlled by the 
#                       use_time_recvd() option.
#    -----------------  -----------------------------------------------
#
#    Options:
#
#    Name            Type    Description                       Default
#    --------------  ------  --------------------------------  --------
#    compress        y/n     Compress the resulting logfile    global
#                            using zlib. NOTE: this is not     setting
#                            implemented as of 1.3.14.
#    reate_dirs      y/n     Enable creating non-existing      no
#                            directories.
#    dir_perm        num     The permission mask of            0600
#                            directories created by 
#                            syslog-ng. Log directories are 
#                            only created if a file after 
#                            macro expansion refers to a 
#                            non-existing directory, and dir 
#                            creation is enabled using 
#                            create_dirs().
#    encrypt         y/n     Encrypt the resulting file.       global
#                            NOTE: this is not implemented as  setting
#                            of 1.3.14.
#    fsync           y/n     Forces an fsync() call on the 
#                            destination fd after each write. 
#                            Note: this may degrade 
#                            performance seriously
#    group           string  Set the group of the created      root
#                            filename to the one specified.
#    log_fifo_size   num     The number of entries in the      global
#                            output fifo.                      setting
#    owner           string  Set the owner of the created      root
#                            filename to the one specified.
#    perm            num     The permission mask of the file   0600
#                            if it is created by syslog-ng.
#    remove_if_older num     If set to a value higher than 0,  0
#                            before writing to a file, 
#                            syslog-ng checks whether this 
#                            file is older than the specified 
#                            amount of time (specified in 
#                            seconds). If so, it removes the 
#                            existing file and the line to 
#                            be written is the first line in 
#                            a new file with the same name. 
#                            In combination with e.g. the 
#                            $WEEKDAY macro, this is can be 
#                            used for simple log rotation, 
#                            in case not all history need to 
#                            be kept. 
#    sync_freq       num     The logfile is synced when this   global
#                            number of messages has been       setting
#                            written to it.
#    template        string  Specifies a template which 
#                            specifies the logformat to be 
#                            used in this file. The possible 
#                            macros are the same as in 
#                            destination filenames.
#    template_escape y/n     Turns on escaping ' and " in      yes
#                            templated output files. It is 
#                            useful for generating SQL 
#                            statements and quoting string 
#                            contents so that parts of your 
#                            log message don't get 
#                            interpreted as commands to the 
#                            SQL server.
#    --------------  ------  --------------------------------  --------
#
#  program      - This driver fork()'s executes the given program with 
#                 the given arguments and sends messages down to the 
#                 stdin of the child.
#
#                 The program driver has a single required parameter, 
#                 specifying a program name to start and no options. 
#                 The program is executed with the help of the current 
#                 shell, so the command may include both file patterns 
#                 and I/O redirection, they will be processed. 
#
#                 NOTE: the program is executed once at startup, and 
#                 kept running until SIGHUP or exit. The reason is to 
#                 prevent starting up a large number of programs for 
#                 messages, which would imply an easy DoS. 
#  tcp/udp      - This driver sends messages to another host on the 
#                 local intranet or internet using either UDP or TCP 
#                 protocol.
#
#                 Both drivers have a single required argument 
#                 specifying the destination host address, where 
#                 messages should be sent, and several optional 
#                 parameters. Note that this differs from source 
#                 drivers, where local bind address is implied, and 
#                 none of the parameters are required. 
#
#    Options:
#
#    Name            Type    Description                       Default
#    --------------  ------  --------------------------------  --------
#    localip         string  The IP address to bind to before  0.0.0.0
#                            connecting to target.
#    localport       num     The port number to bind to.       0
#    port/destport   num     The port number to connect to.   514
#    --------------  ------  --------------------------------  --------
#  usertty      - This driver writes messages to the terminal of a 
#                 logged-in user.
#
#                 The usertty driver has a single required argument, 
#                 specifying a username who should receive a copy of 
#                 matching messages, and no optional arguments. 
#  unix-dgram   - unix-stream -  This driver sends messages to a unix 
#                 socket in either SOCK_STREAM or SOCK_DGRAM mode.
#
#                 Both drivers have a single required argument 
#                 specifying the name of the socket to connect to, and 
#                 no optional arguments. 
#----------------------------------------------------------------------

#----------------------------------------------------------------------
#  Standard Log file locations
#----------------------------------------------------------------------
destination authlog        { file("/var/log/auth.log"); };
destination bootlog        { file("/var/log/boot.log"); };
destination debug          { file("/var/log/debug"); };
destination explan         { file("/var/log/explanations"); };
destination messages       { file("/var/log/messages"); };
destination routers        { file("/var/log/routers.log"); };
destination secure         { file("/var/log/secure"); };
destination spooler        { file("/var/log/spooler"); };
destination syslog         { file("/var/log/syslog"); };
destination user           { file("/var/log/user.log"); };

#----------------------------------------------------------------------
#  Special catch all destination sorting by host
#----------------------------------------------------------------------
destination hosts          { file("/var/log/HOSTS/$HOST/$YEAR/$MONTH/$DAY/$FACILITY_$HOST_$YEAR_$MONTH_$DAY" 
			     owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes)); };

#----------------------------------------------------------------------
#  Forward to a loghost server
#----------------------------------------------------------------------
#destination loghost       { udp("10.1.1.254" port(514)); };

#----------------------------------------------------------------------
#  Mail subsystem logs
#----------------------------------------------------------------------
destination mail           { file("/var/log/mail.log"); };
destination mailerr        { file("/var/log/mail/errors"); };
destination mailinfo       { file("/var/log/mail/info"); };
destination mailwarn       { file("/var/log/mail/warnings"); };

#----------------------------------------------------------------------
#  INN news subsystem
#----------------------------------------------------------------------
destination newscrit       { file("/var/log/news/critical"); };
destination newserr        { file("/var/log/news/errors"); };
destination newsnotice     { file("/var/log/news/notice"); };
destination newswarn       { file("/var/log/news/warnings"); };

#----------------------------------------------------------------------
#  Cron subsystem
#----------------------------------------------------------------------
destination cron           { file("/var/log/cron.log"); };
destination crondebug      { file("/var/log/cron/debug"); }; 
destination cronerr        { file("/var/log/cron/errors"); }; 
destination croninfo       { file("/var/log/cron/info"); }; 
destination cronwarn       { file("/var/log/cron/warnings"); }; 

#----------------------------------------------------------------------
#  LPR subsystem
#----------------------------------------------------------------------
destination lpr            { file("/var/log/lpr.log"); };
destination lprerr         { file("/var/log/lpr/errors"); }; 
destination lprinfo        { file("/var/log/lpr/info"); }; 
destination lprwarn        { file("/var/log/lpr/warnings"); }; 

#----------------------------------------------------------------------
#  Kernel messages
#----------------------------------------------------------------------
destination kern           { file("/var/log/kern.log"); };
destination kernerr        { file("/var/log/kernel/errors"); }; 
destination kerninfo       { file("/var/log/kernel/info"); }; 
destination kernwarn       { file("/var/log/kernel/warnings"); }; 

#----------------------------------------------------------------------
#  Daemon messages
#----------------------------------------------------------------------
destination daemon         { file("/var/log/daemon.log"); };
destination daemonerr      { file("/var/log/daemons/errors"); };
destination daemoninfo     { file("/var/log/daemons/info"); };
destination daemonwarn     { file("/var/log/daemons/warnings"); };

#----------------------------------------------------------------------
#  Console warnings
#----------------------------------------------------------------------
destination console        { file("/dev/tty12"); };

#----------------------------------------------------------------------
#  All users
#----------------------------------------------------------------------
destination users          { usertty("*"); };

#----------------------------------------------------------------------
#  Examples of programs that accept syslog messages and do something
#  programatically with them.
#----------------------------------------------------------------------
#destination mail-alert     { program("/usr/local/bin/syslog-mail"); };
#destination mail-perl      { program("/usr/local/bin/syslog-mail-perl"); };

#----------------------------------------------------------------------
#  Piping to Swatch
#----------------------------------------------------------------------
#destination swatch         { program("/usr/bin/swatch --read-pipe=\"cat /dev/fd/0\""); };

#----------------------------------------------------------------------
#  Database notes:
#
#  Overall there seems to be three primary methods of putting data from
#  syslog-ng into a database.  Each of these has certain pros and cons.
#
#  FIFO file:    Simply piping the template data into a First In, First
#                Out file.  This will create a stream of data that will
#                not require any sort of marker or identifier of how
#                much data has been read.  This is the most elegant of
#                the solutions and probably the most unstable.
#
#                Pros:  Very fast data writes and reads.  Data being
#                       inserted into a database will be near real
#                       time.
#
#                Cons:  Least stable of all the possible solutions,
#                       and could require a lot of custom work to
#                       make function on any particular Unix system.
#
#                       Loss of the pipe file will cause complete
#                       data loss, and all following data that would
#                       have been written to the FIFO file.
#
#  Buffer file:  While very similar to a FIFO file this is would be a
#                text file which would buffer all the template 
#                output information.  Another program from cron or
#                similar service would then run and source the buffer
#                files and process the data into the database.
#
#                Pros:  Little chance of losing data since everything
#                       will be written to a physical file much like
#                       the regular logging process.  
#
#                       This method gives a tremendous amount of 
#                       flexibility since there would be yet another
#                       opportunity to filter logs prior to inserting
#                       any data into the database.
#
#                Cons:  Because there must be some interval between
#                       the processing of the buffer files, there will
#                       be a lag before the data is inserted in to the
#                       database.  
#
#                       There is also a slight chance of data corruption 
#                       (ie bad insert command) if the system crashes 
#                       during a write, although this scenero is very 
#                       unlikely.  
#
#                       Another possible issue is that because multiple 
#                       buffer files be written, the previously run 
#                       sourcing file could get behind the data 
#                       insertion if there is a very large quantity of 
#                       logs being written.  This will totally depend 
#                       on the system that this is running on.
#
#  Program:      The least elegant of the solutions.  This method is to
#                send the stream of data through some further interrupter
#                program such as something in Perl or C.  That program
#                will then take some action based off the data which
#                could include writing to a database similarly to the
#                program "sqlsyslogd".
#
#                Pros:  Allows complete control of the data, and as much
#                       post processing as required.
#
#                Cons:  Slowest of all the forms.  Since the data will
#                       have to go through some post processing it will
#                       cause data being written to the database to 
#                       remain behind actual log records.  This could
#                       cause a race condition in that logging is lost
#                       either due to system crash, or high load on
#                       the logging system.
#
#----------------------------------------------------------------------

#----------------------------------------------------------------------
#  Writing to a MySQL database:
#
#  Assumes a table/database structure of:
#
#            CREATE DATABASE syslog;
#            USE syslog;
#
#            CREATE TABLE logs ( host varchar(32) default NULL, 
#                                facility varchar(10) default NULL, 
#                                priority varchar(10) default NULL, 
#                                level varchar(10) default NULL, 
#                                tag varchar(10) default NULL, 
#                                date date default NULL, 
#                                time time default NULL, 
#                                program varchar(15) default NULL, 
#                                msg text, seq int(10) unsigned NOT NULL auto_increment, 
#                                PRIMARY KEY (seq), 
#                                KEY host (host), 
#                                KEY seq (seq), 
#                                KEY program (program), 
#                                KEY time (time), 
#                                KEY date (date), 
#                                KEY priority (priority), 
#                                KEY facility (facility)) 
#                                TYPE=MyISAM;
#
#----------------------------------------------------------------------
#  Piping method
#----------------------------------------------------------------------
#destination database       { pipe("/tmp/mysql.pipe"
#                             template("INSERT INTO logs (host, facility, 
#                             priority, level, tag, date, time, program, 
#                             msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', 
#                             '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY', 
#                             '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") 
#                             template-escape(yes)); };

#----------------------------------------------------------------------
#  Buffer file method
#----------------------------------------------------------------------
destination database       { file("/var/log/dblog/fulllog.$YEAR.$MONTH.$DAY.$HOUR.$MIN.$SEC"
                             template("INSERT INTO logs (host, facility, 
                             priority, level, tag, date, time, program, 
                             msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', 
                             '$LEVEL', '$TAG', '$YEAR-$MONTH-$DAY', 
                             '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") 
			     owner(root) group(root) perm(0600) 
			     dir_perm(0700) create_dirs(yes)
                             template-escape(yes)); };

#----------------------------------------------------------------------
#  Program method (alternate using sqlsyslogd):
#
#  Notes:  This is not a bad process, but lacks very much flexibility
#          unless more changes are made to the source of sqlsyslogd.
#          This is because sqlsyslogd assumes the data in a larger
#          object style instead of breaking it down into smaller
#          columnar pieces.
#----------------------------------------------------------------------
#destination database       { program("/usr/local/sbin/sqlsyslogd -u
#                             sqlsyslogd -t logs sqlsyslogs2 -p"); };

#----------------------------------------------------------------------
#  Since we probably will not be putting ALL of our logs in the database
#  we better plan on capturing that data that we will be discarding for
#  later review to insure we did not throw anything away we really
#  should have captured.
#----------------------------------------------------------------------
destination db_discard     { file("/var/log/discard.log"); };

#----------------------------------------------------------------------
#  Filters
#----------------------------------------------------------------------
#
#  Functions:
#
#  Name               Synopsis                        Description
#  --------------  ------------------------------  --------------------
#  facility        facility(facility[,facility])    Match messages 
#                                                  having one of the 
#                                                  listed facility code.
#  filter          Call another filter rule and 
#                  evaluate its value
#  host            host(regexp)                    Match messages by 
#                                                  using a regular 
#                                                  expression against 
#                                                  the hostname field 
#                                                  of log messages.
#  level/priority  level(pri[,pri1..pri2[,pri3]])  Match messages based 
#                                                  on priority.
#  match           Tries to match a regular 
#                  expression to the message 
#                  itself.
#  program         program(regexp)                 Match messages by 
#                                                  using a regular 
#                                                  expression against 
#                                                  the program name 
#                                                  field of log messages
#----------------------------------------------------------------------
#  NOTES: 
#
#  Getting filtering to work right can be difficult because while the
#  syntax is fairly simple, it is not well documented.  To illustrate
#  a brief lesson on filtering and to explain the majority of the
#  mechanics, we shall use the filter from the PostgreSQL database
#  how-to page found at:  http://www.umialumni.com/~ben/SYSLOG-DOC.html
#                            
#  This is a perfect and somewhat complex example to use.  In its
#  original form it resembles:
#                            
#  filter f_postgres { not(
#                            (host("syslogdb") and facility(cron) and level(info))
#                         or (facility(user) and level(notice)
#                                 and ( match(" gethostbyaddr: ")
#                                       or match("last message repeated ")
#                                      )
#                             )
#                         or ( facility(local3) and level(notice)
#                               and match(" SYSMON NORMAL "))
#                         or ( facility(mail) and level(warning)
#                               and match(" writable directory")
#                            )
#                         or (  ( host("dbserv1.somecompany.com")
#                                 or host("dbserv2.somecompany.com")
#                               )
#                               and facility(auth) and level(info)
#                               and match("su oracle") and match(" succeeded for root on /dev/")
#                            )
#                         ); };
#
#  While in this form, it does not induce a tremendous amount of 
#  insight on what the specific filter is attempting to accomplish.  In
#  reformatting the filter to resemble something a bit more human
#  readable, it would look like:
#
#  filter f_postgres { not
#                        (
#                          (
#                            host("syslogdb") and 
#                            facility(cron) and 
#                            level(info)
#                          ) or 
#                          (
#                            facility(user) and 
#                            level(notice) and 
#                            ( 
#                              match(" gethostbyaddr: ") or 
#                              match("last message repeated ")
#                            )
#                        ) or 
#                        ( 
#                          facility(local3) and 
#                          level(notice) and 
#                          match(" SYSMON NORMAL ")
#                        ) or 
#                        ( 
#                          facility(mail) and 
#                          level(warning) and 
#                          match(" writable directory")
#                        ) or 
#                        (  
#                          ( 
#                            host("dbserv1.somecompany.com") or 
#                            host("dbserv2.somecompany.com")
#                          ) and 
#                          facility(auth) and 
#                          level(info) and 
#                          match("su oracle") and 
#                          match(" succeeded for root on /dev/")
#                        )
#                      ); 
#                   };
#
#  Now in this form we can now begin to see what this filter has been
#  attempting to accomplish.  We can now further breakdown each logical
#  section and explain the different methods:
#
#  [1]  As in all statements in syslog-ng, each of the beginnings and
#       endings must be with a curly bracket "{" "}" to clearly denote
#       the start and finish.
#
#       In this filter, the entire filter is preferred by a "not" to 
#       indicate that these are the messages that we are NOT interested
#       in and should be the ones filtered out.  All lines of logs that
#       do not match these lines will be sent to the destination.
#
#  { not
#
#  [2]  The first major part of the filter is actually a compound
#       filter that has two parts.  Because the two parts are separated
#       by an "or", only one of the two parts must be matched for that
#       line of log to be filtered.
#
#  [2a]  In the first part of this filter there are three requirements
#        to be met for the filter to take affect.  These are the host 
#        string "syslogdb". the facility "cron", and the syslog level
#        of info.
#
#  (
#    (
#      host("syslogdb") and 
#      facility(cron) and 
#      level(info)
#    ) or 
#
#  [2b]  In the second part of the filter, which in itself is a 
#        compound filter, there are three requirements as well.  These
#        are that the facility of "user", and the log level of "notice"
#        are met in addition to one of the two string matches that are
#        shown in the example.
#
#  (
#    facility(user) and 
#    level(notice) and 
#    ( 
#      match(" gethostbyaddr: ") or 
#      match("last message repeated ")
#    )
#  ) or
#
#  [3]  In the section of the filter there are once again three
#       requirements to fire off a match which are a facility of "level3"
#       a log level of "notice" and a sting match of " SYSMON NORMAL ".
#
#  ( 
#    facility(local3) and 
#    level(notice) and 
#    match(" SYSMON NORMAL ")
#  ) or 
#
#  [4]  This part of the filter is very similar to the previous
#       filter, but with different search patterns.
#
#  ( 
#    facility(mail) and 
#    level(warning) and 
#    match(" writable directory")
#  ) or 
#
#  [5]  The last section of the filter is also a compound filter
#       that to take affect will require that one of two hosts
#       are matched, the facility of "auth", and log level of 
#       "info" occur in addition to the two string matches.
#
#  (  
#    ( 
#      host("dbserv1.somecompany.com") or 
#      host("dbserv2.somecompany.com")
#    ) and 
#    facility(auth) and 
#    level(info) and 
#    match("su oracle") and 
#    match(" succeeded for root on /dev/")
#  )
#
#  [6]  As in all command sets in syslog-ng, each of the statements 
#       must be properly closed with the correct ending punctuation
#       AND a semi-colon.  Do not forget both, or you will be faced with
#       an error.
#
#  ); };
#
#  While this may not be the most complete example, it does cover the
#  majority of the options and features that are available within the
#  current version of syslog-ng.
#----------------------------------------------------------------------

#----------------------------------------------------------------------
#  Standard filters for the standard destinations.
#----------------------------------------------------------------------
filter      f_auth         { facility(auth, authpriv); };
filter      f_authpriv     { facility(authpriv); }; 
filter      f_cron         { facility(cron); };
filter      f_daemon       { facility(daemon); };
filter      f_kern         { facility(kern); };
filter      f_local1       { facility(local1); };
filter      f_local2       { facility(local2); };
filter      f_local3       { facility(local3); };
filter      f_local4       { facility(local4); };
filter      f_local5       { facility(local5); };
filter      f_local6       { facility(local6); };
filter      f_local7       { facility(local7); };
filter      f_lpr          { facility(lpr); };
filter      f_mail         { facility(mail); };
filter      f_messages     { facility(daemon, kern, user); };
filter      f_news         { facility(news); };
filter      f_spooler      { facility(uucp,news) and level(crit); };
filter      f_syslog       { not facility(auth, authpriv) and not facility(mail); };
filter      f_user         { facility(user); };

#----------------------------------------------------------------------
#  Other catch-all filters
#----------------------------------------------------------------------
filter      f_crit         { level(crit); };
#filter     f_debug        { not facility(auth, authpriv, news, mail); };
filter      f_debug        { level(debug); };
filter      f_emergency    { level(emerg); };
filter      f_err          { level(err); };
filter      f_info         { level(info); };
filter      f_notice       { level(notice); };
filter      f_warn         { level(warn); };

#----------------------------------------------------------------------
#  Filer for the MySQL database pipe.  These are things that we really
#  do not care to see otherwise they may fill up our database with
#  garbage.
#----------------------------------------------------------------------
#filter      f_db           { not facility(kern) and level(info, warning) or
#                             not facility(user) and level(notice) or
#                             not facility(local2) and level(debug); };
#
#filter      f_db           { not match("last message repeated ") or
#                             not match("emulate rawmode for keycode"); };
#
#filter      f_discard      { facility(kern) and level(info, warning) or
#                             facility(user) and level(notice) or
#			     facility(local2) and level(debug); };
#
#filter      f_discard      { match("last message repeated ") or
#                             match("emulate rawmode for keycode"); };

#----------------------------------------------------------------------
#  Logging
#----------------------------------------------------------------------
#
#  Notes:  When applying filters, remember that each subsequent filter
#          acts as a filter on the previous data flow.  This means that
#          if the first filter limits the flow to only data from the
#          auth system, a subsequent filter for authpriv will cause
#          no data to be written.  An example of this would be:
#
# log { source(s_dgram);
#       source(s_internal);
#       source(s_kernel);
#       source(s_tcp);
#       source(s_udp);      filter(f_auth); 
#                           filter(f_authpriv);  destination(authlog); };
#
#          So, one can cancel out the other.
#
#  There are also certain flags that can be attached to each of the log
#  statements:
#
#  Flag      Description
#  --------  ----------------------------------------------------------
#  catchall  This flag means that the source of the message is ignored, 
#            only the filters are taken into account when matching 
#            messages.
#  fallback  This flag makes a log statement 'fallback'. Being a 
#            fallback statement means that only messages not matching 
#            any 'non-fallback' log statements will be dispatched.
#  final     This flag means that the processing of log statements ends 
#            here. Note that this doesn't necessarily mean that 
#            matching messages will be stored once, as they can be 
#            matching log statements processed prior the current one.
#----------------------------------------------------------------------

#----------------------------------------------------------------------
#  Standard logging
#----------------------------------------------------------------------
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_auth);      destination(authlog); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_local7);    destination(bootlog); };
#log{ source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);
#      source(s_udp);      filter(f_debug);     destination(debug); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_local1);    destination(explan); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_local5);    destination(routers); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_messages);  destination(messages); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_authpriv);  destination(secure); }; 
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_spooler);   destination(spooler); };
log { source(s_dgram);
      source(s_internal);
      source(s_kernel);
      source(s_tcp);      filter(f_syslog);    destination(syslog); };
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);
#      source(s_udp);                       destination(syslog); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_user);      destination(user); };

#----------------------------------------------------------------------
#  Special catch all destination sorting by host
#----------------------------------------------------------------------
log { source(s_dgram);
      source(s_internal);
      source(s_kernel);
      source(s_tcp);                           destination(hosts); };

#----------------------------------------------------------------------
#  Send to a loghost
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);                           destination(loghost); };

#----------------------------------------------------------------------
#  Mail subsystem logging
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);
#      source(s_udp);     filter(f_mail);      destination(mail); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_mail); 
                          filter(f_err);       destination(mailerr); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_mail); 
                          filter(f_info);      destination(mailinfo); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_mail); 
                          filter(f_notice);    destination(mailinfo); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_mail); 
                          filter(f_warn);      destination(mailwarn); };

#----------------------------------------------------------------------
#  INN subsystem logging
#----------------------------------------------------------------------
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_news); 
                          filter(f_crit);      destination(newscrit); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_news); 
                          filter(f_err);       destination(newserr); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_news); 
                          filter(f_notice);    destination(newsnotice); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_news); 
                          filter(f_warn);      destination(newswarn); };

#----------------------------------------------------------------------
#  Cron subsystem logging
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_tcp);
#      source(s_udp);     filter(f_cron);      destination(crondebug); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_cron); 
                          filter(f_err);       destination(cronerr); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_cron); 
                          filter(f_info);      destination(croninfo); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_cron); 
                          filter(f_warn);      destination(cronwarn); };

#----------------------------------------------------------------------
#  LPR subsystem logging
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_tcp);
#      source(s_udp);     filter(f_lpr);       destination(lpr); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_lpr); 
                          filter(f_err);       destination(lprerr); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_lpr); 
                          filter(f_info);      destination(lprinfo); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_lpr);   
                          filter(f_warn);      destination(lprwarn); }; 

#----------------------------------------------------------------------
#  Kernel subsystem logging
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);
#      source(s_udp);     filter(f_kern);      destination(kern); };
log { source(s_dgram);
      source(s_internal);
      source(s_kernel);
      source(s_tcp);      filter(f_kern); 
                          filter(f_err);       destination(kernerr); }; 
log { source(s_dgram);
      source(s_internal);
      source(s_kernel);
      source(s_tcp);      filter(f_kern); 
                          filter(f_info);      destination(kerninfo); }; 
log { source(s_dgram);
      source(s_internal);
      source(s_kernel);
      source(s_tcp);      filter(f_kern);
                          filter(f_warn);      destination(kernwarn); }; 

#----------------------------------------------------------------------
#  Daemon subsystem logging
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_tcp);
#      source(s_udp);     filter(f_daemon);    destination(daemon); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_daemon);    
                          filter(f_err);       destination(daemonerr); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_daemon); 
                          filter(f_info);      destination(daemoninfo); };
log { source(s_dgram);
      source(s_internal);
      source(s_tcp);      filter(f_daemon);    
                          filter(f_warn);      destination(daemonwarn); };

#----------------------------------------------------------------------
#  Console logging
#----------------------------------------------------------------------
#  16-Mar-03 - REP - Removed logging to the console for performance
#                    reasons.  Since we are not really going to be 
#                    looking at the console all the time, why log there
#                    anyway.
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);      filter(f_syslog);    destination(console); };

#----------------------------------------------------------------------
#  Logging to a database
#----------------------------------------------------------------------
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);      filter(f_db);        destination(database); };
#log { source(s_dgram);
#      source(s_internal);
#      source(s_kernel);
#      source(s_tcp);      filter(f_discard);   destination(db_discard); };

Syslog-ng FAQ (from campin.net)

Filed under: syslog-ng — lancevermilion @ 1:34 pm

Syslog-ng FAQ

This FAQ covers old syslog-ng versions, 1.X and 2.0. If you are looking for information about recent syslog-ng versions, please check the official FAQ now hosted by Balabit at: http://www.balabit.com/wiki/syslog-ng-faq.Every mailing list should have a list of frequently asked questions, and the answers usually given to those questions. Here’s one for syslog-ng.

Disclaimer: Use this information at your own risk, I cannot be held responsible for how you use this information and any consequences that may result. However, every effort has been made to ensure the technical accuracy of this document.

Most questions are taken from actual posts to the syslog-ng mailing list. Truly horrible grammar and spelling were cleaned up, but most questions are identical to the original post.

Any new entries should be submitted to the new FAQ at Balabit, not here.

Important Syslog-ng and syslog links

syslog/syslog-ng Graphical Interfaces

Contents

Geting started

  • Syslog-ng 2.x requires glib and eventlog which reside in /usr, thus cannot be used on systems where /usr is mounted during boot time, aftersyslog-ng starts.The latest snapshots (and future releases) of syslog-ng 2.x links to GLib and EventLog statically, thus it will not require those libraries to be present at boot time.

    The eventlog library was written by the syslog-ng author, and can be downloaded from the Balabit site.

    You can download glib from here, linked to from the main GTK site here.

  • I miss this/that very important feature from syslog-ng 2.x while it was present in syslog-ng 1.6.From Bazsi:
    syslog-ng 2.x is a complete reimplementation of syslog-ng and even though I plan to make it upward compatible with syslog-ng 1.6, I might have forgotten something. So please post to the mailing list either if you find missing or incompatible features.

     

  • What’s with this libol stuff, and which one do I need?libol is a library written by the author of syslog-ng, Balazs Scheidler, which is used in syslog-ng 1.6.x and below. A built copy of libol needs to be present on a system when these versions of syslog-ng are built.

    libol does *not* need to be installed, however. A built copy can be left in a typical build directory like /usr/src, and given as a parameter to syslog-ng’s configure script. Run ‘./configure –help’ in the syslog-ng source directory for more information.Information about versions of libol and which branch of syslog-ng they correspond to, see here.

  • When attempting to use the match(“.asx”) in my filter it is returning anything with “asx”. I only need to return those lines with the period before the asx, hence a file extension. For some reason syslog-ng seems to ignore my specification of the . before the asx. I have tried searching with ..asx and \.asx and /.asx but doesn’t seem to work no matter what I do. Any suggestions?match() expects an extended regular expression. _and_ syslog-ng performs \ based escaping for strings to make it possible to include ” within the string. Therefore you need to specify:
    match("\\.asx") 

    …it will match a single dot followed by the string asxSee this post for more explanation on this issue: https://lists.balabit.hu/pipermail/syslog-ng/2003-December/005616.html

  • I run Linux and see that I can choose one of two types of UNIX socket for my main syslog source. Which one is correct?You should choose unix-stream over unix-dgram for the same reasons you’d choose TCP (stream) over UDP (datagram): increased reliability, ordered delivery and client-side notification of failure.

    Along the same lines, you should choose unix-dgram over unix-stream for the same reasons you’d choose UDP (datagram) over TCP (stream): less possibility of denial of service by opening many connections (local-only vulnerability though), less overhead, don’t care to know if the remote end actually received the message.

    Most of us setting up syslog-ng tend to desire the benefits of unix-stream, and Bazsi recommends its use. See his commentary in the officialreference manual.

  • Hi, can someone please help me to get the compile right.OS: RHEL ES 3 update 4

    The ./configure went well no errrors.
    But the make did not go so well:

    Error message(s) :

    >> snip gcc -g -O2 -Wall -I/usr/local/include/libol -D_GNU_SOURCE -o
    syslog-ng main o sources.o center.o filters.o destinations.o log.o cfgfile.o 
    cfg-grammar.o cfg lex.o affile.o afsocket.o afunix.o afinet.o afinter.o afuser.o 
    afstreams.o afpr gram.o afremctrl.o nscache.o utils.o syslog-names.o macros.o -lnsl 
    -lresolv 
    sr/local/lib/libol.a -lnsl -Wl,-Bstatic 
    -Wl,-Bdynamic cfg-lex.o(.text+0x45f): In function `yylex': 
    /root/rpm/syslog-ng/syslog-ng-1.6.8/src/cfg-lex.c:1123: undefined 
    reference to 
    yywrap' 
    cfg-lex.o(.text+0xb33): In function `input': 
    /root/rpm/syslog-ng/syslog-ng-1.6.8/src/cfg-lex.c:1450: undefined 
    reference to 
    yywrap' 
    collect2: ld returned 1 exit status 
    make[3]: *** [syslog-ng] Error 1 
    make[3]: Leaving directory `/root/rpm/syslog-ng/syslog-ng-1.6.8/src' 
    make[2]: *** [all-recursive] Error 1 
    make[2]: Leaving directory `/root/rpm/syslog-ng/syslog-ng-1.6.8/src' 
    make[1]: *** [all] Error 2 
    make[1]: Leaving directory `/root/rpm/syslog-ng/syslog-ng-1.6.8/src' 
    make: *** [all-recursive] Error 1 

    Can someone plz explain what went wrong?As previously noted by another poster this is a problem with the flex version on your system. If you use a flex with a higher version than 2.5.4 you’re out of luck, unless you patch the sources. The reason for this is that the people developing flex had the “interesting” idea of changing the way the lexer parses the language file.

    The fix is to downgrade your flex or to patch cfg-lex.l with a %option field disabling yywarp. From the top of my head it should read:

     %option noyywrap

    Or you define the options as follows (I think):

     %{
    prototype functionname(parameters);
     %}

    Just my 2 cents since this issue turns up on almost every OSS project out there and people hit this very problem all the time and I thus want to enlarge the information and have google find it once and forever ;) .Best regards,
    Roberto Nibali, ratz

    ps.: Let’s hope I got it right

    Note from Rob Munsch:
    It should be noted that one will get an *identical* error, specifically the ‘undefined reference to yywrap,’ if flex is *not installed at all.
    …those who hit this error should first perhaps ensure that they have flex/m4 installed before they start screwing with the cfg-lex.l code. I’ve been happily compiling various things on this (new) system for a while now, and to those of us newer at this than some others, we tend to fall into the trap of thinking that if most things compile, then we aren’t missing any vital steps of a compile process, like say preprocessors….

  • Thanks for the patch, I just patched it, and I can’t recompile libol. I did a make clean after I patched it, then tried:
     $ make
     Making all in utils
     make[1]: Entering directory `/home/src/libol-0.2.17/utils'
     make[1]: Nothing to be done for `all'.
     make[1]: Leaving directory `/home/src/libol-0.2.17/utils'
     Making all in src
     make[1]: Entering directory `/home/src/libol-0.2.17/src'
     /usr/src/libol-0.2.17/utils/make_class io.c.xt
     /bin/sh: /usr/src/libol-0.2.17/utils/make_class: No such file or directory
     make[1]: *** [io.c.x] Error 127
     make[1]: Leaving directory `/home/src/libol-0.2.17/src'
     make: *** [all-recursive] Error 1

    I’m not sure what this means. Should I try the patch again? I just did a 

    $ patch -ORIG_FILE -DIFF_FILE

    This comes from a missing scheme interpreter, touch io.c.x or install scsh.

    For much more on libol and scheme in syslog-ng, read this post by Bazsi

  • If I replace my syslog daemon with syslog-ng, what side effects can it have?Glad you asked, the most common side effect is being happy with a superior syslog daemon.

    Another common result is that system logfiles grow to huge sizes. This isn’t syslog-ng’s fault, but a side effect of syslog-ng logging to different logfiles than your old syslog daemon. Change your log rotation program’s config files to rotate the new log names/locations or change syslog-ng’s config file to make it log to the same files as your old syslog daemon.

Running it

  • I’m new to syslog-ng. Is there a way for syslog-ng and syslogd to co-exist? Our servers are managed by another group, and they don’t supportsyslog-ng. Can you pipe all syslogd messages to syslog-ng?Yes, syslog-ng can accept messages from stock syslogd using the udp() source.
  • I want a catch-all log destination and can’t seem to find out how in the documentation or examples.Jay Guerette helped out with:

    Filters are optional. A catchall should appear in your .conf before all othe entries; and can look something like:

    destination catchall {
            file(/var/log/catchall);
    };
    log {
            source(syslog);
            destination(catchall);
    };
  • I want to replace syslogd *and* klogd on my Linux box with syslog-ng.Use a source line like this in your conf file to read kernel messages, too.
    source src { file("/proc/kmsg"); unix-stream("/dev/log"); internal(); };

    Notes:

    1. do not run klogd and syslog-ng fetching local kernel messages at the same time. It may cause syslog-ng to block which makes all logging local daemons unusable.
    2. Current selinux policy distributed for RHEL4 supports syslog-ng by a boolean named “use_syslogng”. But on the not working host (using “pipe”), following happens:avc: denied { write } for pid=2190 comm="syslog-ng" name="kmsg" dev=proc ino=-268435446 scontext=root:system_r:syslogd_t tcontext=system_u:object_r:proc_kmsg_t tclass=filePlease don’t use “pipe” at all for /proc/kmsg.

      Thanks to Peter Bieringer for contributing this information

    3. If you find yourself getting lots of kernel messages to the console after replacing klogd with syslog-ng: set the kernel’s console log level. This is done by klogd but not syslog-ng automatically.Something like “dmesg -n4″ should help.
  • I have been trying syslog-ng and extremely happy with the power of using it. I have one question, when using the program option under destination drivers, my PERL script gets launched when I start syslog-ng, but executes once and then dies. I am using this script to page any time I see an log entry, but it only runs the first time it runs.You can read log messages on your stdin, so instead of fetching a single line and exiting keep reading your input like this:
    #!/usr/bin/perl
    while (<>) {
            # send to pager
    }
  • Is it possible to create sockets with syslog-ng similar to how you can do so with syslogd? The reason for this being that I’m running some applications chrooted, and need to open a /dev/log socket that is in that chroot-jail.Of course you can. Just add a source:
    source local { unix-stream("/dev/log"); internal(); };
    source jail1 { unix-stream("/jail/dns/dev/log"); };
    source jail2 { unix-stream("/jail/www/dev/log"); };

    You can do this by using a single source:

    source local {
    	unix-stream("/dev/log");
    	internal();
    	unix-stream("/jail/dns/dev/log");
    };

    Note that postfix appears to need a log socket in it’s chroot jail, or it’s logging will stop when you reload syslog-ng:

    source postfix { unix-stream("/var/spool/postfix/dev/log" keep-alive(yes)); };
  • Directories with names like “Error”, “SCSI”, “”, are showing up in the directory that holds the syslogs for the different hosts that we monitor.Has anyone seen these random directories? Any suggestions on how to deal with them?From the description it’s apparent that logs are being stored in your filesystem with a macro similar to this:

     

    destination std { file( "/var/log/$HOST/$FACILITY"); };

    …so that you have directories created with the value of $HOST. This is bad. The host entry in syslog messages is often set to a bad value, especially with messages originating from the UNIX kernel, like SCSI error messages.

    The best fix for this is to *never* create files or directories based on unfiltered input from the network (You’d do well to remember that in general). Set the option keep_hostname to (no), and syslog-ng will always replace the hostname field (possibly using DNS, so make sure your local caching DNS is setup correctly).

    Here’s the way to keep the hostnames in the log files but ALSO log safely to the filesystem:

    options {
            long_hostnames(off);
    	keep_hostname(yes);
    	use_dns(no);
    };
    
    source src {
    	internal();
    	unix-stream("/dev/log" keep-alive(yes));
    };
    
    # set it up
    destination logip {
    	file("/archive/logs/HOSTS/$HOST_FROM/$FACILITY/$YEAR$MONTH/$FACILITY$YEAR$MONTH$DAY"
    	owner(syslog-ng) group(syslog-ng) perm(0600) dir_perm(0700) create_dirs(yes)
    	template("$DATE $FULLHOST $PROGRAM $TAG [$FACILITY.$LEVEL] $MESSAGE\n")  );
    };
    
    # log it
    log {
    	source(src);
    	destination(logip);
    };

    Since you don’t use DNS, your $HOST_FROM directory name will be an IP, but since you keep_hostnames(yes) you’ll still have the hostname AS SENT inside the actual logfile. How’s that for a good setup? I quite like it! ;) If still you really want to use hostnames for directory or file names, read on:
    When still using hostnames (from the DNS) for directory names, the author of this FAQ didn’t have garbled $HOST macros go away until he modified all clients to run syslog-ng and transfer over TCP. Both steps might not be required, syslog-ng over UDP might be sufficient, though there’s little reason *not* to use TCP. Modern TCP/IP stacks are tuned to handle lots of web connections, so even a central host for hundreds of machines can use TCP without issues from the use of TCP alone. There will be I/O problems with trying to commit that many hosts’ logs to disk much sooner under most circumstances.

  • DNS: I want to use fully qualified domain names in my logs, I have many different hosts named ns1, or www, and don’t want the logs mixed up. Also, I have a question concerning the use_dns option. Is this a global option only, or is there some way to change this per source or destination?First of all, make sure that you have a reliable DNS cache nearby. Nearby may be on the same host or network segment, or even at your upstream provider. Just make sure that you can reach it reliably. syslog-ng blocks on DNS lookups, so you can stop all logging if you start getting DNS timeouts.

    Internal syslog-ng DNS caching has recently been worked on, and reportedly works well. This appears to be a good alternative to running a local caching DNS server (‘dns_cache(yes);’).

    The use_dns option can be specified on a per-source basis (so can the keep_hostname option).

    See also: the section on hostname options directly below.

  • What is with all the “hostname” options?When syslog-ng receives a message it tries to rewrite the hostname it contains unless keep_hostname is true. If the hostname is to be rewritten (e.g. keep_hostname is false), it checks whether chain_hostnames (or long_hostname which is an alias for chain_hostnames) is true. If chain_hostnames is true, the name of the host syslog-ng received the message from is appended to the hostname, otherwise it’s replaced.

    So if you have a message which has hostname “server”, and which resolves to “server2″, the following happens:

    keep_hostname(yes) keep_hostname(no)
    chain_hostname(yes) server server/server2
    chain_hostname(no) server server2

    I hope this makes things clear.

  • I have this config file:
     filter f_local0 { facility(local0); }; filter f_local1 { facility(local1); }; destination df_local1 { file("/mnt/log/$R_YEAR-$R_MONTH-$R_DAY/$SOURCEIP/local.log" template("$FULLDATE <> $PROGRAM <> $MSGONLY\n") template_escape(no)); }; log { source(s_tcp); source(s_internal); source(s_udp); source(s_unix); filter(f_local0); filter(f_local1); destination(df_local1); }; 

    When an event arrives at the system by facility local0 or local1, this one is registered in the file never. Can be some bug of syslog-ng or failure in config?If I understand you correctly then the problem is that you’re using two filters which exclude each other however using more filters they are logically AND-ed. If you want to catch messages from local0 and local1 use a filter like this:

    filter f_local01 { facility(local0) or facility(local1); }; 

     

  • I archive my logs like this:
    file("/var/log/HOSTS/$HOST/$YEAR/$MONTH/$DAY/$FACILITY_$HOST_$YEAR_$MONTH_$DAY" owner(root) group(root) perm(0600) dir_perm(0700) create_dirs(yes) ); 

    …and over time my archive takes up lots of space. When will syslog-ng implement compression so that I can compress them automatically?You don’t need syslog-ng to compress them, install bzip2 and run a nightly cronjob like this:

     /usr/bin/find /var/log/HOSTS ! -name "*bz2" -type f ! -path "*`/bin/date +%Y/%m/%d`*" -exec /usr/bin/bzip2 {} \; 

    This might need some explaining: find all non-bzipped files that aren’t from today (syslog-ng might still write to them) and compress them with bzip2. This was tested on Debian GNU/Linux with GNU find version 4.1.7.Submitted by Michael King:
    We started with the compression script you have, but changed it to use gzip compression. (Less file space efficient, but more time efficient. B2zip takes approximately 20 minutes to decompress vs 2 or 3 minutes for Gzip), added a quick find and delete for empty directories and files modified more than 14 days old.

    # Current policy is:
    # Find all non-Archived files that aren't from today, and archive them
    # Archive Logs are deleted after 14 days
    #
    #Changes.   Change -mtime +14 to the number of days to keep
    
    # Archive old logs
    /usr/bin/find /var/log/HOSTS ! -name "*.gz" -type f ! -path "*`/bin/date +%Y/%m/%d`*" -exec /usr/bin/gzip {} \;
    
    # Delete old archives
    find /var/log/HOSTS/ -daystart -mtime +14 -type f -exec rm {} \;
    
    # Delete empty directories
    find /var/log/HOSTS/ -depth -type d -empty -exec rmdir {} \;
  • My syslog-ng.conf has
    destination std { file( "/var/log/$HOST/$YEAR$MONTH/$FACILITY" create_dirs(yes)); };

    What happens is if /var/log/$HOST/$YEAR$MONTH does not exist, syslog-ng makes that dir, but it’s owner is root:other. I think because daemon’s effective user ID is root. I want to change dir’s owner. Is this possible?

    Yes, you can do this using the owner(), group() and perm() options.
    For example:

    destination d_file { file("/var/log/$HOST/log" create_dirs(yes)
     owner("log") group("log") perm(0600)); };
  • a) I have snmptrapd running so that any trap that it receives should be logged to local1. I have a filter taking anything received via local1 to a specific file:
    filter snmptrap {facility(local1);};
    destination snmptraps { file("/var/log/snmptraps";};

    Unfortunately a number of traps are getting cut off at a specific point, and the remainder of the trap ends up in syslog and not in the proper destination.syslog defaults to 1024 byte long messages, but this value is tunable in syslog-ng 1.5 where you can set it to a higher value.

    options { log_msg_size(8192); };

    b) Andreas Schulze points out: “We are running snmptrapd and syslog-ng 1.5.x under Solaris 8 and observed exactly the same problem.

    This doesn’t fix the problem for us. It seems that there is a problem in the syslog(3) implementation at least on Solaris. Maybe on Linux, too. This is important, because snmptrapd feeds its messages via syslog(3) to syslog-ng. So syslog-ng never gets the correct message, because its truncated in libc before syslog-ng receive it.

    Our solution was, to patch snmptrapd to log its messages via a local Unix DGRAM socket and use this socket as message source for syslog-ng. This fix the problem and works pretty fine and very stable for more than one year in our environment.

    Basically you’re screwed on Solaris, but hopefully other implementations aren’t as brain-dead.

  • It seems I have syslog clients with unsynchronized clocks and I have files that were created with the time macros, and their date is wrong. What I want is the files to be created with the time/date they are received.It’s an option. use_time_recvd() boolean.
    options { use_time_recvd(true); };
  • What conf settings can I use for my syslog-ng.conf file so that messages are written to disk the instant they are received?Add sync(0) to your config file.
    options { sync(0); };
  • I want to run syslog-ng chrooted and as a non-root user.Use syslog-ng 1.5.x’s own -C and -u flags when starting it.
  • I want to rewrite my logs into a specific format.Syslog-ng 1.5.3 added support for user definable log file formats. Here’s how to use it:
    destination my_file {
            file("/var/log/messages" template("$ISODATE $TAG $FULLHOST $MESSAGE"));
    };

    For an explanation of available macros read this post.

  • I have been experiencing a problem with a syslog-ng (1.4.11) server seemingly only allowing 10 connections to a tcp() source. A quick tour of the code found the offending code at line 341 in afinet.c:
    self->super.max_connections = 10;

    It’s limited because otherwise it’d be easy to mount a DoS attack against the logger host. You can change this limit run-time without changing the source with the max-connection option to tcp():

    source src { tcp(max-connections(100)); };

  • Can output from programs started by syslog-ng be captured and logged by syslog-ng?It’s on the todo list. As long as it is not implemented, you might try to redirect the program’s output to a named pipe like this:
    destination d_swatch { program("swatch 2> /var/run/swatch.err"); };
     source s_swatch { pipe("/var/run/swatch.err"); };

Getting fancy

  • The whole point of setting up a loghost was to report on the logs. How can syslog-ng help?Syslog-ng is not about reporting on messages. Syslog-ng is a “sink” for syslog messages. Once syslog-ng commits them to some sort of storage (filesystem, database, line printer, etc), it is up to you to scan them.

    That being said, Nate Campi’s “newlogcheck” page shows how he filters all messages through swatch in real time, and also uses syslog-ng’s “match” option to alert on certain message strings.
    The fact that this stuff works is a result of syslog-ng’s flexibility, not because it was written to be all things to all people. Syslog-ng is a quality daemon because it tries to stay good at one thing and one thing only: being a syslog server.

    Look at the links part of this page for the link to Nate’s newlogcheck page, and for the link to the Log Analysis page. You’ll find plenty of information on log parsing there.

  • I want to input my logs into a database in real time – why can’t I do it?You can, there’s just nothing built into syslog-ng which knows about databases. You simply need to take advantage of syslog-ng’s ability to pipe to a program. Follow the links in the links part of this page to read up on how other have done it.
  • How much log volume can syslog-ng handle?The limits to throughput in syslog-ng are similar to that of most other network applications, where network and disk I/O are the limiting factors.

    Here’s a report from Kevin Kadow about throughput on his busy loghost:

    Our log volume has been growing slowly over time, I recently checked my primary
    logger and noticed that the raw log volume for this past Monday (the 24-hour
    period from midnight to midnight) was 10,982,118,488 bytes, or 10.22GiB.
    
    Peak hourly volume was 913MiB. Peak one-second volume in that hour was 2,626
    messages totaling 440KiB, no duplicates.
    
    System specs:
    OpenBSD on a Dell 2650 (single 2.8Ghz P4) running syslog-ng 1.6.X.
    Logs are written to a Dell PERC 3/Di SCSI RAID-0, as a 2-drive stripe.
  • I’m using syslog-ng over redirected ports inside an SSH channel and whenever I HUP syslog-ng, the SSH channel closessyslog-ng closes TCP connections when a SIGHUP is received, but you can change this behaviour with the keep-alive option.
    destination remote_tcp { tcp("loghost" port(1514) keep-alive(yes)); };
    source tcp_listen { tcp(ip("10.0.0.1") port(5140) keep-alive(yes)); };
  • I’ve successfully set up syslog-ng to tunnel through stunnel. I’m having one problem though, all messages come through with a hostname of “localhost”, presumably since stunnel is coming from localhost on the syslog server….Keep the hostname as sent by the remote syslog daemon:
    options { keep_hostname(yes); };
  • I am trying to setup a central log host and am having trouble getting events registered on the central server. It looks like the remote host does connect to the central host but nothing shows in a log anywhere for it.Here is the central loghost config file:
    options {
            long_hostnames(off);
            sync(0);
            stats(43200);
            dns_cache(yes);
            use_fqdn(no);
            keep_hostname(yes);
            use_dns(yes);
    };
    
    source gateway {
            unix-stream("/dev/log");
            internal();
            udp(ip(0.0.0.0) port(514));
    };
    
    source tcpgateway {
            unix-stream("/dev/log");
            internal();
            tcp(ip(0.0.0.0) port(514) max_connections(1000));
    };
    
    destination hosts {
            file("/var/log/syslogs/$HOST/$FACILITY"
            owner(root) group(root) perm(0600) dir_perm(0700)
    create_dirs(yes));
    };
    
    log {
            source(gateway); destination(hosts);
    };
    
    log {
            source(tcpgateway); destination(hosts);
    };

    Don’t duplicate sources in your source{} (unix-stream(“/dev/log”); and internal); directives. syslog-ng is going to open /dev/log once for each time you list it, same for any TCP/IP ports, files, etc. List them once and use the source{} in additional log{} statements.

    You’ll want something more like:

    options {
            long_hostnames(off);
            sync(0);
            stats(43200);
            dns_cache(yes);
            use_fqdn(no);
            keep_hostname(yes);
            use_dns(yes);
    };
    
    source local {
            unix-stream("/dev/log");
            internal();
    };
    
    source gateway {
            udp(ip(0.0.0.0) port(514));
    
    source tcpgateway {
            tcp(ip(0.0.0.0) port(514) max_connections(1000));
    };
    
    destination hosts {
            file("/var/log/syslogs/$HOST/$FACILITY"
            owner(root) group(root) perm(0600) dir_perm(0700)
    create_dirs(yes));
    };
    
    log {
            source(gateway);
    	destination(hosts);
    };
    
    log {
            source(local);
    	destination(hosts);
    };
    
    log {
            source(tcpgateway);
    	destination(hosts);
    };
  • I am having problems with syslog-ng on an SeLinux aware machine. The kernel will not allow me to open up /proc/kmsg for kernel messages. The error message I get looks like:Oct 24 14:03:06 shadowlance kernel: audit(1130178038.432:2): avc: denied { read } for pid=2690 comm="syslog-ng" name="kmsg" dev=proc ino=-268435446 scontext=user_u:system_r:syslogd_t tcontext=system_u:object_r:proc_kmsg_t tclass=fileWhat can I do?

    These errors are a sign that either the Selinux policies are not syslog-ng aware or have not been enabled yet. Make sure you have the latest policies for your distribution and then use getsebool to see if usesyslogng is turned on

    # getsebool use_syslogng
    use_syslogng --> inactive
    # setsebool -P use_syslogng=1

    Restart the syslog and the problem should be fixed. If not, you will need to contact your distributions selinux team for more guidance.

  • I am trying to send all important messages from a bunch of other machines to a central syslog-ng server via tcp. I chose tcp partly, because the same log server gets all kinds of less important stuff via udp from other machines, which can easily be distinguished that way, but partially also because I expected tcp to be more reliable. Unfortunately, this does not seem to be the case: When the connection has died for any reason, the client will only discover this when it is trying to send the next message to the server. Only then it starts to wait until “time_reopen” is over and establishes a new connection – the message that originally triggered this and whatever comes in between is lost.This has been discussed quite a bit lately on the mailing list. A single line is written to a TCP socket without an error when the connection has been lost. It is not until the next message is written that the error condition is reported by the kernel.

Performance Tips

  • What are some tips for optimizing a really busy loghost running syslog-ng?In no particular order:
  • If you use DNS, at least keep a caching DNS server running on the local host and make use of it – or better yet don’t use DNS.
  • You can post-process logs on an analysis host later on and resolve hostnames at that time if you need to. On your loghost your main concern is keeping up with the incoming log stream – the last thing you want to do is make the recording of events rely on an external lookup. syslog-ng blocks on DNS lookups (as noted elsewhere in this FAQ), so you’ll slow down/stop ALL destinations with slow/failed DNS lookups.
      • Don’t log to the console or a tty, under heavy load they won’t be able to read the messages as fast as syslog-ng sends them, slowing down syslog-ng too much.
      • Don’t use regular expressions in your filters. Instead of:
        filter f_xntp_filter_no_regexp {
        	# original line: "xntpd[1567]: time error -1159.777379 is way too large (set clock manually);
        	program("xntpd") and
        	match("time error .* is way too large .* set clock manually");
        };
         

        Use this instead:

        filter f_xntp_filter_no_regexp {
        	# original line: "xntpd[1567]: time error -1159.777379 is way too large (set clock manually);
        	program("xntpd") and
        	match("time error") and match("is way too large") and match("set clock manually");
        
        };

        Under heavy, heavy logging load you’ll see CPU usage like this when using regexps:


        …vs CPU usage like this when not using regexps:

        Note that the results at the bottom of the graphs show that the test with heavy regexp use caused huge delays, almost 25% lost messages (the test only sent 5,000 messages!) and hammered the CPU. The test without regexps was one where I sent 50,000 messages, and it hardly used any CPU, didn’t drop any messages and all the messages made it across in under a second (not all 50,000, each individual message made it in under a second). Note that the “Pace” of 500/sec is simply how fast they were injected to the syslog system using the syslog() system call (from perl using Unix::Syslog).

        NOTE: when not using regexps and matching on different pieces of the message, you might match messages that you don’t mean to. There is only a small risk of this, and it is much better than running out of CPU resources on your log server under most circumstances. It is your call to make.

        Please don’t ask me for the scripts that generated these graphs, I wrote them for work and it probably wouldn’t be possible to ever release them. I hope to one day write some like it in my free time and release them…but that may be a pipe dream. :(

 

    • Be sure to increase your maximum connections to a TCP source, as described here
    • There’s a good chance you’ll want to set per-destination buffers. The official reference manual covers the subject here.The idea here is to make sure that when you have multiple log destinations that might block somewhat “normally” (TCP and FIFO come to mind) that they don’t interfere with each other’s buffering. If you have a TCP connection maxed out in its buffer because of an extended network problem, but have only a temporary problem feeding logs into a FIFO, you can avoid losing any data in the FIFO (assuming your buffer size is large enough to handle the backup) if you set up separate buffers.

      If our TCP destination connection drops because the regional syslog server is down for a syslog-ng upgrade or kernel patch, we want events bound for the TCP destination to be held in the buffer and sent across once the connection is re-established. If our bucket is already filled because of FIFO problems to a local process, we can’t buffer a single message for the entire duration of the TCP connection outage. Ouch.

      The problem with implementing per-destination buffers is that the log_fifo_size option was added to the TCP destinations in the 1.6.6 version. You need to upgrade to syslog-ng 1.6.6 or later (I suggest the latest stable version).

  • You probably need to increase the size of your UDP receive buffers on your loghost. See this doc about UDP buffer sizing and how to modify it.
  • If you have many clients, you might well run out of fd’s (the default limit for maximum file descriptors is around 1000), thus syslog-ng might not be able to open files. The workaround then would be to increase the maximum file handles (ulimit -n) before starting syslog-ng, the best is to put this in the init script.

October 13, 2011

HTML Input type hidden has extra space when POST/GET methods sends

Filed under: html — lancevermilion @ 3:45 pm

Apparently some browsers interrupt INPUT types of hidden differently.
For example if you have a newlines, tables, or spaces between the space between is considered.

<INPUT NAME="A1" TYPE="hidden"/> <INPUT NAME="A2" TYPE="hidden"/>

The solution is to use a DIV with a STYLE of display:none and change your TYPE to “text”.
<DIV STYLE="display;none;"><INPUT NAME="A1" TYPE="text"/> <INPUT NAME="A2" TYPE="text"/></DIV>

This is a nice discovery, but very frustrating.

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS Drivers

Filed under: microsoft, perl, sql, ODBC — lancevermilion @ 2:30 pm

Perl to Microsoft SQL Server 2008 Standard via ODBC using FreeTDS

Today I had a need to have a perl script (running on CentOS 5.4) connect to a MSSQL Server 2008 Std server so i had to do a little research and playing around. The solution to getting everything working with “Free/OpenSource” stuff was not difficult if you don’t follow everything that everyone publishes.

I have provided the steps I used below to get everything working.

Pre-requisites

Before you start you need to ensure you know how to satisfy all the RPMs in the pre-requisites.
Location from where you can get the RPMs is listed in parentheses ().

  1. RPMs Needed/used:
    perl - 5.8.8-10 (Yum)
    perl-DBI - 1.52-1 (Yum)
    freetds - 0.64-11 (Yum)
    unixODBC-devel - 2.2.11.7.1 (Yum)
    unixODBC - 2.2.11.7.1 (Yum)
    perl-DBD-ODBC - 1.23-1 (http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm)
    

Steps to get MSSQL,ODBC and Perl working as one happy product.

  1. How did I get ODBC setup?
    Install freetds, unixODBC, unixODBC-devel via Yum
    sudo yum install freetds unixODBC unixODBC-devel
    

    Then install the perl, perl-DBI if you don’t have them installed already.

    sudo yum install perl perl-DBI
    

    If you haven’t downloaded the perl-DBD-ODBC module you can do so using wget/curl/etc. I prefer wget.
    Then install the perl, perl-DBI if you don’t have them installed already.

    wget http://pkgs.repoforge.org/perl-DBD-ODBC/perl-DBD-ODBC-1.23-1.el5.rf.i386.rpm
    

    Then install the perl-DBD-ODBC module. I am assuming the perl-DBD-ODBC is in the same directory you are working in. If not you will need to provide a complete path to the rpm.

    sudo rpm -ivh perl-DBD-ODBC
    
  2. DBD::ODBC We used DBD::ODBC. You can use similar methods as above to determine if DBD::ODBC is installed and to see what version you have:To check you have the DBD::ODBC module installed:
    perl -e 'use DBD::ODBC;'
    

    If you have not got DBD::ODBC installed you can build it via CPAN or download the RPM and go from there. I just downloaded it, see the RPM list at the top of the article.To show the DBD::ODBC version:

    perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
    

    e.g.

    perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
    1.23
    

    To show all drivers DBI knows about and their versions:

    perl -MDBI -e 'DBI->installed_versions;'
    

    e.g.

    perl -MDBI -e 'DBI->installed_versions;'
      Perl            : 5.008008    (i386-linux-thread-multi)
      OS              : linux       (2.6.9-42.0.3.elsmp)
      DBI             : 1.52
      DBD::Sponge     : 11.10
      DBD::Proxy      : install_driver(Proxy) failed: Can't locate RPC/PlClient.pm in @INC
      DBD::ODBC       : 1.23
      DBD::File       : 0.35
      DBD::ExampleP   : 11.12
      DBD::DBM        : 0.03
    
  3. What ODBC drivers have I got?
    You can find out what ODBC drivers are installed under unixODBC with:
    odbcinst -q -d
    

    e.g.

    odbcinst -q -d
    [PostgreSQL]
    

    For unixODBC, drivers are installed in the odbcinst.ini file. You can find out which odbcinst.ini file unixODBC is using with:

    odbcinst -j
    

    e.g.

    odbcinst -j
    unixODBC 2.2.11
    DRIVERS............: /etc/odbcinst.ini
    SYSTEM DATA SOURCES: /etc/odbc.ini
    
  4. Here, /etc/odbcinst.ini defines the ODBC drivers. Configure unixODBC to use FreeTDS as the Driver for the MSSQL connection.
    First we need to locate the driver file.
    sudo updatedb
    locate libtdso
    

    e.g.

    sudo updatedb
    locate libtdso
    /usr/lib/libtdsodbc.so.0
    /usr/lib/libtdsodbc.so.0.0.0
    

    Now we know where the source files are so lets create a driver template to use for driver installation.
    You can use vi if you want, but to make sure people can copy/paste as much as possible I have provided an echo command below.
    Adjust the path and possibly filename for “/usr/lib/libtdsodbc.so.0″.
    Note: Only use the driver that has a single 0 if there is more than one returned like my example.

    echo "[FreeTDS]
    Driver = /usr/lib/libtdsodbc.so.0" >> tds.driver.template
    

    Now lets install the driver from the template. This will install the driver so anyone can use this driver.

    sudo odbcinst -i -d -f tds.driver.template
    

    e.g.

    sudo odbcinst -i -d -f tds.driver.template
    odbcinst: Driver installed. Usage count increased to 1.
        Target directory is /etc
    

    If you check to see what drivers are available to ODBC you will now see “FreeTDS” has been added.

    odbcinst -q -d
    

    e.g.

    odbcinst -q -d
    [PostgreSQL]
    [FreeTDS]
    
  5. Configure ODBC with MSSQL parameters.
    To do this we need to modify the /etc/odbc.ini file.
    Your odbc.ini file could be in a different location. use odbcinst -j to location your ini file.
    You need to adjust the values accordingly for the echo command to work for your install.
    You will want to be root to do the file modification. I jump to root by way of sudo su -.
    Note:You will need a SQL user account setup in MSSQL to allow you to connect remotely with the way I am showing you.
    sudo su -
    echo "[MSSQL]
    Driver = FreeTDS
    Address = IPADDRESSOFMSSQL
    Port = 1433
    TDS Version = 8.0
    Database = MYDATABASENAME" >> /etc/odbc.ini
    

    e.g.

    sudo su -
    echo "[MSSQL]
    Driver = FreeTDS
    Address = 192.168.10.1
    Port = 1433
    TDS Version = 8.0
    Database = Northwind" >> /etc/odbc.ini
    
  6. Test the ODBCconnect to your MSSQL database.
    isql -v -s MSSQL SQLUSERNAME SQLUSERPASSWORD
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    SQL>
    
  7. If the step above was successful then lets try using a Perl script.
    Note: Below I specify DSN=MSSQL, that should match the name in [ ] in your odbc.ini file.
    You will also want to change SQLUSERNAME and SQLUSERNAMEPASSWORD to your username/password needed to connect to MSSQL using SQL username/password.
    This is NOT windows authentication.
    #!/usr/bin/perl
    use DBI;
    use strict;
    use DBI;
    my @dsns = DBI->data_sources('ODBC');
    foreach my $d (@dsns)
    {
      print "$d\n";
    }
      my $dbh = DBI-> connect('dbi:ODBC:DSN=MSSQL;UID=SQLUSERNAME;PWD=SQLUSERPASSWORD') or die "CONNECT ERROR! :: $DBI::err $DBI::errstr $DBI::state $!\n";
    if ($dbh)
    {
      print "There is a connection\n";
      my $sql = q/SELECT * FROM dbo.users/;
      my $sth = $dbh->prepare($sql);
      $sth->execute();
      my @row;
      while (@row = $sth->fetchrow_array) {  # retrieve one row at a time
        print join(", ", @row), "\n";
      }
      $dbh->disconnect;
    }
    

External Reference links used to get everything working.

http://www.unixodbc.org/doc/FreeTDS.html
http://www.martin-evans.me.uk/node/20
http://www.easysoft.com/developer/languages/perl/dbi_dbd_odbc.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html
http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html
http://www.easysoft.com/developer/languages/perl/sql_server_unix_tutorial.html
http://www.easysoft.com/developer/languages/perl/tutorial_data_web.html
http://www.easysoft.com/developer/languages/perl/dbi-debugging.html

October 3, 2011

REDCOM DCT Translation Tester using net.db (100% offline)

Filed under: perl, REDCOM — lancevermilion @ 2:23 pm

The other day I shared my script on how to parse Dial Code Tables (DCT) if took the time to convert them to CSV. Well after thinking about it this morning I made a quick change to my code to read in the net.db file (ascii copy of the database) and process routing that way.

Update: I found some coding errors, removed the utilization of external shell commands, and added strict usage.

Redcom version 4.0 R1P1

The Code:

#!/usr/bin/perl
# Author:  Lance Vermilion
# Purpose: Walk through Redcom Dial Code Tables and determine
#          what entry the digits would hit if it was ran
#          through a Redcom.
# Script:  dct.pl
# Date:    10/4/2011
# Rev:     0.4
# Syntax:  dct.pl
# Example: dct.pl dct0 4352348763
#################################################################

#################################################################
# Instructions:
# Downlaod the binary configuration to a ascii format on the
# Redcom.
# rsh /tmp> xld downl;over=yes;downl;exit;log
# Cat the net.db and copy/paste this in a local net.db file in the
# same folder as dct.pl
# cat /sys/net.db
# dct.pl will parse net.db and preform all the work needed just
# as if it was being processed through the Redcom.
#################################################################

#################################################################
# Not supported yet
# TYPE (other than dct, rte, int, stn, sup)
# SC
# PRE  (does support any variation of ac- ac+)
# POS  (does support any variation of ac- ac+)
# MARK (does support any variation of ac- ac+)
# Next (Only jumps to that dct)
# SB
# SST
# TID
#
# Pattern that uses a # in the pattern
#################################################################

use strict;
use Carp;
use Data::Dumper;

my $dctnumber = "$ARGV[0]";
my $dialeddigits = "$ARGV[1]";
my $file = "net.db";

# DCT to be used that is passed by commandline
my $hash_ref = {};
print "Reading \"$file\" file: ";
if ( -f "$file" )
{
  print "[OK]\n";
}
else
{
  print "[FAILED]\n";
  croak "File: \"$file\" does not exist. :: $!\n";
}

# File net.db exists slurp it to an array
open(FH, "$file" ) or die "Could not open $file\n";
my @netdbarr = ;

sub help()
{
  print "Syntax:  dct.pl  \n";
  print "Example: dct.pl dct0 4352348763\n";
  exit;
}

if ( scalar(@ARGV) ne 2 )
{
  print "You didn't provide enough variables\n"
  &help();
}

&buildDCThash();
&processDCThash($dctnumber,$dialeddigits,0,0,0);

# Sub-routine to print DCT
sub printdct ($$$$$$$$)
{
  my $printent  = shift;
  my $printpatt = shift;
  my $printsc   = shift;
  my $printtype = shift;
  my $printval  = shift;
  my $printpre  = shift;
  my $printpos  = shift;
  my $printmark = shift;
  my $printnext = shift;
  my $printsb = shift;
  my $printsst = shift;
  my $printtid = shift;

  print "ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID    \n";
format DCTREPORT =
@<<<< @<<<<<<<<<<<<<< @< @<<< @<< @<< @<< @<<< @<<< @<<<<<  @<<<<<<  @<< $printent $printpatt $printsc $printtype $printval $printpre $printpos $printmark $printnext $printsb $printsst $printtid .   $~ = 'DCTREPORT';   write; } # Sub-routine to build our hash sub buildDCThash() {   my $curdct = '';   my $curgrp = '';   my $currte = '';   for my $netdbline (@netdbarr)   {     if ( $netdbline =~ /^_dcta\[ (\d+) \] = (.*)/ )     {       $curdct = "dct$1";       my ($dctname, $dctqty, undef, undef, undef, undef, $dcttoneable, undef, $dctdialtimer) = split(/,/, $2);       $hash_ref->{'dct'}->{$curdct}->{'name'} = $dctname;
      $hash_ref->{'dct'}->{$curdct}->{'qty'} = $dctqty;
      $hash_ref->{'dct'}->{$curdct}->{'tonable'} = $dcttoneable;
      $hash_ref->{'dct'}->{$curdct}->{'dtimer'} = $dctdialtimer;
    }

    $netdbline =~ s/,252,/,,/g if $netdbline =~ /^_dctm/; #252 equals blank
    $netdbline =~ s/220/ac/g if $netdbline =~ /^_dctm/;   #220 equals ac
    if ( $netdbline =~ /^_dctm\[ (\d+) \] = (.*)/ )
    {
      my $ent=$1;
      my ($patt, $type, $val, $next, $sc, $pre, $pos, $mark, $sb, undef, $sst, undef, $tid) = split(/,/, $2);
      $patt = '-default-' if $ent eq 0;  # net.db stored a blank entry for entry 0 aka -default- so we hard set the value.
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'origpatt'} = $patt;
      $patt = 'NULL_PATTERN' if $patt eq '';
      $patt =~ s/n/[2-9]/g;
      $patt =~ s/x/[0-9]/g;
      $patt =~ s/q/[0-4]/g;
      $patt =~ s/Q/[5-9]/g;
      $patt =~ s/X/[2-8]/g;
      $patt =~ s/\~/\\d+/g;
      $patt =~ s/\*/\\*/g;
      $patt =~ s/\#/\\#/g;
      $patt =~ s/\?/[0-9]/g;
      $patt =~ s/w//g;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'patt'} = $patt;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'sc'}   = $sc;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'type'} = $type;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'val'}  = $val;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'pre'}  = $pre;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'pos'}  = $pos;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'mark'} = $mark;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'next'} = $next;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'sb'} = $sb;
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'sst'} = $sst;
      $tid =~ s/65535//;   # similar to VAL, except that the final value 65535 would be reserved for the "blank" case)
      $hash_ref->{'dct'}->{$curdct}->{'entries'}->{$ent}->{'tid'} = $tid;
    }

    #
    # INCLUDE ROUTE AND GROUP INFORMATION
    #
    if ( $netdbline =~ /^_grpatt\[ (\d+), 1 \] = (.*)/ )
    {
      $curgrp = $1;
      my ( $type1,undef,undef,$grpname,undef,undef,undef ) = split(/,/, $2);
      #print "$group $type1 $grpname\n";
      $hash_ref->{'grp'}->{$curgrp}->{'group'} = $curgrp;
      $hash_ref->{'grp'}->{$curgrp}->{'type1'} = $type1;
      $hash_ref->{'grp'}->{$curgrp}->{'grpname'} = $grpname;
    }

    if ( $netdbline =~ /^_grpatt\[ (\d+), 3 \] = (.*)/ )
    {
      if ( $curgrp eq $1 )
     {
        my ( $type3,$grptype,undef,undef,undef,undef,undef,undef,undef) = split(/,/, $2);
        #print "$group,$type3,$grptype\n";
        $hash_ref->{'grp'}->{$curgrp}->{'type3'} = $type3;
        $hash_ref->{'grp'}->{$curgrp}->{'grptype'} = $grptype;
      }
    }

    my $currte = '';
    if ( $netdbline =~ /^route\[ (\d+), 1 \] = (.*)/ )
    {
      $currte = "rte$1";
      my ( $prefix_digits,undef,undef,undef,$rtename,$alt1,$alt2,$alt3,undef,undef,undef,undef,$catid,undef,undef,undef,undef,undef,undef,undef,$comp,undef,undef,undef,undef,$groupnum,undef,undef,undef,undef,undef,undef,undef ) = split(/,/, $2);
      $comp =~ s/0x0/off/g;
      $comp =~ s/0x40/on/g;
      #print "$route,$prefix_digits,$rtename,$alt1,$alt2,$alt3,$comp,$groupnum\n";
      $hash_ref->{'rte'}->{$currte}->{'prefix_digits'} = $prefix_digits;
      $hash_ref->{'rte'}->{$currte}->{'rtename'} = $rtename;
      $hash_ref->{'rte'}->{$currte}->{'alt1'} = $alt1;
      $hash_ref->{'rte'}->{$currte}->{'alt2'} = $alt2;
      $hash_ref->{'rte'}->{$currte}->{'alt3'} = $alt3;
      $hash_ref->{'rte'}->{$currte}->{'comp'} = $comp;
      $hash_ref->{'rte'}->{$currte}->{'groupnum'} = $groupnum;
    }
  }
}

# Sub-routine to process the hash
sub processDCThash($$$$$)
{
# TYPE (DCT type and val (ie. dct6)
# Digits (digits to be processed)
# Pre (digits to delete from begining of the digit string)
# Pos (digits to delete from end of the digit string)
# Mark (pass all digits, but read from n place in the digit string)

  # TYPE from DCT
  my $TYPE = shift;

  # Since we wrap back on our self to walk DCTs we need to make sure we exit on types of int,etc
  if ( $TYPE =~ /^(int|int\d+)/ )
  {
    print "User will be sent to an intercept message\n";
    print "#" x 73 . "\n";
    exit;
  }
  elsif ( $TYPE =~ /^(rte|rte\d+)/ )
  {
    my $tempTYPE = $TYPE;
    $tempTYPE =~ s/rte//g;
    print "Digits will be sent to:\n";
    print "  Primary Route: Route $tempTYPE $hash_ref->{'rte'}->{$TYPE}->{'rtename'}\n";
    print "    Group $hash_ref->{'rte'}->{$TYPE}->{'groupnum'} $hash_ref->{'grp'}->{$hash_ref->{'rte'}->{$TYPE}->{'groupnum'}}->{'grpname'} Type: $hash_ref->{'grp'}->{$hash_ref->{'rte'}->{$TYPE}->{'groupnum'}}->{'type1'},$hash_ref->{'grp'}->{$hash_ref->{'rte'}->{$TYPE}->{'groupnum'}}->{'type3'}\n";
    print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$TYPE}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$TYPE}->{'prefix_digits'} ne '';
    if ( $hash_ref->{'rte'}->{$TYPE}->{'alt1'} ne 0 )
    {
      my $tmpalt1rte = "rte" . $hash_ref->{'rte'}->{$TYPE}->{'alt1'};
      my $tmpalt1grp = $hash_ref->{'rte'}->{$tmpalt1rte}->{'groupnum'};
      print "  Alternate Route #1: Route $hash_ref->{'rte'}->{$TYPE}->{'alt1'} $hash_ref->{'rte'}->{$tmpalt1rte}->{'rtename'}\n";
      print "    Group $tmpalt1grp $hash_ref->{'grp'}->{$tmpalt1grp}->{'grpname'} Type: $hash_ref->{'grp'}->{$tmpalt1grp}->{'type1'},$hash_ref->{'grp'}->{$tmpalt1grp}->{'type3'}\n";
      print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$tmpalt1rte}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$tmpalt1rte}->{'prefix_digits'} ne '';
    }
    if ( $hash_ref->{'rte'}->{$TYPE}->{'alt2'} ne 0 )
    {
      my $tmpalt2rte = "rte" . $hash_ref->{'rte'}->{$TYPE}->{'alt2'};
      my $tmpalt2grp = $hash_ref->{'rte'}->{$tmpalt2rte}->{'groupnum'};
      print "  Alternate Route #2: Route $hash_ref->{'rte'}->{$TYPE}->{'alt2'} $hash_ref->{'rte'}->{$tmpalt2rte}->{'rtename'}\n";
      print "    Group $tmpalt2grp $hash_ref->{'grp'}->{$tmpalt2grp}->{'grpname'} Type: $hash_ref->{'grp'}->{$tmpalt2grp}->{'type1'},$hash_ref->{'grp'}->{$tmpalt2grp}->{'type3'}\n";
      print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$tmpalt2rte}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$tmpalt2rte}->{'prefix_digits'} ne '';
    }
    if ( $hash_ref->{'rte'}->{$TYPE}->{'alt3'} ne 0 )
    {
      my $tmpalt3rte = "rte" . $hash_ref->{'rte'}->{$TYPE}->{'alt3'};
      my $tmpalt3grp = $hash_ref->{'rte'}->{$tmpalt3rte}->{'groupnum'};
      print "  Alternate Route #3: Route $hash_ref->{'rte'}->{$TYPE}->{'alt3'} $hash_ref->{'rte'}->{$tmpalt3rte}->{'rtename'}\n";
      print "    Group $tmpalt3grp $hash_ref->{'grp'}->{$tmpalt3grp}->{'grpname'} Type: $hash_ref->{'grp'}->{$tmpalt3grp}->{'type1'},$hash_ref->{'grp'}->{$tmpalt3grp}->{'type3'}\n";
      print "    Inserted Prefix Digits: $hash_ref->{'rte'}->{$tmpalt3rte}->{'prefix_digits'}\n" if $hash_ref->{'rte'}->{$tmpalt3rte}->{'prefix_digits'} ne '';
    }
    print "#" x 73 . "\n";
    exit;
  }
  elsif ( $TYPE =~ /^(stn|stn\d+)/ )
  {
    print "Digits will be sent to a local station.\n";
    print "#" x 73 . "\n";
    exit;
  }

  # Account for the fact the user may not provide dct6 and may provide something else.
  if ( $TYPE !~ /^dct\d+$/ )
  {
    print "Error: The Dial Code Table (DCT) you want to start at must be in this format.\n";
    print "       dct\n";
    print "       Example: dct0\n";
    my $loop = 0;
    until ( $TYPE =~ /^dct\d+$/ )
    {
      print "Re-Enter your DCT: ";
      chomp($TYPE = );
      $loop++;
      if ($loop >= 3)
      {
        print "Exiting!!! You failed to enter the DCT correctly 3 times.\n";
        exit;
      }
    }
  }

  # numbers dialed
  my $digits = shift;
  chomp($digits);

  # Adjust digits based on pre, pos, and mark
  my $pre = shift;
  my $pos = shift;
  my $mark = shift;
  $digits = substr($digits, $pre)  if ( $pre > 0 );
  $digits = substr($digits, -$pos) if ( $pos > 0 );
  $digits = substr($digits, $mark)  if ( $mark > 0 );

  my $curdct = '';

  # Create a pattern hash keyed by pattern with entry and value
  my $patt_ref = {};
  for my $key ( sort keys %{$hash_ref->{'dct'}->{$TYPE}->{'entries'}} )
  {
    $patt_ref->{$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$key}->{'origpatt'}} = $key;
  }

  print "\n" . "#" x 73 . "\n";
  print "Processing " . length($digits) . " Digit Dialed Number \"$digits\"\n";

  my $charcnt = 0;

  # Print DCT in really short form
  my $tmpTYPE = $TYPE;
  $tmpTYPE =~ s/dct//g;
  print "DCT Number = $tmpTYPE\n";
  print "DCT Name = $hash_ref->{'dct'}->{$TYPE}->{'name'}\n";
  print "DCT Entry Quantity = $hash_ref->{'dct'}->{$TYPE}->{'qty'}\n";
  print "DCT Toneable = $hash_ref->{'dct'}->{$TYPE}->{'tonable'}\n";
  print "DCT Dial Timer = " . $hash_ref->{'dct'}->{$TYPE}->{'dtimer'}/100 . " seconds\n";
  print "=" x 73 . "\n";
  print "ENTRY PATTERN        PATTERN (as seen by Perl)\n";
  for my $key ( sort { $a  $b } keys %{$hash_ref->{'dct'}->{$TYPE}->{'entries'}} )
  {
    printf ("%-5s %-14s %s\n", $key,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$key}->{'origpatt'},($hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$key}->{'patt'}));
  }
  print "=" x 73 . "\n";

  # Check for an exact match first
  for my $patt_orig (sort keys %$patt_ref)
  {
    my $dctentry = $patt_ref->{$patt_orig};
    my $patt = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'patt'};
    if ( $dctentry eq 0 )
    {
      next;
    }
    if ( $digits =~ /^$patt$/ )
    {
      print " * Found exact match!\n";
      &printdct($dctentry,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'origpatt'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sc'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'mark'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sb'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sst'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'tid'});
      print "#" x 73 . "\n";
      if ( $hash_ref->{'dct'}->{$dctentry}->{'type'} eq 'sup' )
      {
        print "User will be sent to a DCT equal to the Next field with Supervision\n";
        print "#" x 73 . "\n";
        $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'} = 'dct';
        $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'} = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'}
      }
      &processDCThash("$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'}$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'}",$digits,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},length($patt));
    }
    else
    {
      if ( substr($digits, 0, length($hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'origpatt'})) =~ /^$patt$/ )
      {
        print " * Found DbyD exact match!\n";
        &printdct($dctentry,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'origpatt'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sc'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'mark'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sb'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'sst'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'tid'});
        print "#" x 73 . "\n";
        if ( $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'} eq 'sup' )
        {
          print "User will be sent to a DCT equal to the Next field with Supervision\n";
          print "#" x 73 . "\n";
          $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'} = 'dct';
          $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'} = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'next'}
        }
        &processDCThash("$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'type'}$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'val'}",$digits,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{$dctentry}->{'pos'},length($patt));
      }
    }
  }

  print " * No match using Default.\n";
  &printdct(0,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'origpatt'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'sc'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'type'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'val'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pos'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'mark'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'next'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'sb'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'sst'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'tid'});
  print "#" x 73 . "\n";
  if ( $hash_ref->{'dct'}->{'0'}->{'type'} eq 'sup' )
  {
    print "User will be sent to a DCT equal to the Next field with Supervision\n";
    print "#" x 73 . "\n";
    $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'type'} = 'dct';
    $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'val'} = $hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'next'}
  }
  &processDCThash("$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'type'}$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'val'}",$digits,$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pre'},$hash_ref->{'dct'}->{$TYPE}->{'entries'}->{'0'}->{'pos'},0);
}

# make sure we exit incase we don't make it into the subroutines
exit;

Sample output: Traffic routes to a local station.

Reading "net.db" file: [OK]

#########################################################################
Processing 10 Digit Dialed Number "6235951206"
DCT Number = 0
DCT Name = "Loop Originate"
DCT Entry Quantity = 10
DCT Toneable = dial
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     *              \*
2     #              \#
3     911            911
4     zxxxx          z[0-9][0-9][0-9][0-9]
5     1nxxnxxxxxx    1[2-9][0-9][0-9][2-9][0-9][0-9][0-9][0-9][0-9][0-9]
6     011~w          011\d+
7     011~#          011\d+\#
8     1800nxxxxxx    1800[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
9                    NULL_PATTERN
=========================================================================
 * No match using Default.
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
0     -default-       0  dct  6   0   0   0         0       0
#########################################################################

#########################################################################
Processing 10 Digit Dialed Number "6235951206"
DCT Number = 6
DCT Name = "NPA Check"
DCT Entry Quantity = 10
DCT Toneable = sil
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     623            623
2     480            480
3     xxx            [0-9][0-9][0-9]
4                    NULL_PATTERN
5                    NULL_PATTERN
6                    NULL_PATTERN
7                    NULL_PATTERN
=========================================================================
 * Found DbyD exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
1     623             0  dct  7   0   0   ac        0       0
#########################################################################

#########################################################################
Processing 7 Digit Dialed Number "5951206"
DCT Number = 7
DCT Name = "Switch Code Check"
DCT Entry Quantity = 7
DCT Toneable = sil
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     959            959
2     595            432
3     738            738
4     525            525
5     nxxxxxx        [2-9][0-9][0-9][0-9][0-9][0-9][0-9]
=========================================================================
 * Found DbyD exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
2     595             0  dct  8   0   0   ac        0       0
#########################################################################

#########################################################################
Processing 4 Digit Dialed Number "1206"
DCT Number = 8
DCT Name = "Local Numbers"
DCT Entry Quantity = 20
DCT Toneable = sil
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     1155           1155
2     11xx           11[0-9][0-9]
3     12xx           12[0-9][0-9]
4     25xx           25[0-9][0-9]
5     26xx           26[0-9][0-9]
6     93xx           93[0-9][0-9]
7     94xx           94[0-9][0-9]
8     95xx           95[0-9][0-9]
9     xxxx           [0-9][0-9][0-9][0-9]
=========================================================================
 * Found exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
3     12xx            0  stn  0   0   0   ac        0       0
#########################################################################
Digits will be sent to a local station.
#########################################################################

Sample Output: Traffic goes out a Trunk to PSTN

Reading "net.db" file: [OK]

#########################################################################
Processing 11 Digit Dialed Number "16134321209"
DCT Number = 0
DCT Name = "Loop Originate"
DCT Entry Quantity = 15
DCT Toneable = dial
DCT Dial Timer = 7 seconds
=========================================================================
ENTRY PATTERN        PATTERN (as seen by Perl)
0     -default-      -default-
1     *              \*
2     #              \#
3     911            911
4     zxxxx          z[0-9][0-9][0-9][0-9]
5     1480nxxxxxx    1480[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
6     1602nxxxxxx    1602[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
7     1623nxxxxxx    1623[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
8     1nxxnxxxxxx    1[2-9][0-9][0-9][2-9][0-9][0-9][0-9][0-9][0-9][0-9]
9     011~w          011\d+
10    011~#          011\d+\#
11    1800nxxxxxx    1800[2-9][0-9][0-9][0-9][0-9][0-9][0-9]
12                   NULL_PATTERN
=========================================================================
 * Found exact match!
ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID
8     1nxxnxxxxxx     0  rte  1   0   0   0         0       0
#########################################################################
Digits will be sent to:
  Primary Route: Route 1 "Primary PSTN"
    Group 8 "PRI Span 1/0" Type: trk,trk
  Alternate Route #1: Route 7 "Secondary PSTN"
    Group 6 "PRI Span 1/1" Type: trk,trk
#########################################################################

September 30, 2011

REDCOM DCT Translation Tester (100% offline)

Filed under: perl, REDCOM — lancevermilion @ 5:45 pm

I took some time today to write a fairly simple Perl script to walking through REDCOM Dial Code Tables(DCT) and preform the translations in a 100% offline mode. You will need to get a copy of each of your DCTs and make them a CSV format.

The script works for basic stuff, but it does not evaluate the SC, NEXT (except when used with TYPE=sup), SB/SNU, SST/NST, TID fields because I don’t see a need for them in my testing. The script does not handle using ac-x, ac+x, etc in the PRE, POS, or MARK fields. There is no support for patterns that contain # symbols (again no need for me to figure out why my script doesn’t like them).

I need to do some cleanup, more inline comments, add strict usage, and some other things. This is a functional script. If you do use it then please let me know what you think of it and also if you make changes to it that are for the positive please let me know of those since I shared this with you.

The Code:

#!/usr/bin/perl
# Author:  Lance Vermilion
# Purpose: Walk through Redcom Dial Code Tables and determine
#          what entry the digits would hit if it was ran
#          through a Redcom.
# Script:  dct.pl
# Date:    9/30/2011
# Rev:     0.1
# Syntax:  dct.pl <starting dct> <digits dialed>
# Example: dct.pl dct0 4352348763
#################################################################

#################################################################
# Instructions:
# Copy the all each entry (row) in the DCT to a CSV file named dct[n].csv.
# Do not copy the headers, footers, other data, etc.
# All possible dial code tables should be in one directory.
# Sample Output
# [root@localhost dct]$ ls -als
# total 60
# 8 drwxrwxr-x  2 root root 4096 Sep 30 16:48 .
# 8 drwx------ 33 root root 4096 Sep 30 16:48 ..
# 8 -rw-rw-r--  1 root root  260 Sep 30 15:38 dct0.csv
# 8 -rw-rw-r--  1 root root   92 Sep 30 15:11 dct6.csv
# 8 -rw-rw-r--  1 root root  202 Sep 30 15:16 dct7.csv
# 8 -rw-rw-r--  1 root root  285 Sep 30 15:14 dct8.csv
#12 -rw-rw-r--  1 root root 6410 Sep 30 16:44 dct.pl
#################################################################

#################################################################
# Not supported yet
# TYPE (other than dct, rte, int, stn, sup)
# SC
# PRE  (does support any variation of ac- ac+)
# POS  (does support any variation of ac- ac+)
# MARK (does support any variation of ac- ac+)
# Next (Only jumps to that dct)
# SB
# SST
# TID
#
# Pattern that uses a # in the pattern
#################################################################

my $inputfile = "$ARGV[0]";
my $dialeddigits = $ARGV[1];

sub help()
{
  print "Syntax:  dct.pl <starting dct> <digits dialed>\n";
  print "Example: dct.pl dct0 4352348763\n";
  exit;
}

if ( scalar(@ARGV) ne 2 )
{
  print "You didn't provide enough variables\n"
  &help();
}

&work($inputfile,$dialeddigits,0,0,0);

sub work($$$$$)
{

  # DCT file in csv format or type
  my $type = shift;

  # Since we wrap back on our self to walk DCTs we need to make sure we exit on types of int,etc
  if ( $type =~ /^(int|int\d+)/ )
  {
    print "User will be sent to an intercept message\n";
    exit;
  }
  elsif ( $type =~ /^(rte|rte?\d+)/ )
  {
    print "Digits will be sent to Route: $type\n";
    exit;
  }
  elsif ( $type =~ /^(stn|stn?\d+)/ )
  {
    print "Digits will be sent to a local station.\n";
    exit;
  }

  # numbers dialed
  my $digits = shift;
  chomp($digits);

  # Adjust digits based on pre, pos, and mark
  my $pre = shift;
  my $pos = shift;
  my $mark = shift;
  $digits = substr($digits, $pre)  if ( $pre > 0 );
  $digits = substr($digits, -$pos) if ( $pos > 0 );
  $digits = substr($digits, $mark)  if ( $mark > 0 );

  # DCT to be used that is passed by commandline
  my $dct = {};
  if ( -f "$type.csv" )
  {
    print "Reading DCT file: \"$type.csv\"\n";
  }
  else
  {
    print "File: \"$type.csv\" does not exist. :: $!\n";
    exit;
  }
  my @dctarr = `cat "$type.csv"`;

  for my $dctentry (@dctarr)
  {
    my ($ent, $patt, $sc, $type, $val, $pre, $pos, $mark, $next, $sb, $sst, $tid);
    ($ent, $patt, $sc, $type, $val, $pre, $pos, $mark, $next, $sb, $sst, $tid) = split(/,/, $dctentry);
    $dct->{$ent}->{'origpatt'} = $patt;
    $patt =~ s/n/[2-9]/g;
    $patt =~ s/x/[0-9]/g;
    $patt =~ s/q/[0-4]/g;
    $patt =~ s/Q/[5-9]/g;
    $patt =~ s/X/[2-8]/g;
    $patt =~ s/\~/\\d+/g;
    $patt =~ s/\*/\\*/g;
    $patt =~ s/\#/\\#/g;
    $patt =~ s/\?/[0-9]/g;
    $patt =~ s/w//g;
    $dct->{$ent}->{'patt'} = $patt;
    $dct->{$ent}->{'sc'}   = $sc;
    $dct->{$ent}->{'type'} = $type;
    $dct->{$ent}->{'val'}  = $val;
    $dct->{$ent}->{'pre'}  = $pre;
    $dct->{$ent}->{'pos'}  = $pos;
    $dct->{$ent}->{'mark'} = $mark;
    $dct->{$ent}->{'next'} = $next;
    $dct->{$ent}->{'sb'} = $sb;
    $dct->{$ent}->{'sst'} = $sst;
    $dct->{$ent}->{'tid'} = $tid;
  }

  # Create a pattern hash keyed by pattern with entry and value
  my $patt_ref = {};
  for my $key ( sort keys %$dct )
  {
    $patt_ref->{$dct->{$key}->{'origpatt'}} = $key;
  }

  print "Proccessing " . length($digits) . " Digit Dialed Number \"$digits\"\n";

  my $charcnt = 0;

  # Print DCT in really short form
  print "DCT = $type\n";
  print "ENTRY PATTERN         PATTERN (as seen by Perl)\n";
  for my $key ( sort { $a <=> $b } keys %$dct )
  {
    printf ("%-5s %-14s %s\n", $key,$dct->{$key}->{'origpatt'},($dct->{$key}->{'patt'}));
  }

  # Sub-routine to print DCT
  sub printdct ($$$$$$$$)
  {
    my $printent  = shift;
    my $printpatt = shift;
    my $printsc   = shift;
    my $printtype = shift;
    my $printval  = shift;
    my $printpre  = shift;
    my $printpos  = shift;
    my $printmark = shift;
    my $printnext = shift;
    my $printsb = shift;
    my $printsst = shift;
    my $printtid = shift;

    print "ENTRY PATTERN         SC TYPE VAL PRE POS MARK NEXT SB/SNU  SST/NST  TID    \n";
format DCTREPORT =
@<<<< @<<<<<<<<<<<<<< @< @<<< @<< @<< @<< @<<< @<<< @<<<<<  @<<<<<<  @<<
$printent $printpatt $printsc $printtype $printval $printpre $printpos $printmark $printnext $printsb $printsst $printtid
.
    $~ = 'DCTREPORT';
    write;
  }

  # Check for an exact match first
  for my $patt_orig (sort keys %$patt_ref)
  {
    my $dctentry = $patt_ref->{$patt_orig};
    my $patt = $dct->{$dctentry}->{'patt'};
    if ( $dctentry eq 0 )
    {
      next;
    }
    if ( $digits =~ /^$patt$/ )
    {
      print "\nFound exact match!\n";
      &printdct($dctentry,$dct->{$dctentry}->{'origpatt'},$dct->{$dctentry}->{'sc'},$dct->{$dctentry}->{'type'},$dct->{$dctentry}->{'val'},$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},$dct->{$dctentry}->{'mark'},$dct->{$dctentry}->{'next'},$dct->{$dctentry}->{'sb'},$dct->{$dctentry}->{'sst'},$dct->{$dctentry}->{'tid'});
      if ( $dct->{$dctentry}->{'type'} eq 'sup' )
      {
        print "User will be sent to a DCT equal to the Next field with Supervision\n";
        $dct->{$dctentry}->{'type'} = 'dct';
        $dct->{$dctentry}->{'val'} = $dct->{$dctentry}->{'next'}
      }
      &work("$dct->{$dctentry}->{'type'}$dct->{$dctentry}->{'val'}",$digits,$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},length($patt));
    }
    else
    {
      if ( substr($digits, 0, length($dct->{$dctentry}->{'origpatt'})) =~ /^$patt$/ )
      {
        print "\nFound DbyD exact match!\n";
        &printdct($dctentry,$dct->{$dctentry}->{'origpatt'},$dct->{$dctentry}->{'sc'},$dct->{$dctentry}->{'type'},$dct->{$dctentry}->{'val'},$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},$dct->{$dctentry}->{'mark'},$dct->{$dctentry}->{'next'},$dct->{$dctentry}->{'sb'},$dct->{$dctentry}->{'sst'},$dct->{$dctentry}->{'tid'});
        if ( $dct->{$dctentry}->{'type'} eq 'sup' )
        {
          print "User will be sent to a DCT equal to the Next field with Supervision\n";
          $dct->{$dctentry}->{'type'} = 'dct';
          $dct->{$dctentry}->{'val'} = $dct->{$dctentry}->{'next'}
        }
        &work("$dct->{$dctentry}->{'type'}$dct->{$dctentry}->{'val'}",$digits,$dct->{$dctentry}->{'pre'},$dct->{$dctentry}->{'pos'},length($patt));
      }
    }
  }

  print "\nNo match using Default.\n";
  &printdct(0,$dct->{'0'}->{'origpatt'},$dct->{'0'}->{'sc'},$dct->{'0'}->{'type'},$dct->{'0'}->{'val'},$dct->{'0'}->{'pre'},$dct->{'0'}->{'pos'},$dct->{'0'}->{'mark'},$dct->{'0'}->{'next'},$dct->{'0'}->{'sb'},$dct->{'0'}->{'sst'},$dct->{'0'}->{'tid'});
  if ( $dct->{'0'}->{'type'} eq 'sup' )
  {
    print "User will be sent to a DCT equal to the Next field with Supervision\n";
    $dct->{'0'}->{'type'} = 'dct';
    $dct->{'0'}->{'val'} = $dct->{$dctentry}->{'next'}
  }
  &work("$dct->{'0'}->{'type'}$dct->{'0'}->{'val'}",$digits,$dct->{'0'}->{'pre'},$dct->{'0'}->{'pos'},0);
}

# make sure we exit incase we don't make it into the subroutines
exit;

Here are my sample Dial Code Table files.

::::::::::::::
dct0.csv
::::::::::::::

0,-default-,0,dct,6,0,0,0,
1,*,0,dct,1,0,0,ac,
2,#,0,dct,2,0,0,ac,
3,?111,0,rte,1,0,0,0,
4,zxxxx,0,rte,3,0,0,0,
5,1nxxnxxxxxx,0,rte,1,0,0,0,
6,011~w,0,rte,1,0,0,0,
7,011~#,0,rte,1,0,0,0,
8,1800nxxxxxx,0,rte,1,0,0,0,
9,911,0,rte,1,0,0,0,
10,011~,0,rte,1,0,0,0,

::::::::::::::
dct6.csv
::::::::::::::

0,-default-,0,dct,7,0,0,0,
1,623,0,dct,7,0,0,ac,
2,480,0,dct,7,0,0,ac,
3,xxx,0,rte,1,0,0,0,

::::::::::::::
dct7.csv
::::::::::::::

0,-default-,0,int,0,0,ac,,,0,,
1,959,0,dct,8,0,0,ac,,,0,,
2,4x21207,0,dct,8,0,0,ac,,,0,,
3,738,0,dct,8,0,0,ac,,,0,,
4,525,0,dct,8,0,0,ac,,,0,,
5,4x21206,0,dct,8,0,0,ac,,,0,,
6,4x21,0,dct,8,0,0,ac,,,0,,

::::::::::::::
dct8.csv
::::::::::::::

0,-default-,0,int,,0,0,ac,,,0,,
1,1155,0,sup,,ac,0,ac,17,,0,,
2,11xx,0,stn,0,0,0,ac,,,0,,
3,12xx,0,stn,0,0,0,ac,,,0,,
4,25xx,0,stn,0,0,0,ac,,,0,,
5,26xx,0,stn,0,0,0,ac,,,0,,
6,93xx,0,stn,0,0,0,ac,,,0,,
7,94xx,0,stn,0,0,0,ac,,,0,,
8,95xx,0,stn,0,0,0,ac,,,0,,
9,xxxx,0,rte,1,0,0,0,,,0,,

::::::::::::::
dct17.csv
::::::::::::::

0,-default-,0,int,,0,0,ac,,,0,,
1,1155,0,sup,,ac,0,ac,17,,0,,
2,11xx,0,stn,0,0,0,ac,,,0,,
3,12xx,0,stn,0,0,0,ac,,,0,,
4,25xx,0,stn,0,0,0,ac,,,0,,
5,26xx,0,stn,0,0,0,ac,,,0,,
6,93xx,0,stn,0,0,0,ac,,,0,,
7,94xx,0,stn,0,0,0,ac,,,0,,
8,95xx,0,stn,0,0,0,ac,,,0,,
9,xxxx,0,rte,1,0,0,0,,,0,,

August 12, 2011

How to setup MSSQL Server 2008 R2 for High Availability using mirroring.

Filed under: database, microsoft, sql — lancevermilion @ 4:40 pm

Contents

Document Overview: 4

Assumptions. 4

Installing Microsoft MSQQL Server 2008 R2. 5

Background on Database Mirroring. 22

Database Mirroring Overview.. 22

Database mirroring is a simple strategy that offers the following benefits: 22

How Database Mirroring Works. 22

Transaction Safety and Operating Modes. 23

Operating Modes. 23

Transaction Safety. 23

Topology – Synchronous High-Safety Mode without Automatic Failover. 24

Role Switching. 24

Configuration of Database Mirroring. 25

Microsoft SQL Server Management Studio. 25

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 27

Restoring a Backup Copy of the Principal Server’s Database on the Mirror Server. 29

Using Microsoft SQL Server Management Studio. 29

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 33

Configuring Database Mirroring. 35

Using Microsoft SQL Server Management Studio. 35

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}. 46

Administering MSSQL. 48

Managing Logins and Jobs After Role Switching. 48

Mirroring States. 49

How to Manually Failover the Database between Principal and Mirror. 50

Using Microsoft SQL Server Management Studio. 50

Using Microsoft Transact-SQL (T-SQL). 52

How to: Force Service in a Database Mirroring Session. 53

Using Microsoft Transact-SQL (T-SQL). 54

References: 54

Document Overview:

Microsoft MSSQL Server 2008 R2 can be setup in a High Availability model in a few different ways. The simplest way to this with two server is to use database mirroring. This document will only cover how to setup database mirroring with manual failover in synchronous high-safety mode between two server running Microsoft MSSQL Server 2008 R2.

Assumptions

· A server is already installed with Microsoft Windows Server 2003 or 2007

· Microsoft Server 2008 R2 Software to perform the install.

· The two servers are in a workgroup and NOT a domain.

· The two servers are on the same IP network.

· Remote access has been setup for Administrator.

· Access to the administrator account.

Installing Microsoft MSQQL Server 2008 R2

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

1. Make sure you have Microsoft .NET Framework 3.5 installed before you proceed.

2. Create the Service Accounts that will be used for each MSSQL process.

a. Start->Control Panel->Administrative Tools->Computer Management->Local Users and Groups

b. Use the table below for an example of the user names that need to be created for each SQL Service Account and what check boxes should be checked. You can use different passwords for each or the same one. Make sure you uncheck “User must change password at next logon”.

c. Right click “Users” under “Computer Management (Local)”->”System Tools”->”Local Users and Groups” and left click “New User”.

d. Below is a table that provides some recommend SQL Service Account names and the groups which they should belong to. The assignment to each group will be done during installation. If you don’t do it at installation you can do it afterwards, but it is a bit more complicated.

Service Account Service Account Description Group Membership General Settings
sqlsa SQL Server Agent Service Account (mssql agent) · SQLServerSQLAgentUser$<servername>$<instancename>· Users · Password never expires· User cannot change password
sqlsde SQL Server Database Engine Service Account (mssqlserver) · SQLServerMSSQLUser$<servername>$<instancename>· Users · Password never expires· User cannot change password
sqlsas SQL Server Analysis Services Service Account · SQLServerMSASUser$<servername>$<instancename>· Users · Password never expires· User cannot change password
Sqlsrs SQL Server Reporting Services Service Account · SQLServerReportServerUser$<servername>$<instanceid>· Users · Password never expires· User cannot change password
Sqlffdl SQL Full-text Filter Daemon Launcher Service Account · SQLServerFDHostUser$<servername>$<instancename>· Users · Password never expires· User cannot change password

3. The group membership bit you will not be able to do at this stage since these groups get created when you install Microsoft MSSQL Server 2008. You can see the memberships that should exists after installation if you look at the table above.

Let’s begin the install now we have SQL Service Accounts.

1. Locate the “Setup” file and double click it.

2. It is recommended to run “System Configuration Checker” before going forward, but not required.

3. This is the output from the “System Configuration Checker”.

4. After you have verified you meet all the requirements for “System Configuration Checker” click “Installation” on the left. On the right side click “New installation and add features to an existing installation”.

5. The first thing that happens is the install does some checks.

6. Click “Install”.

7. Some more checks. Click “Next”.

8. Enter your “Product key” and click “Next”.

9. Check “I accept the license terms” and click “Next”.

10. Choose “SQL Server Feature Installation” and click “Next”.

11. Click “Select All” and then click “Next”.

12. Yet another set of checks. Click “Next”.

13. Select “Default instance” unless you want to specifically name your instance.

14. Disk Space check is preformed. If you have enough space click “Next”.

15. This is where we use the SQL Service Accounts we created earlier. Refer to the table at the beginning of the section to properly populate these account names.

16. Choose “Mixed Mode (SQL Server authentication and Windows authentication)”, click “Add Current User”, and then click “Next”.

17. Click “Add Current User”, and then click “Next”.

18. Make sure “Install the native mode default configuration” and click “Next”.

19. Click “Next”.

20. Yet again we have more checks done. Click “Next”.

21. Click “Install” to do begin the actual installation process.

22. At the end of “Installation Process” click “Next”.

23. If everything went good you should have a similar screen. Click “Close” to finish.

Background on Database Mirroring

Database Mirroring Overview

Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

Note
You cannot mirror the master, msdb, tempdb, or model databases.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

Database mirroring is a simple strategy that offers the following benefits:

  • Increases data protection.
  • Database mirroring provides complete or almost complete redundancy of the data, depending on whether the operating mode is high-safety or high-performance.
  • Increases availability of a database.

In the event of a disaster, the database administrator has the alternative of forcing service (with possible data loss) to the standby copy of the database.

· Improves the availability of the production database during upgrades.

How Database Mirroring Works

The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. At any given time, one partner performs the principal role, and the other partner performs the mirror role. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions.

Transaction Safety and Operating Modes

Operating Modes

A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.

There are two mirroring operating modes.

One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.

The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded. This mode requires MSSQL Server 2008 Enterprise.

This document will only cover how to setup/administer the synchronous high-safety mode.

Transaction Safety

Whether an operating mode is asynchronous or synchronous depends on the transaction safety setting. If you exclusively use SQL Server Management Studio to configure database mirroring, transaction safety settings are configured automatically when you select the operation mode. Since SQL Server Management Studio is used in this document the end result will be synchronous.

If you use Transact-SQL to configure database mirroring, you must understand how to set transaction safety. Transaction safety is controlled by the SAFETY property of the ALTER DATABASE statement. On a database that is being mirrored, SAFETY is either FULL or OFF.

  • If the SAFETY option is set to FULL, database mirroring operation is synchronous, after the initial synchronizing phase.
  • If the SAFETY option is set to OFF, database mirroring operation is asynchronous. The session runs in high-performance mode.

Topology – Synchronous High-Safety Mode without Automatic Failover

All database mirroring sessions support only one principal server and one mirror server. The following figure shows the configuration of synchronous high-safety mode without automatic failover. The configuration consists of only the two partners. This configuration is shown in the following illustration.

When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).

Note
Establishing a new mirroring session requires that all involved server instances run the same version of SQL Server.

Role Switching

Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.

The following three forms of role switching exist.

· Automatic failover

This requires high-safety mode and the presence of the mirror server and a witness. The database must already be synchronized, and the witness must be connected to the mirror server.

The role of the witness is to verify whether a given partner server is up and functioning. The witness role is not discussed further in this document.

· Manual failover

This requires high-safety mode. The partners must be connected to each other, and the database must already be synchronized.

· Forced service (with possible data loss)

Under high-performance mode and high-safety mode without automatic failover, forcing service is possible if the principal server has failed and the mirror server is available.

In any role-switching scenario, as soon as the new principal database comes online, the client applications can recover quickly by reconnecting to the database.

Configuration of Database Mirroring

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

To configure mirroring you will need to have two databases that are identical. If you have not preformed a backup of your database you will want to follow these short steps.

Microsoft SQL Server Management Studio

1. Right click on your database in the “Object Explorer”, go to “Tasks”, and click on “Backup…”.

2. In the “Source” section select your “Database” in the dropdown.

In the “Backup set” section give the backup a name and provide a description if you like.

In the “Destination” section click “Add” and pick a location for your backup to be saved to.

Note
The backup file name needs to end in “.bak”.

Click “Ok” to start the backup.

3. Repeat Steps 1-2 but in the “Source” section change the “Backup Type” to “Transactional” and in the “Destination” section use the same file you used for the Full backup (the default backup setting is “appended to the backup file”).

4. Copy the backup file you just made or created before to the server that will start out as the “mirror server”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Create a database/transactional log backup on the Principal server.

Note
The default database recovery model is “Simple” but we want “Full”.

On the Principal server set the recovery type for each database you wish to mirror.

USE master;
GO
ALTER DATABASE <DatabaseName>
SET RECOVERY FULL;
GO

Example:

PRINCIPAL SERVER:

USE master;
GO
ALTER DATABASE TempDB
SET RECOVERY FULL;
GO

2. Backup the Database on the Principal Server.

BackupType:

Full = Full Database Backup

Log = Transaction Log Backup

To do a Full Database Backup:

BACKUP DATABASE <DatabaseName>
TO DISK = 'C:<DatabaseName-BackupType>.bak'
WITH FORMAT
GO

Example:

PRINCIPAL SERVER:

BACKUP DATABASE TempDB
TO DISK = 'C:TempDB-Full.bak'
WITH FORMAT
GO

Backup the log database too.

BACKUP DATABASE TempDBLog
TO DISK = 'C:TempDBLog-Full.bak'
WITH FORMAT
GO

To do a Transactional Log Backup:

BACKUP LOG TempDB
TO DISK = 'C:TempDB-Log.bak'
GO

Backup the log database too.

BACKUP LOG TempDBLog
TO DISK = 'C:TempDBLog-Log.bak'
GO

Copy the backup file over to the Server2 that we want to be the Mirror server.

Restoring a Backup Copy of the Principal Server’s Database on the Mirror Server.

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. Open “Microsoft SQL Server Management Studio” on the “mirror server”, right click “Databases”, and left click “Restore Database…”.

2. In the “Destination for restore” section populate the “To database:” box with the exact same database name that you preformed the backup on, on the principal server.

In the “Source for restore” section click the radio button “From device:” and click the button on the far right.

3. You should get a “Specify Backup” window. Click “Add”, locate the backup file you copied to this server, and click “Ok”. Click “Ok” one more time.

4. You should be back to the “Restore Database – <database name>” window. If you are you need to check the box in the restore column for the file you just finished added.

On the left hand window under “Select a page” click “Options”.

Make sure you select the transactional log and the full backup.

5. In the “Recovery state” section click the radio button for “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored. (RESTORE WITH NORECOVERY)” and click “Ok”.

6. You should now see your databases in a state of “Restoring”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Restore database/transactional log backups.

Assumption:
We are going to assume you have copied it over to Server2 and have it in the root of the C: drive.

BackupType:

Full = Full Database Backup

Log = Transaction Log Database Backup

2. To Restore a Full Database Backup:

RESTORE DATABASE <DatabaseName>
FROM DISK = 'C:<DatabaseName-Full>.bak'
WITH NORECOVERY
GO

Restore Full Database Backup Example:

RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Full.bak'
WITH NORECOVERY
GO

Restore the log database too.

RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Full.bak'
WITH NORECOVERY
GO

3. To Restore a Transactional Database Backup:

RESTORE LOG <DatabaseName>
FROM DISK = 'C:<DatabaseName-Log>.bak'
WITH FILE=1, NORECOVERY
GO

Restore Transactional Log Backup Example:

RESTORE DATABASE TempDB
FROM DISK = 'C:TempDB-Log.bak'
WITH FILE=1, NORECOVERY
GO

Restore the log database too.

RESTORE DATABASE TempDBLog
FROM DISK = 'C:TempDBLog-Log.bak'
WITH FILE=1, NORECOVERY
GO

Restore Transactional Log Backup Example:

Configuring Database Mirroring

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are going to be mirroring should be visible. Right click on the database you want to setup database mirroring. Go to “Tasks” and then to “Mirror…”.

2. Click on “Configure Security”.

3. Click “Next”.

4. Choose the radio button “No” because we will not be using a witness server. Click “Next”.

5. The dropdown “Principal server instance” is likely already selected to be the name of the server you are connected to and if not make sure it is.

I used the default port “5022” for the “Listener port” but you can use a different port. If you have a firewall enabled on your servers that will be the Principal/Mirror server you will want to disable it first to make sure everything comes up. After everything is working you can enable it again and troubleshoot your rules by allowing the proper ports (i.e. 5022,etc).

Check “Encrypt data sent through this endpoint”.

The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <mirror server hostname> <DatabaseToMirror> Mirroring (i.e. Server2 TempDB Mirroring).

Click “Next”.

6. The dropdown “Mirror server instance” needs to be set to the name of the mirror server. After setting it click connect.

I used the default port “5022” for the “Listener port” but you can use a different port. If you have a firewall enabled on your servers that will be the Principal/Mirror server you will want to disable it first to make sure everything comes up. After everything is working you can enable it again and troubleshoot your rules by allowing the proper ports (i.e. 5022,etc).

Check “Encrypt data sent through this endpoint”.

The “Endpoint name” is a friendly name so name is so it makes sense to you. I normally name it like so <PrincipalServerHostname> <DatabaseToMirror> Mirroring (i.e. Server1 TempDB Mirroring).

Click “Next”.

7. Leave all of these boxes blank and click “Next”.

8. Click “Finish”.

9. MSSQL will create a Data Mirroring object under “Server Objects”->”Endpoints”->”Database Mirroring” on the Principal and Mirror servers and make the necessary database changes. If those changes are done successfully you should have a screen similar to what I have below.

Click “Close”.

10. You will receive a warning similar to the one shown below. Click “Start Monitoring” to tell MSSQL to attempt to initiate/complete the mirroring connection.

11. Click “Yes”. MSSQL is just warning you that you are not using a Fully Qualified Domain Name (FQDN). To use a FQDN you need to connect to everything using a FQDN via “Microsoft SQL Server Management Studio”. This also means you need to have a domain setup. This guide is intended for setup outside a domain.

12. If mirror was setup successfully you wil see the status window now says “Synchronized: the databases are fully synchronized.” If you have the same status I have described you can click “Ok”. If you do not you will need to trouble shoot the issue.

13. You should now see the following status “Principal, Synchronized” on the “Principal Server”.

14. You should now see the following status “Mirror, Synchronized / Restoring…” on the “MIrror Server”.

Using Microsoft Transact-SQL (T-SQL) {UNVERIFIED}

1. Create endpoints on both servers

CREATE ENDPOINT EndPointName
STATE=STARTED AS TCP(LISTENER_PORT = PortNumber, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

Example:

PRINCIPAL SERVER:

CREATE ENDPOINT “Server2 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

MIRROR SERVER:

CREATE ENDPOINT “Server1 TempDB Mirroring”
STATE=STARTED AS TCP(LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING(ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)

2. Set partner and setup job on mirror server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://PrincipalServer:PortNumber'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
Note
The Database monitoring default interval is 1 minute.

Example:

MIRROR SERVER:

ALTER DATABASE TempDB SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server1:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO

3. Set partner and setup job on principal server

ALTER DATABASE DatabaseName SET PARTNER = N'TCP://MirrorServer:PortNumber'
ALTER DATABASE DatabaseName SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
Note
The Database monitoring default interval is 1 minute.

Example:

PRINCIPAL SERVER:

ALTER DATABASE TempDB SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO
ALTER DATABASE TempDBLog SET PARTNER = N'TCP://Server2:5022'
ALTER DATABASE TempDB SET SAFETY FULL
EXEC sys.sp_dbmmonitoraddmonitoring
GO

Administering MSSQL

Managing Logins and Jobs After Role Switching

Only content within the principal database is mirrored. Associated information in the master or msdb system databases cannot be mirrored. Such associated information includes jobs set up against the principal database and logins that are added to the principal server.

If such information is important to supporting role switching, the information should be duplicated at the mirrored site. If it is possible, after roles are switched it is best to programmatically reproduce the information on the new principal database. The most common issues are logins and jobs.

· Logins

For users to be able to access the database after a role switch, a login on the principal server that has permission to access the principal database, must also be defined on the mirror server. However, the master database cannot be mirrored. Therefore, if on the current principal server, you create a new login to this login for the principal database, you must do the same on the mirror.

The login of every user of the database must be manually defined on the mirror server and on the principal server. Otherwise, when the principal role switches and the former mirror server offers its database as the principal database, users whose logins are not defined on the former mirror cannot access the new principal. The users are orphaned.

· Jobs

Jobs, such as backup jobs, require special consideration. Typically, after a role switch, the database owner or system administrator must re-create the jobs for the new principal database.

When the former principal server is available, you should also delete the original jobs from the new mirror database. Jobs on the mirror database fail because it is in the RESTORING state and so it is unavailable.

Note
The partners might be configured differently, with different tape drive letters or such. The jobs for each partner must allow for any such differences.

Mirroring States

During a database mirroring session, the mirrored database is always in a specific state (the mirroring state). The state of the database reflects the communication status, data flow, and the difference in data between the partners. The database mirroring session adopts the same state as the principal database.

Throughout a database mirroring session, the server instances monitor each other. The partners use the mirroring state to monitor the database. With the exception of the PENDING_FAILOVER state, the principal and mirror database are always in the same state.

The possible mirroring states of the database are as follows:

Mirroring state Description
SYNCHRONIZING The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.
SYNCHRONIZED When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.
SUSPENDED The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session.SUSPENDED is a persistent state that survives partner shutdowns and startups.
PENDING_FAILOVER This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.
DISCONNECTED The partner has lost communication with the other partner.

How to Manually Failover the Database between Principal and Mirror

To manually failover the database between the principal and mirror server you will need do the following.

WARNING: I have removed the server name from all images so make sure you pay close attention to what server you are connected to.

Using Microsoft SQL Server Management Studio

1. If you are not already on the Principal server login now. Open “Microsoft SQL Server Management Studio” and click on the plus symbol next to “Databases”. The database(s) we are mirroring. Right click on the database you want to failover to the mirror server. Go to “Tasks” and then to “Mirror…”.

2. If you simply click the button “Failover” the failover will happen. If you are mirroring the log database for your database you will want to failover that database first (i.e. database = TempDB, database log = TempDBLog).

Using Microsoft Transact-SQL (T-SQL)

When the mirrored database is synchronized (that is, when the database is in the SYNCHRONIZED state), the database owner can initiate manual failover to the mirror server. Manual failover can be initiated only from the principal server.

On the principal server first we will set the database context to the master database and then initiate an immediate transition of the mirror server to the principal role.

Note
On the former principal, clients are disconnected from the database and in-flight transactions are rolled back (meaning lost).
USE master
GO
ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER
GO

Example:

PRINCIPAL SERVER:

Failover any <DatabaseName>Log databases first.

ALTER DATABASE TempDBLog SET PARTNER FAILOVER

Failover the main database after you have successfully failed over the Log database.

ALTER DATABASE TempDB SET PARTNER FAILOVER

How to: Force Service in a Database Mirroring Session

Caution
Forced service is strictly a disaster recovery method. Forcing service may involve some data loss. Therefore, force service only if you are willing to risk losing some data in order to restore service to the database immediately. If forcing service risks losing significant data, we recommend that you stop mirroring and manually resynchronize the databases.Forcing service suspends the session and starts a new recovery fork. The effect of forcing service is similar to removing mirroring and recovering the former principal database. However, forcing service facilitates resynchronizing the databases (with possible data loss) when mirroring resumes.

In high-performance mode and high-safety mode without automatic failover, if the principal server fails while the mirror server is available, the database owner can make the database available by forcing service to fail over (with possible data loss) to the mirror database. This option is available only under all the following conditions:

Or said differently

We are going to assume Server1 (Principal Server) is dead and now we want to change Server2 (currently in a mirror mode) into a “Principal Server”. To do this we will have to execute a SQL Query because I cannot find any documentation on how to do it within “Microsoft SQL Server Management Studio”.

Using Microsoft Transact-SQL (T-SQL)

To check the current failover state and Safety Settings:

SELECT SD.name, SD.database_id, SD.state_desc, SDM.mirroring_state_desc, SDM.mirroring_role_desc, SDM.mirroring_safety_level_desc, SD.recovery_model_desc
FROM sys.database_mirroring AS SDM
JOIN sys.databases AS SD
ON (SDM.database_id = SD.database_id)
WHERE mirroring_guid IS NOT NULL

Example output (Principal Server):

Example output (Mirror Server):

To force service in a database mirroring session

Connect to the mirror server and issue the following statement:

ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

The mirror server immediately transitions to principal server, and mirroring is suspended.

Example:

MIRROR SERVER:

ALTER DATABASE TempDB SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
ALTER DATABASE TempDBLog SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

References:

Technical Information:: http://technet.microsoft.com/en-us/library/ms187875.aspx

August 11, 2011

Poor man’s query logging (from http://www.mysqlperformanceblog.com)

Filed under: database, linux, mysql, perl — lancevermilion @ 5:51 pm

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with

--log

(or

--general-log

in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by

--log-slow-queries

parameter (MySQL 5.1 requires also

--slow-query-log

), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.

I know that many databases out there run with none of these and it would require a restart to get the logging in place and possibly another restart to disable it when no longer necessary (though actually slow log can be disabled by simply setting

long_query_time

MySQL variable vale high enough).

So what can be done when you really need to see the queries, but can’t afford any downtime?

If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s my quick one-liner which I will write in multiple lines:

garfield ~ # sudo /usr/sbin/tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

The output may contain little garbage, but it can be easily filtered out.

Obviously this method works only when applications communicate with MySQL through TCP sockets. When localhost (not to be confused with 127.0.0.1) is used as a MySQL host, this will not work since all traffic goes through a unix socket file.

It’s most definitely not a MySQL log replacement, but can be very useful if you need just a few minute dump.

Maciek

Here is sample output

tcpdump: listening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes
110811 17:47:32     325 Connect     bill@10.168.192.90 on
                    325 Query       select @@version_comment limit 1
110811 17:47:38     325 Query       SELECT DATABASE()
                    325 Init DB     accounting
                    325 Query       show databases
                    325 Query       show tables
                    325 Field List  records
110811 17:48:15     325 Quit

mysqlsniffer :: MySQL network protocol sniffer

Filed under: centos, database, linux, mysql, perl — lancevermilion @ 5:49 pm

You’ve got a production database server, and you can’t enable query logging… so how do you see the queries being executed against the database?

The answer: use a modified network sniffer to parse out the MySQL packets and decode them. You’ll have to do a little compiling, but it’ll be worth it. Note that this will not usually work for local connections, although you are welcome to try.

mysqlsniffer is a tcpdump clone specifically for dumping/sniffing/watching MySQL network protocol traffic over TCP/IP networks. mysqlsniffer is coded in C using the pcap library and works with MySQL version 4.0 and newer. mysqlsniffer is the only MySQL-specific network sniffer.

mk-query-digest also understands the MySQL protocol. It’s not a sniffer, though. It reads packet dumps from tcpdump like a slowlog. If you want to analyze queries from the wire (i.e. from network traffic), mk-query-digest is what you want.

For more information about the MySQL protocol read MySQL Internals ClientServer Protocol.

mysqlsniffer latest version Aug 18 2006 v1.2 (what’s new) via mysqlsniffer.tgz if you don’t want to follow the instructions below.

First, you need to install libpcap-dev (libpcap-devel for Redhat/Centos), which is the development library that allows an application to sniff network packets.

Ubuntu/Debian

sudo apt-get install libpcap-dev

Redhat/CentOS

sudo yum install libpcap-devel

Now let’s make a directory, download the source code and compile it

mkdir mysqlsniffer
cd mysqlsniffer
wget http://hackmysql.com/code/mysqlsniffer.tgz
tar xvfz mysqlsniffer.tgz
gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (somewhere in the path would be useful)

To run mysqlsniffer, you need to specify the network interface that MySQL is listening on. For me, it’s eth0.

sudo /path/to/mysqlsniffer eth0

Ah, now there we are… all sorts of query information, without having to restart MySQL.

Here are the full options for the command:

Usage: mysqlsniffer [OPTIONS] INTERFACE

OPTIONS:
–port N Listen for MySQL on port number N (default 3306)
–verbose Show extra packet information
–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
–net-hdrs Show major IP and TCP header values
–no-mysql-hdrs Do not show MySQL header (packet ID and length)
–state Show state
–v40 MySQL server is version 4.0
–dump Dump all packets in hex
–help Print this

mysqlsniffer Capture Example

MySQL network protocol traffic from the command “mysqladmin -u root -h 127.0.0.1 processlist”:

Note: The capture interface here was lo instead of eth0.

mysqlsniffer listening for MySQL on interface lo port 3306
server > 127.0.0.1.3051: ID 0 len 70 Handshake  (Caps: Get all column flags, Connect w/DB, Compression, )
127.0.0.1.3051 > server: ID 1 len 38 Handshake (new auth) <user root db (null) max pkt 1073741824> (Caps: Long password, Get all column flags, LOAD DATA LOCAL, 4.1 protocol, Transactions, 4.1 authentication, )
server > 127.0.0.1.3051: ID 2 len 7 OK  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 17 COM_QUERY: show processlist
server > 127.0.0.1.3051: ID 1 len 1 8 Fields
        ID 2 len 24 Field: ..Id
        ID 3 len 26 Field: ..User
        ID 4 len 26 Field: ..Host
        ID 5 len 24 Field: ..db
        ID 6 len 29 Field: ..Command
        ID 7 len 26 Field: ..Time
        ID 8 len 27 Field: ..State
        ID 9 len 26 Field: ..Info
        ID 10 len 5 End  (Status: Auto-commit, )
        ID 11 len 49 || 1 | root | localhost:3051 | NULL | Query | 0 | NULL | show processlist ||
        ID 12 len 5 End  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 1 COM_QUIT
mysqlsniffer exited cleanly.
17 MySQL packets captured (469 bytes)

Loads of stuff starts flying by… let’s filter it out a little more so we can just get the queries and not all the excess data.

$ sudo /path/to/mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY

192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=”
192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

Original source code and more information at:

http://hackmysql.com/mysqlsniffer

If you are running on a development server, it would be easier to just turn on query logging.

MySQL Tools (from http://mysqlhack.com).

Filed under: database, mysql, perl — lancevermilion @ 5:31 pm

MySQL Tools

The MySQL distros come with extra tools/scripts: mysqldumpslow, mysqldump, mysql_zap, etc. I’ve never known a DBA for whom these tools sufficed. The open-source community has created a sizable number of MySQL and MySQL-related tools which fill pretty much every niche and need. As a full-time developer of such tools, these projects are my speciality. All code listed here is free and open-source.

MySQL Forge is a repository for MySQL projects, tools, code snippets and other odds and ends. In my opinion, it’s not focused enough to be useful which is why I maintain this page. The tools here are ones that I know top MySQL professionals use regularly and recommend.

Hack MySQL Scripts

These are the scripts I created many years ago when no such (better) scripts existed. They have since been retired and replaced by Maatkit tools. I leave them here for historicity and because they are mentioned in several books (one of which was published in early 2010).

Tools

Profile

  • Project: none, archived here
  • Status: retired, replaced by Maatkit tools
  • Tested: no
  • Documented: yes, online and included with releases
  • Language: Perl
  • Design: procedural, partly modular

Maatkit (mk)

Maatkit is a collection of 20+ scripts originally created by Baron Schwartz. These scripts include mk-table-sync, mk-query-digest, mk-archiver, mk-heart, etc. I am the full-time developer and maintainer for all the scripts. Baron and myself are employed by Percona which sponsors most Maatkit development.

Tools

  • mk-table-checksum – Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
  • mk-table-sync – Synchronize MySQL tables efficiently.
  • mk-query-digest – Parses logs and more. Analyze, transform, filter, review and report on queries.
  • mk-archiver, mk-find, mk-heartbeat, mk-kill, mk-parallel-dump, mk-parallel-restore, mk-slave-delay, mk-upgrade, …

Profile

  • Project: Maatkit
  • Status: active, full-time, professional development
  • Tested: yes, over 2,000 tests
  • Documented: yes, POD in each tool, POD reproduced online at each release
  • Language: Perl
  • Design: procedural, highly modular

boots

Boots is an extensible command line database client for Drizzle and MySQL. It looks and feels almost like the old, venerable mysql cli, but it has modern features like “lingos” and pipes. In my opinion, it’s the MySQL-esque cli of the future for the MySQL-esque RDMS of the future, Drizzle. It still works with MySQL, though.

Profile

  • Project: boots
  • Status: active, professional development
  • Tested: yes
  • Documented: in-program help, not really documented yet since it’s still in devel
  • Language: Python
  • Design: object-oriented, extensible

Percona Projects

Percona develops and sponsors various MySQL tools, programs, patches, etc.–including Maatkit. These range from backup programs to data recovery tools. There’s hardly a way to summarize them all, so just check out the project page.

Profile

  • Project: Percona Projects
  • Status: active, full-time, professional development
  • Tested: unknown
  • Documented: somewhat online, larger documentation effort currently in progress
  • Language: C, C++, Perl
  • Design: various

Aspersa

Maatkit once had a masse of “utilities” which didn’t really belong in Maatkit proper, so Baron moved them to separted project: aspersa. Most of the tools are not directly MySQL-related, but they’re in the ecosystem so I’ve included them here. (If nothing else, they were born from a pure MySQL project: Maatkit.)

Tools

  • bt-aggregate – Aggregate and print GDB backtraces.
  • iodump – Compute per-PID I/O stats for Linux when iotop/pidstat/iopp are not available.
  • mext2 – Columnize output of mysqladmin.
  • snoop-to-tcpdump – Formats ‘snoop’ output the same way as ‘tcpdump’.
  • More tools added as needed…

Profile

  • Project: aspersa
  • Status: developed as needed
  • Tested: no
  • Documented: minimal
  • Language: Perl, bash
  • Deisgn: whatever works

Kontrollkit

Kontrollkit is a collection of scripts to simplify daily MySQL DBA responsibilities. Like Maatkit, it is a suite of many tools backed by a business: Kontrollsoft.

Tools

  • kt-backup-parallel – runs mysql backups in parallel super fast, has lots of reporting features.
  • kt-backup-ai – runs mysql backups standard mysqldump method but adds nice features like email reporting and backup completion checking as well as compression, also adds disk space checking and size analysis before running the backup to ensure you don’t use up 100% partition space with a backup.
  • kt-check-replication – script to report on replication status for slave servers.
  • kt-connections-log – logs connections to mysql to disk, reports on threshold overages.
  • kt-flush-tables-sequence – runs through schema.tables to flush in sequence before global flush.
  • kt-mysql-systemcheck – generates a report for point-in-time system status that is useful for troubleshooting MySQL servers.
  • Several more tools…

Profile

  • Project: kontrollkit
  • Status: active
  • Tested: no
  • Documented: no
  • Language: Perl, Python
  • Design: procedural

MMM

MMM does monitoring/failover and management of MySQL master-master replication configurations.

Profile

  • Project: MMM 2.x
  • Status: semi-active, professional and volunteer development
  • Tested: no
  • Documented: yes, POD and online
  • Language: Perl
  • Design: procedural, highly modular

innotop

innotop is a ‘top’ clone for MySQL. It was created by Baron Schwartz.

Profile

  • Project: innotop
  • Status: no longer in development
  • Tested: no
  • Documented: yes, POD
  • Language: Perl
  • Design: procedural, partly modular

Schema Sync

Schema Sync will generate the SQL necessary to migrate the schema of a source database to a target database (patch script), as well as a the SQL necessary to undo the changes after you apply them (revert script).

Profile

  • Project: SchemaSync
  • Status: active development
  • Tested: yes
  • Documented: basics
  • Language: Python
  • Design: procedural, modular

rtime

rtime is a program that records and reports query response times by sniffing MySQL protocol traffic. It’s coded by another Percona employee, Ignacio Nin. It’s meant to be small and light-weight, filling a role somewhere between mysqlsniffer and mk-query-digest –type tcdpump.

Profile

  • Project: rtime
  • Status: active development
  • Tested: no
  • Documented: yes
  • Language: C
  • Design: procedural

openark kit

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand. The kit was created and is maintained by Shlomi Noach.

Tools

  • oak-apply-ri – apply referential integrity on two columns with parent-child relationship.
  • oak-block-account – block or release MySQL users accounts, disabling them or enabling them to login.
  • oak-chunk-update – Perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.
  • oak-kill-slow-queries – terminate long running queries.
  • oak-modify-charset – change the character set (and collation) of a textual column.
  • oak-online-alter-table – Perform a non-blocking ALTER TABLE operation.
  • oak-purge-master-logs – purge master logs, depending on the state of replicating slaves.
  • oak-security-audit – audit accounts, passwords, privileges and other security settings.
  • oak-show-limits – show AUTO_INCREMENT free space.
  • oak-show-replication-status – show how far behind are replicating slaves on a given master.

Profile

  • Project: openarkkit
  • Status: active, professional development
  • Tested: no
  • Documented: yes, online
  • Language: Python
  • Design: procedural

mycheckpoint

Shlomi’s other tool is mycheckpoint. mycheckpoint is an open source monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

Profile

  • Project: mycheckpoint
  • Status: active, professional development
  • Tested: no
  • Documented: yes, online
  • Language: Python
  • Design: procedural

MySQL Sandbox

MySQL Sandbox, created by Giuseppe Maxia, is a set of tools (and Perl modules) that allow you to create isolated instances (sandboxes) of MySQL servers in various configurations (standalone, replication, etc.). MySQL sandboxes are useful for testing because they setup and tear down quickly and are self-contained, not interfering with a default MySQL server and its data, port, socket, etc.

Profile

  • Project: MySQL Sandbox
  • Status: active, professional development
  • Tested: yes
  • Documented: yes
  • Language: Perl
  • Design: procedural, modular

MySQL Cacti Templates

Although I’ve never used and know nothing about Cacti, I know that these MySQL Cacti templates are used extensively. Based on questions and issues I see related to MySQL monitoring with Cacti graphs, it seems that Cacti templates can be difficult to config correctly/well so these templates help make it easier.

Profile

  • Project: mysql-cacti-templates
  • Status: active, professional development
  • Tested: yes
  • Documented: yes
  • Language: Perl, PHP
  • Design: procedural
Older Posts »

Theme: Silver is the New Black. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.