There is a typo in the sql template, $msg% instead of %msg%; which rsyslog tried to tell me, and I even quoted in my original question.
Update:
2 more things that have popped up in the comments and in follow-up questions:
- Using
auditd
this way will dramatically increase your log volume, especially if the system is heavily in use via commandline. Adjust your log retention policy.
Auditd
logs on the host where they are created are just as secure as other files on the same box. Forward your logs to a remote log collection server like ELK or Graylog to preserve your logs' integrity. Plus, adding to the point above, it allows to more aggressively delete old logs.
As was suggested by Michael Hampton, auditd
is the correct tool for the job here.
I tested this on an Ubuntu 12.10 installation, so your mileage may vary on other systems.
Install auditd
:
apt-get install auditd
Add these 2 lines to /etc/audit/audit.rules
:
-a exit,always -F arch=b64 -F euid=0 -S execve
-a exit,always -F arch=b32 -F euid=0 -S execve
These will track all commands run by root (euid=0
). Why two rules? The execve
syscall must be tracked in both 32 and 64 bit code.
To get rid of auid=4294967295
messages in logs, add audit=1
to the kernel's cmdline (by editing /etc/default/grub
)
Place the line
session required pam_loginuid.so
in all PAM config files that are relevant to login (/etc/pam.d/{login,kdm,sshd}
), but not in the files that are relevant to su
or sudo
.
This will allow auditd
to get the calling user's uid
correctly when calling sudo
or su
.
$ id -u
1000
$ sudo ls /
bin boot data dev etc home initrd.img initrd.img.old lib lib32 lib64 lost+found media mnt opt proc root run sbin scratch selinux srv sys tmp usr var vmlinuz vmlinuz.old
$ sudo su -
# ls /etc
[...]
This will yield something like this in /var/log/audit/auditd.log
:
----
time->Mon Feb 4 09:57:06 2013
type=PATH msg=audit(1359968226.239:576): item=1 name=(null) inode=668682 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=PATH msg=audit(1359968226.239:576): item=0 name="/bin/ls" inode=2117 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=CWD msg=audit(1359968226.239:576): cwd="/home/user"
type=EXECVE msg=audit(1359968226.239:576): argc=2 a0="ls" a1="/"
type=SYSCALL msg=audit(1359968226.239:576): arch=c000003e syscall=59 success=yes exit=0 a0=10cfc48 a1=10d07c8 a2=10d5750 a3=7fff2eb2d1f0 items=2 ppid=26569 pid=26570 auid=1000 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=1 comm="ls" exe="/bin/ls" key=(null)
----
time->Mon Feb 4 09:57:06 2013
type=PATH msg=audit(1359968226.231:575): item=1 name=(null) inode=668682 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=PATH msg=audit(1359968226.231:575): item=0 name="/usr/bin/sudo" inode=530900 dev=08:01 mode=0104755 ouid=0 ogid=0 rdev=00:00
type=CWD msg=audit(1359968226.231:575): cwd="/home/user"
type=BPRM_FCAPS msg=audit(1359968226.231:575): fver=0 fp=0000000000000000 fi=0000000000000000 fe=0 old_pp=0000000000000000 old_pi=0000000000000000 old_pe=0000000000000000 new_pp=ffffffffffffffff new_pi=0000000000000000 new_pe=ffffffffffffffff
type=EXECVE msg=audit(1359968226.231:575): argc=3 a0="sudo" a1="ls" a2="/"
type=SYSCALL msg=audit(1359968226.231:575): arch=c000003e syscall=59 success=yes exit=0 a0=7fff327ecab0 a1=7fd330e1b958 a2=17cc8d0 a3=7fff327ec670 items=2 ppid=3933 pid=26569 auid=1000 uid=1000 gid=1000 euid=0 suid=0 fsuid=0 egid=1000 sgid=1000 fsgid=1000 tty=pts0 ses=1 comm="sudo" exe="/usr/bin/sudo" key=(null)
----
time->Mon Feb 4 09:57:09 2013
type=PATH msg=audit(1359968229.523:578): item=1 name=(null) inode=668682 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=PATH msg=audit(1359968229.523:578): item=0 name="/bin/su" inode=44 dev=08:01 mode=0104755 ouid=0 ogid=0 rdev=00:00
type=CWD msg=audit(1359968229.523:578): cwd="/home/user"
type=EXECVE msg=audit(1359968229.523:578): argc=2 a0="su" a1="-"
type=SYSCALL msg=audit(1359968229.523:578): arch=c000003e syscall=59 success=yes exit=0 a0=1ceec48 a1=1cef7c8 a2=1cf4750 a3=7fff083bd920 items=2 ppid=26611 pid=26612 auid=1000 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=1 comm="su" exe="/bin/su" key=(null)
----
time->Mon Feb 4 09:57:09 2013
type=PATH msg=audit(1359968229.519:577): item=1 name=(null) inode=668682 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=PATH msg=audit(1359968229.519:577): item=0 name="/usr/bin/sudo" inode=530900 dev=08:01 mode=0104755 ouid=0 ogid=0 rdev=00:00
type=CWD msg=audit(1359968229.519:577): cwd="/home/user"
type=BPRM_FCAPS msg=audit(1359968229.519:577): fver=0 fp=0000000000000000 fi=0000000000000000 fe=0 old_pp=0000000000000000 old_pi=0000000000000000 old_pe=0000000000000000 new_pp=ffffffffffffffff new_pi=0000000000000000 new_pe=ffffffffffffffff
type=EXECVE msg=audit(1359968229.519:577): argc=3 a0="sudo" a1="su" a2="-"
type=SYSCALL msg=audit(1359968229.519:577): arch=c000003e syscall=59 success=yes exit=0 a0=7fff327ecab0 a1=7fd330e1b958 a2=17cc8d0 a3=7fff327ec670 items=2 ppid=3933 pid=26611 auid=1000 uid=1000 gid=1000 euid=0 suid=0 fsuid=0 egid=1000 sgid=1000 fsgid=1000 tty=pts0 ses=1 comm="sudo" exe="/usr/bin/sudo" key=(null)
----
time->Mon Feb 4 09:57:09 2013
type=PATH msg=audit(1359968229.543:585): item=1 name=(null) inode=668682 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=PATH msg=audit(1359968229.543:585): item=0 name="/bin/bash" inode=6941 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=CWD msg=audit(1359968229.543:585): cwd="/root"
type=EXECVE msg=audit(1359968229.543:585): argc=1 a0="-su"
type=SYSCALL msg=audit(1359968229.543:585): arch=c000003e syscall=59 success=yes exit=0 a0=13695a0 a1=7fffce08a3e0 a2=135a030 a3=7fffce08c200 items=2 ppid=26612 pid=26622 auid=1000 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=1 comm="bash" exe="/bin/bash" key=(null)
----
time->Mon Feb 4 09:57:11 2013
type=PATH msg=audit(1359968231.663:594): item=1 name=(null) inode=668682 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=PATH msg=audit(1359968231.663:594): item=0 name="/bin/ls" inode=2117 dev=08:01 mode=0100755 ouid=0 ogid=0 rdev=00:00
type=CWD msg=audit(1359968231.663:594): cwd="/root"
type=EXECVE msg=audit(1359968231.663:594): argc=3 a0="ls" a1="--color=auto" a2="/etc"
type=SYSCALL msg=audit(1359968231.663:594): arch=c000003e syscall=59 success=yes exit=0 a0=7fff8c709950 a1=7f91a12149d8 a2=1194c50 a3=7fff8c709510 items=2 ppid=26622 pid=26661 auid=1000 uid=0 gid=0 euid=0 suid=0 fsuid=0 egid=0 sgid=0 fsgid=0 tty=pts0 ses=1 comm="ls" exe="/bin/ls" key=(null)
The auid
column contains the calling user's uid
, which allows you filter for commands run by this user with
ausearch -ua 1000
This will even list commands the user ran as root.
Sources:
Best Answer
You'll need to do two sequential filters rather than both on one line.
:msg, contains, "some-text"
if $syslogfacility-text == "facility" then /var/log/somelog.log
~
Edit:
I take that back. I have seen it done both ways now. I just found this example in the rsyslog Wiki that should be able to be adapted.
if $programname == 'popa3d' and $syslogseverity <= '6' then /var/log/popa3d.log
You of course will substitute your conditions in to the example.
if $syslogfacility-text == 'local0' and $msg contains 'some-text' then /var/log/somelog.log
& ~
Rsyslog Wiki
Rsyslog Docs