How to log everything in postgresql database.

Alain De Vos

Alain De Vos

- disable syslog,syslogd

- enable syslog-ng compile with :
OPTIONS_FILE_SET+=CURL
OPTIONS_FILE_SET+=DOCS
OPTIONS_FILE_SET+=IPV6
OPTIONS_FILE_SET+=JSON
OPTIONS_FILE_SET+=MONGO
OPTIONS_FILE_SET+=REDIS
OPTIONS_FILE_SET+=SQL
OPTIONS_FILE_SET+=TCP_WRAPPERS

- Install libdbi & libdbi-drivers
- Edit /usr/local/etc/rc.d/syslog-ng
----> # REQUIRE: mountcritremote cleanvar ldconfig postgresql
We want to force to start postgresql first.



reboot and start postgresql & syslog-ng,
New syslog-ng conf file below,
 
cat syslog-ng.conf
Code: 
@version:4.10
@include "scl.conf"


source src {
    file("/dev/klog" flags(kernel) program_override("kernel"));
    unix-dgram("/var/run/log");
    udp();
    internal();
};

source s_network {
    default-network-drivers(
    );
};

destination d_pgsql {
    sql(
        type(pgsql)
        host("127.0.0.1")
        database("logs")  # Ensure you ran 'CREATE DATABASE logs;' in psql
        username("x")
        password("x")
        table("logs")
        columns("host", "facility", "priority", "tag", "datetime", "program", "msg")
        values("$HOST", "$FACILITY", "$LEVEL", "$TAG", "$ISODATE", "$PROGRAM", "$MSGONLY")
        indexes("datetime", "program")
    );
};

filter f_no_acpi_noise {
    not message("ACPI group/action undefined");
};

#
# destinations
#
destination messages { file("/var/log/messages"); };
destination security { file("/var/log/security"); };
destination authlog { file("/var/log/auth.log"); };
destination maillog { file("/var/log/maillog"); };
destination lpd-errs { file("/var/log/lpd-errs"); };
destination xferlog { file("/var/log/xferlog"); };
destination cron { file("/var/log/cron"); };
destination debuglog { file("/var/log/debug.log"); };
destination consolelog { file("/var/log/console.log"); };
destination all { file("/var/log/all.log"); };
destination newscrit { file("/var/log/news/news.crit"); };
destination newserr { file("/var/log/news/news.err"); };
destination newsnotice { file("/var/log/news/news.notice"); };
destination slip { file("/var/log/slip.log"); };
destination ppp { file("/var/log/ppp.log"); };
destination console { file("/dev/console"); };
destination allusers { usertty("*"); };
destination loghost { udp("loghost" port(514)); };

#
# log facility filters
#
filter f_auth { facility(auth); };
filter f_authpriv { facility(authpriv); };
filter f_not_authpriv { not facility(authpriv); };
filter f_console { facility(console); };
filter f_cron { facility(cron); };
filter f_daemon { facility(daemon); };
filter f_ftp { facility(ftp); };
filter f_kern { facility(kern); };
filter f_lpr { facility(lpr); };
filter f_mail { facility(mail); };
filter f_news { facility(news); };
filter f_security { facility(security); };
filter f_user { facility(user); };
filter f_uucp { facility(uucp); };
filter f_local0 { facility(local0); };
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); };

#
# log level filters
#
filter f_emerg { level(emerg); };
filter f_alert { level(alert..emerg); };
filter f_crit { level(crit..emerg); };
filter f_err { level(err..emerg); };
filter f_warning { level(warning..emerg); };
filter f_notice { level(notice..emerg); };
filter f_info { level(info..emerg); };
filter f_debug { level(debug..emerg); };
filter f_is_debug { level(debug); };

#
# program filters
#
filter f_ppp { program("ppp"); };
filter f_slip { program("startslip"); };

#
# *.err;kern.warning;auth.notice;mail.crit        /dev/console
log { source(src); filter(f_err); destination(console); };
log { source(src); filter(f_kern); filter(f_warning); destination(console); };
log { source(src); filter(f_auth); filter(f_notice); destination(console); };
log { source(src); filter(f_mail); filter(f_crit); destination(console); };

#
# *.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err    /var/log/messages
log { source(src); filter(f_notice); filter(f_not_authpriv); destination(messages); };
log { source(src); filter(f_kern); filter(f_debug); destination(messages); };
log { source(src); filter(f_lpr); filter(f_info); destination(messages); };
log { source(src); filter(f_mail); filter(f_crit); destination(messages); };
log { source(src); filter(f_news); filter(f_err); destination(messages); };

#
# security.*                        /var/log/security
log { source(src); filter(f_security); destination(security); };

#
# auth.info;authpriv.info                /var/log/auth.log
log { source(src); filter(f_auth); filter(f_info); destination(authlog); };
log { source(src); filter(f_authpriv); filter(f_info); destination(authlog); };

