Wowza Community

Logging to a Database

https://www.wowza.com/docs/how-to-log-to-a-mysql-database

Wowza does not work with this version of Java. It is best to install the most recent Java JDK 6 from Sun:

http://java.sun.com/javase/downloads/index.jsp

Also, see this post:

http://www.wowza.com/community/t/-/48

Charlie

This is the PostgreSQL version of logging to a database.

Download the PostgreSQL JDBC jar to [wowza-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 SQL window and create this 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 /conf/log4j.properties. In the first line, add a new root category “SQ”:

log4j.rootCategory=INFO, stdout, serverAccess, serverError, [B]SQ[/B]

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 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.

Richard

Hi,

I use that log method on a Windows Server and on a Linux Debian, but now I need to run on a RedHat 64bits, I had used the las version of the mysql connector: 5.1.12.

On it show me that problem:

Configure logging: file:///usr/local/WowzaMediaServer/conf/log4j.properties

log4j:ERROR Failed to load driver

java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

at java.net.URLClassLoader$1.run(URLClassLoader.java:217)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:205)

at java.lang.ClassLoader.loadClass(ClassLoader.java:319)

at java.lang.ClassLoader.loadClass(ClassLoader.java:264)

at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:332)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:186)

at org.apache.log4j.jdbc.JDBCAppender.setDriver(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:616)

