• How to log to a PostgreSQL database

    This article describes how to set up logging to a PostgreSQL database using the Apache log4j logging utility.

    Note: For information about MySQL logging, see How to log to a mySQL database.

    Download the PostgreSQL JDBC Driver to the Wowza media server installation folder [install-dir]/lib. I used the "JDBC4 Postgresql Driver, Version 8.4-701": postgresql-8.4-701.jdbc4.jar

    In PostgreSQL admin, create a database named "wowzalogs", then open a SQL window and create the following table:
    CREATE TABLE accesslog
    (
      logid serial NOT NULL,
      date character varying(100),
      "time" character varying(100),
      tz character varying(100),
      xevent character varying(20),
      xcategory character varying(20),
      xseverity character varying(100),
      xstatus character varying(100),
      xctx character varying(100),
      xcomment character varying(255),
      xvhost character varying(100),
      xapp character varying(100),
      xappinst character varying(100),
      xduration character varying(100),
      sip character varying(100),
      sport character varying(100),
      suri character varying(255),
      cip character varying(100),
      cproto character varying(100),
      creferrer character varying(255),
      cuseragent character varying(100),
      cclientid character varying(25),
      csbytes character varying(20),
      scbytes character varying(20),
      xstreamid character varying(20),
      xspos character varying(20),
      csstreambytes character varying(20),
      scstreambytes character varying(20),
      xsname character varying(100),
      xsnamequery character varying(100),
      xfilename character varying(100),
      xfileext character varying(100),
      xfilesize character varying(100),
      xfilelength character varying(100),
      xsuri character varying(255),
      xsuristem character varying(255),
      xsuriquery character varying(255),
      csuristem character varying(255),
      csuriquery character varying(255),
      CONSTRAINT "PK_accesslog" PRIMARY KEY (logid)
    )
    WITH (OIDS=FALSE);
    ALTER TABLE accesslog OWNER TO postgres;
    Open the file [install-dir]/conf/log4j.properties in a text editor. In the first line, add a new root category "SQ":
    log4j.rootCategory=INFO, stdout, serverAccess, serverError, SQ
    In the same file, copy in the following appender. You will have to modify the password at least, and possibly the URL for the location of the database, and the user as necessary:
    log4j.appender.SQ=org.apache.log4j.jdbc.JDBCAppender
    log4j.appender.SQ.Driver=org.postgresql.Driver
    log4j.appender.SQ.URL=jdbc:postgresql://localhost:5432/wowzalogs
    log4j.appender.SQ.user=postgres
    log4j.appender.SQ.password=CHANGEME
    log4j.appender.SQ.layout=com.wowza.wms.logging.ECLFPatternLayout
    log4j.appender.SQ.layout.OutputHeader=false
    log4j.appender.SQ.sql=INSERT INTO accesslog (date, time, tz, xevent, xcategory, xseverity,xstatus,xctx,xcomment,xvhost,xapp,xappinst,xduration,sip,sport,suri,cip,cproto,creferrer,cuseragent,cclientid,csbytes,scbytes,xstreamid, xspos,csstreambytes,scstreambytes,xsname,xsnamequery,xfilename,xfileext,xfilesize,xfilelength,xsuri, xsuristem,xsuriquery,csuristem,csuriquery) VALUES ('%X{date}', '%X{time}', '%X{tz}', '%X{x-event}', '%X{x-category}', '%X{x-severity}', '%X{x-status}', '%X{x-ctx}', '%X{x-comment}', '%X{x-vhost}', '%X{x-app}', '%X{x-appinst}', '%X{x-duration}', '%X{s-ip}', '%X{sport}', '%X{s-uri}', '%X{c-ip}', '%X{c-proto}', '%X{c-referrer}', '%X{c-user-agent}', '%X{c-client-id}', '%X{cs-bytes}', '%X{scbytes}', '%X{x-stream-id}', '%X{x-spos}','%X{cs-stream-bytes}', '%X{sc-stream-bytes}', '%X{x-sname}', '%X{x-sname-query}', '%X{x-file-name}', '%X{x-file-ext}', '%X{x-file-size}', '%X{x-file-length}','%X{x-suri}','%X{x-suri-stem}', '%X{x-suri-query}', '%X{c-suri-stem}', '%X{c-suri-query}');
    Then restart the Wowza media server and check the table:
    select * from accesslog;
    Note: You should start the Wowza media server in the console or IDE to see any errors that occur.

    Note: Some fields might need to be longer to accommodate all possible values.


    If you're having problems or want to discuss this article, post in our forum.


    This article was originally published in forum thread: Logging to a Database started by rrlanham View original post