#
# mail.info                        /var/log/maillog
#
log { source(src); filter(f_mail); filter(f_info); destination(maillog); };

#
# lpr.info                        /var/log/lpd-errs
#
log { source(src); filter(f_lpr); filter(f_info); destination(lpd-errs); };

#
# ftp.info                        /var/log/xferlog
#
log { source(src); filter(f_ftp); filter(f_info); destination(xferlog); };

#
# cron.*                        /var/log/cron
log { source(src); filter(f_cron); destination(cron); };

#
# *.=debug                        /var/log/debug.log
#
# log { source(src); filter(f_is_debug); destination(debuglog); };

#
# *.emerg                        *
log { source(src); filter(f_emerg); destination(allusers); };

#
# uncomment this to log all writes to /dev/console to /var/log/console.log
# console.info                        /var/log/console.log
log { source(src); filter(f_console); filter(f_info); destination(consolelog); };

#
# uncomment this to enable logging of all log messages to /var/log/all.log
# touch /var/log/all.log and chmod it to mode 600 before it will work
# *.*                            /var/log/all.log
log { source(src); destination(all); };
log { source(src); destination(d_pgsql); };


#
# uncomment this to enable logging to a remote loghost named loghost
# *.*                            @loghost
#
#log { source(src); destination(loghost); };

#
# uncomment these if you're running inn
# news.crit                        /var/log/news/news.crit
# news.err                        /var/log/news/news.err
# news.notice                        /var/log/news/news.notice
#
#log { source(src); filter(f_news); filter(f_crit); destination(newscrit); };
#log { source(src); filter(f_news); filter(f_err); destination(newserr); };
#log { source(src); filter(f_news); filter(f_notice); destination(newsnotice); };

#
# !startslip
# *.*                            /var/log/slip.log
#
#log { source(src); filter(f_slip); destination(slip); };

#
# !ppp
# *.*                            /var/log/ppp.log
#
#log { source(src); filter(f_ppp); destination(ppp); };


# Destinations: Where to send the data
destination d_all_logs { file("/var/log/all.log"); };


options { 
    chain_hostnames(off); 
    flush_lines(0); 
    threaded(yes);
    suppress(5); 
};
 
A python tkinter program to show your logs in a gui,
Code: 
import tkinter as tk
from tkinter import ttk
import psycopg2
from tkinter import messagebox

def fetch_logs():
    try:
        # Verbinding maken met de PostgreSQL database op FreeBSD
        conn = psycopg2.connect(
            host="127.0.0.1",
            database="logs",
            user="x",
            password="x",
            connect_timeout=3
        )
        cur = conn.cursor()
        
        # Haal de laatste 100 logs op voor het overzicht
        cur.execute("SELECT datetime, program, msg FROM logs ORDER BY datetime DESC LIMIT 100")
        rows = cur.fetchall()
        
        # Bestaande rijen verwijderen voor de verversing
        for i in tree.get_children():
            tree.delete(i)
            
        # Nieuwe data invoegen
        for row in rows:
            tree.insert("", "end", values=row)
            
        cur.close()
        conn.close()
        
    except psycopg2.Error as e:
        print(f"Database fout: {e}")
        # Optioneel: toon een waarschuwing in de GUI als de verbinding wegvalt
        
    # Vernieuw de data elke 5 seconden (5000ms)
    root.after(5000, fetch_logs)

# --- GUI Setup ---
root = tk.Tk()
root.title("FreeBSD Syslog-ng Monitor")
root.geometry("1000x500")

# Container voor de tabel en scrollbar
main_frame = tk.Frame(root)
main_frame.pack(fill="both", expand=True, padx=10, pady=10)

# De Tabel (Treeview)
columns = ("datetime", "program", "message")
tree = ttk.Treeview(main_frame, columns=columns, show='headings', selectmode="browse")

# Kolomkoppen en breedtes instellen
tree.heading("datetime", text="Tijdstip")
tree.heading("program", text="Programma")
tree.heading("message", text="Log Bericht")

tree.column("datetime", width=200, anchor="w")
tree.column("program", width=120, anchor="w")
tree.column("message", width=600, anchor="w")

# De Scrollbar toevoegen
scrollbar = ttk.Scrollbar(main_frame, orient="vertical", command=tree.yview)
tree.configure(yscrollcommand=scrollbar.set)

# Lay-out van tabel en scrollbar (naast elkaar)
tree.pack(side="left", fill="both", expand=True)
scrollbar.pack(side="right", fill="y")

# Statusbalk onderaan
status_label = tk.Label(root, text="Live verbinding met PostgreSQL: logs", bd=1, relief="sunken", anchor="w")
status_label.pack(side="bottom", fill="x")

# Start de eerste data-ophaling
fetch_logs()

# De applicatie starten
root.mainloop()
 
A screenshot,
test.png
 
You must log in or register to reply here.
Back
Top