at org.apache.log4j.config.PropertySetter.setProperty(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperty(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperties(Unknown Source)

at org.apache.log4j.config.PropertySetter.setProperties(Unknown Source)

at org.apache.log4j.PropertyConfigurator.parseAppender(Unknown Source)

at org.apache.log4j.PropertyConfigurator.parseCategory(Unknown Source)

at org.apache.log4j.PropertyConfigurator.configureRootCategory(Unknown Source)

at org.apache.log4j.PropertyConfigurator.doConfigure(Unknown Source)

at org.apache.log4j.PropertyConfigurator.configure(Unknown Source)

at com.wowza.wms.logging.WMSLoggerFactory.initializeLogging(Unknown Source)

at com.wowza.wms.logging.WMSLoggerFactory.initializeLogging(Unknown Source)

at com.wowza.wms.server.Server.initLogging(Unknown Source)

at com.wowza.wms.server.Server.start(Unknown Source)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:616)

at com.wowza.wms.bootstrap.Bootstrap.startServer(Bootstrap.java:248)

at com.wowza.wms.bootstrap.Bootstrap.main(Bootstrap.java:47)

INFO server server-start Wowza Media Server 2 Perpetual 2.0.0 build22912 -

¿Can you help me?¿Its a 64bits system problem?

It the problems where’s on MySQL connector, ¿where can I found the 5.8?

Kind regard

To be able to use the CategoryInclude & EventExclude filters that are available with the ECLFPatternLayout, you need to extend org.apache.log4j.jdbc.JDBCAppender and write your own execute method.

The problem is that the filters return an empty string when the filter is applied and the default execute method throws an SQLException which is what you see when first starting the server. It is also coded to only show 1 exception so you only see 1 warning. When the exception is thrown, the log entry is not cleared from the buffer so when a valid entry is processed, an insert is done for each entry in the buffer resulting in the repeated lines. The following class fixes the problem.

package com.wowza.wms.plugin.logging;

import java.sql.SQLException;

import org.apache.log4j.jdbc.JDBCAppender;

public class WowzaJDBCAppender extends JDBCAppender {
	protected void execute(String sql) throws SQLException {
		if (sql.isEmpty()) {
			return;
		}
		StringBuffer sbSql = new StringBuffer();
		String[] bits = sql.split(",");
		for (int i = 0; i < bits.length; i++) {
			if (bits[i].trim().startsWith("-"))
				bits[i] = bits[i].replace("-", "null");
			 if (bits[i].trim().startsWith("'-'"))
				 bits[i] = bits[i].replace("'-'", "null");
			sbSql.append(bits[i]);
			if(i + 1 < bits.length)
				sbSql.append(",");
		}

		super.execute(sbSql.toString());
	}
}

The only bit it really needs is

		if (sql.isEmpty()) {
			return;
		}

		super.execute(sbSql.toString());

but I have also included code to replace the - with null when the field is empty so you can use proper types in your database table. It is a little bit messy but it does the job.

Richard, Charlie, Would it be possible to add a NullValueString to the ECLFPatternLayout so that we could define this in the log4j.properties file and do away with the extra bit of code above.

The modified appender based on the normal statistics appender is as follows. Make sure the appender class is com.wowza.wms.plugin.logging.WowzaJDBCAppender.

# Statistics database appender (to use this appender add "SQ" to the list of appenders in the first line of this file)
log4j.appender.SQ=com.wowza.wms.plugin.logging.WowzaJDBCAppender
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.layout.CategoryInclude=session,stream
log4j.appender.SQ.layout.EventExclude=comment
log4j.appender.SQ.sql=INSERT INTO statslog (xseverity, xcategory, xevent, date, time, xapp, cclientid, cip, cproto, csbytes, scbytes, xduration, xsname, xstreamid, xspos, scstreambytes, csstreambytes, xfilesize, xfilelength, xctx, xcomment) VALUES ('%X{x-severity}', '%X{x-category}', '%X{x-event}','%X{date}', '%X{time}', '%X{x-app}', %X{c-client-id}, '%X{c-ip}', '%X{c-proto}', %X{cs-bytes}, %X{sc-bytes}, %X{x-duration}, '%X{x-sname}', %X{x-stream-id}, %X{x-spos}, %X{sc-stream-bytes}, %X{cs-stream-bytes}, %X{x-file-size}, %X{x-file-length}, '%X{x-ctx}', '%X{x-comment}');

Note, I hafe removed the ’ ’ from around the numeric values. The sql for the table is

CREATE TABLE `statslog` (
  `logid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` date DEFAULT NULL,
  `time` time DEFAULT NULL,
  `xevent` varchar(20) DEFAULT NULL,
  `xcategory` varchar(20) DEFAULT NULL,
  `xseverity` varchar(20) DEFAULT NULL,
  `xctx` varchar(255) DEFAULT NULL,
  `xcomment` varchar(255) DEFAULT NULL,
  `xapp` varchar(100) DEFAULT NULL,
  `xduration` double(11,3) DEFAULT NULL,
  `cip` varchar(100) DEFAULT NULL,
  `cproto` varchar(100) DEFAULT NULL,
  `cclientid` int(11) DEFAULT NULL,
  `csbytes` int(11) DEFAULT NULL,
  `scbytes` int(11) DEFAULT NULL,
  `xstreamid` int(11) DEFAULT NULL,
  `xspos` int(11) DEFAULT NULL,
  `csstreambytes` int(11) DEFAULT NULL,
  `scstreambytes` int(11) DEFAULT NULL,
  `xsname` varchar(100) DEFAULT NULL,
  `xfilesize` int(11) DEFAULT NULL,
  `xfilelength` double(11,3) DEFAULT NULL,
  PRIMARY KEY (`logid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

Hope this helps someone.

Roger

Hello, sorry for being a little offtopic but I would need help figure out how to calculate traffic usage from wowzalogs database.

Let’s say I need current month server to client traffic for ApplicationX.

I just can’t figure out which field under what condition must be added / incremented.

Do I need to add all scstreambytes if xevent = destroy (or is it when xevent = disconnect?, or xevent = stop?) and if xapp = ApplicationX ?

I’ve tried this and get the same errors. I don’t know of a solution except to remove those lines or ignore the errors.

Richard

Try this in NetStream.play:

netstream.play("example.flv?userId=123")

Then look in the x-sname-query field for stream events and you will see “userID=123”.

Of course you can append some other metadata.

Richard

Do you have a url for that? I didn’t post instructions for mssqlserver because I couldn’t get it working. I don’t have msdn subscription, which was a disadvantage for working on it.

Richard

The problem I had was on with mssqlserver and the jdbc driver, Java wasn’t involved. If you can get it working, please post.

Richard

Nice! Much appreciated. Thanks

Richard

Great post. Very helpful.

Thanks,

Richard

IBut the problem stil been the same, if I remove the “SQ” sentence and the rest of the code for connect to MySQL, any erroas has showed the the WoWzaMedia Server still not working.

If you remove the SQ appender and it is not working, try starting over with an unchanged copy of log4j.properties

Richard

You have to download the JDBC jar file and copy it to [wowza-install-dir]/lib folder

This is where I got a mysql JDBC jar file that worked for me:

http://dev.mysql.com/downloads/connector/j/5.1.html

Richard

Try this one:

http://laketk.com/mysql-connector-java-5.1.8-bin.jar

Richard

I don’t think there is a way to do that.

Richard

xfilename is the suggested name for the database column. The log field is “x-file-name”

Richard

Take a look at the access log, there is usually good error or warn lines about what is happening.

Richard

Not every row in the logs includes data for every field. It’s a flat file.

Richard

If you see “-” it means that row doesn’t have data in that field. Unless there is something wrong, but probably nothing wrong. “-” is the data that is logged in fields that are not relevant for a row. That field should have filename in the rows where it is relevant.

Richard