Wowza Media Systems – Logging to a Database

Logging to a MySQL Database

This is a guide to setting up logging to a MySQL database using log4j. PostgreSQL is covered in this article.

Note: Database logging is provided by the log4j open source logging system which Wowza uses for its logging framework. There have been reports of issues with database logging when the connection to the database is lost. The log4j system will not automatically try to reconnect to the database server.

First download the MySQL JDBC Connector here, then open the zip file and extract mysql-connector-java-5.1.8-bin.jar to [wowza-install-dir]/lib.

In MySQL, create a catalog named “wowzalogs”. Then create the following table to store all Wowza log fields. (Table attributes have the same name as log4j fields but without hyphens.)

Code:
CREATE TABLE  `wowzalogs`.`accesslog` (
  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` varchar(100) DEFAULT NULL,
  `time` varchar(100) DEFAULT NULL,
  `tz` varchar(100) DEFAULT NULL,
  `xevent` varchar(20) DEFAULT NULL,
  `xcategory` varchar(20) DEFAULT NULL,
  `xseverity` varchar(100) DEFAULT NULL,
  `xstatus` varchar(100) DEFAULT NULL,
  `xctx` varchar(100) DEFAULT NULL,
  `xcomment` varchar(255) DEFAULT NULL,
  `xvhost` varchar(100) DEFAULT NULL,
  `xapp` varchar(100) DEFAULT NULL,
  `xappinst` varchar(100) DEFAULT NULL,
  `xduration` varchar(100) DEFAULT NULL,
  `sip` varchar(100) DEFAULT NULL,
  `sport` varchar(100) DEFAULT NULL,
  `suri` varchar(255) DEFAULT NULL,
  `cip` varchar(100) DEFAULT NULL,
  `cproto` varchar(100) DEFAULT NULL,
  `creferrer` varchar(255) DEFAULT NULL,
  `cuseragent` varchar(100) DEFAULT NULL,
  `cclientid` varchar(25) DEFAULT NULL,
  `csbytes` varchar(20) DEFAULT NULL,
  `scbytes` varchar(20) DEFAULT NULL,
  `xstreamid` varchar(20) DEFAULT NULL,
  `xspos` varchar(20) DEFAULT NULL,
  `csstreambytes` varchar(20) DEFAULT NULL,
  `scstreambytes` varchar(20) DEFAULT NULL,
  `xsname` varchar(100) DEFAULT NULL,
  `xsnamequery` varchar(100) DEFAULT NULL,
  `xfilename` varchar(100) DEFAULT NULL,
  `xfileext` varchar(100) DEFAULT NULL,
  `xfilesize` varchar(100) DEFAULT NULL,
  `xfilelength` varchar(100) DEFAULT NULL,
  `xsuri` varchar(255) DEFAULT NULL,
  `xsuristem` varchar(255) DEFAULT NULL,
  `xsuriquery` varchar(255) DEFAULT NULL,
  `csuristem` varchar(255) DEFAULT NULL,
  `csuriquery` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`logid`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

 

 

 

Open /conf/log4j.properties. 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:

Code:
log4j.appender.SQ=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.SQ.Driver=com.mysql.jdbc.Driver
log4j.appender.SQ.URL=jdbc:mysql://localhost:3306/wowzalogs
log4j.appender.SQ.user=root
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 Wowza and check the table:
select * from accesslog;

NOTE: You should start Wowza in console or IDE to see any errors that occur

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s