Sure, here it is: http://www.microsoft.com/downloads/details.aspx?FamilyID=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en
Ohh so you were running into problems.
If you’re not succeeding with this I’ll try it on my own to help you, but I’m an MS developer so I don’t know how much help I can give in java troubleshooting.
Attila
So here is a detailed guide on how to configure wowza to log into Microsoft SQL Server 2005 or later. I have tested it with SQL Server 2008 R2 November CTP, but it should work with other versions as well.
There are some prerequisities what you must check before begin:
Check in the SQL Server Configuration tool that TCP/IP is enabled for the used SQL Server instance.
Check in the SQL Server configuration that SQL authentication is enabled.
As a first step download the latest JDBC driver from Microsoft: http://www.microsoft.com/downloads/details.aspx?FamilyID=f914793a-6fb4-475f-9537-b8fcb776befd&displaylang=en
After unzipping it into a directory, copy sqljdbc4.jar into the lib folder of your Wowza Media Server installation.
Now we’ve to create a database and a user for this database with read/write rights.
Please make sure that you will change username or at least the default password for production environment.
Start up SQL Server Management Studio and execute the following database script:
CREATE DATABASE WowzaLog
GO
BEGIN TRAN
GO
USE WowzaLog
GO
CREATE TABLE dbo.AccessLog
(
Id int NOT NULL IDENTITY (1, 1),
[Date] NVARCHAR(100) NULL,
[Time] NVARCHAR(100) NULL,
TZ NVARCHAR(100) NULL,
XEVENT NVARCHAR(20) NULL,
XCATEGORY NVARCHAR(20) NULL,
XSEVERITY NVARCHAR(100) NULL,
XSTATUS NVARCHAR(100) NULL,
XCTX NVARCHAR(100) NULL,
XCOMMENT NVARCHAR(255) NULL,
XVHOST NVARCHAR(100) NULL,
XAPP NVARCHAR(100) NULL,
XAPPINST NVARCHAR(100) NULL,
XDURATION NVARCHAR(100) NULL,
SIP NVARCHAR(100) NULL,
SPORT NVARCHAR(100) NULL,
SURI NVARCHAR(255) NULL,
CIP NVARCHAR(100) NULL,
CPROTO NVARCHAR(100) NULL,
CREFERRER NVARCHAR(255) NULL,
CUSERAGENT NVARCHAR(100) NULL,
CCLIENTID NVARCHAR(25) NULL,
CSBYTES NVARCHAR(20) NULL,
SCBYTES NVARCHAR(20) NULL,
XSTREAMID NVARCHAR(20) NULL,
XSPOS NVARCHAR(20) NULL,
CSSTREAMBYTES NVARCHAR(20) NULL,
SCSTREAMBYTES NVARCHAR(20) NULL,
XSNAME NVARCHAR(100) NULL,
XSNAMEQUERY NVARCHAR(100) NULL,
XFILENAME NVARCHAR(100) NULL,
XFILEEXT NVARCHAR(100) NULL,
XFILESIZE NVARCHAR(100) NULL,
XFILELENGTH NVARCHAR(100) NULL,
XSURI NVARCHAR(255) NULL,
XSURISTEM NVARCHAR(255) NULL,
XSURIQUERY NVARCHAR(255) NULL,
CSURISTEM NVARCHAR(255) NULL,
CSURIQUERY NVARCHAR(255) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.AccessLog ADD CONSTRAINT
PK_AccessLog PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.AccessLog SET (LOCK_ESCALATION = TABLE)
GO
CREATE LOGIN [WowzaLogger] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[WowzaLog], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
CREATE USER [WowzaLogger] FOR LOGIN [WowzaLogger]
GO
EXEC sp_addrolemember N'db_datareader', N'WowzaLogger'
GO
EXEC sp_addrolemember N'db_datawriter', N'WowzaLogger'
GO
COMMIT
GO
If everything went OK, then you’ve a database named WowzaLog in you SQL Server and an AccessLog table in it.
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:
# SQL Server appender
log4j.appender.SQ=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.SQ.Driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
log4j.appender.SQ.URL=jdbc:sqlserver://localhost;databaseName=WowzaLog
log4j.appender.SQ.user=WowzaLogger
log4j.appender.SQ.password=P@ssw0rd
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
Have fun,
Attila
Hello,
We use that method to make some simple accountig for our clients but when the users and streams get high there is a problem when you try to make some complicated serches in the database and specialy when you try to make very detailed statistics. So we make some optimization of the database that work ideal for more than 10 000 000 records (~2GB size). We just add some keys (in RED) to the table :
…
PRIMARY KEY (logid),
UNIQUE KEY sk (logid,date,time,xctx,cip,xevent,scstreambytes,xduration,csstreambytes,cclientid,xsname)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
With that optimization querys finish under couple of seconds for example before that we need to wait more than 3-4 min. to get detailed statics.
I hope to be helpful to someone.
Hi guys.
we’ve successfully integrated the database logging function with MySQL. However, We’re having trouble getting the name of the recorded file to be logged. We have sett the application.xml Stream type to “live-record” and the fles is being recorded ok, but there is no file name being logged. Isnt the “xfilename” field supposed to be inserted with the recorded file’s name?
I also noticed that this field is written in RED in your ‘appender code’. Not sure why.
thanks in advance for your help.
wow - thanks for the fast response.
We have the field in the database, and we are using the “Appender code” the exact same way that you have provided it:
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}');
Its Just not logging ANYTHING. Why isnt the name of the file being logged when a live stream is started?
Thanks.
Take a look at the access log, there is usually good error or warn lines about what is happening.
Richard
Ok. thanks.
I see this:
log4j:ERROR Failed to execute sql
com.mysql.jdbc.mysqldatatruncation: Data truncation: Data too long for column ‘x comment’ at row 1
The ‘xcomment’ field sql error has been fixxed (simply increased size from 255 to 500).
But there is still no file name being entered into the database/log. Only “-” is being entered.
I see.
How would we go about grabbing & updating the db/log with the recorded file name?
Is there anyway that we split the logs into several tables ? or several DBs ?
Table per application for example …
The problem is that the DB is getting little bigger now ( only 750,000 Record , 300 Meg storage )
so any MySQL syntax take long time to get the results ( specially when there is calculation in it )
the configuration was good without any errors but the values are not getting inserted into the SQL Server DB…i downloaded the same jar file that was shown and even cross checked the class name being referenced…i also have the tcp port open…
i checked the error logs and i do not find any errors relating to this in there…Any suggestions?
Now that i corrected the log4j file,
I am getting the following error in the wowza log…i checked to see that the TCP port is enabled in the sql server configuration…what else can i do to get rid of this?
Configure logging: file:///C:/Program Files/Wowza Media Systems/Wowza Media Serv
er 2.1.1/conf/log4j.properties
INFO server server-start Wowza Media Server 2 Developer 2.1.1 build24490 -
log4j:ERROR Failed to excute sql
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the ho
st localhost, port 1433 has failed. Error: "Connection refused: connect. Verify
the connection properties, check that an instance of SQL Server is running on th
e host and accepting TCP/IP connections at the port, and that no firewall is blo
cking TCP connections to the port.".
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(S
QLServerException.java:171)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLSer
verConnection.java:1033)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConne
ction.java:817)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerCon
nection.java:700)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.
java:842)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at org.apache.log4j.jdbc.JDBCAppender.getConnection(Unknown Source)
at org.apache.log4j.jdbc.JDBCAppender.execute(Unknown Source)
at org.apache.log4j.jdbc.JDBCAppender.flushBuffer(Unknown Source)
at org.apache.log4j.jdbc.JDBCAppender.append(Unknown Source)
at org.apache.log4j.AppenderSkeleton.doAppend(Unknown Source)
at org.apache.log4j.helpers.AppenderAttachableImpl.appendLoopOnAppenders
(Unknown Source)
at org.apache.log4j.Category.callAppenders(Unknown Source)
at org.apache.log4j.Category.forcedLog(Unknown Source)
at org.apache.log4j.Category.log(Unknown Source)
at com.wowza.wms.logging.WMSLogger.log(Unknown Source)
at com.wowza.wms.logging.WMSLogger.info(Unknown Source)
at com.wowza.wms.server.Server.startServer(Unknown Source)
at com.wowza.wms.server.Server.start(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.wowza.wms.bootstrap.Bootstrap.startServer(Bootstrap.java:248)
at com.wowza.wms.bootstrap.Bootstrap.main(Bootstrap.java:47)
thanks
I got it!
The mistake i was doing was that i did enable the tcp port but did not get into it’s properties again and enable it for that particular instance. My bad.
Thank you Richard!
Hi
I am trying to setup the mysql log,but if i add the SQ to the 1st line of log4j.properties and i restart wowza,it says streaming server not found:
log4j.rootCategory=INFO, stdout, serverAccess, serverError, SQ
Also,if i try to start wowza from console to see the error,i get the following error:
/usr/local/WowzaMediaServer-2.0.0/bin/startup.sh: line 2: ./setenv.sh: No such file or directory
What i really need is to track users watching a movie in any player(web,mobile) in a pay per view system.The question is if i can do that with wowza log,or with any other module.I am not sure how could i send/log any data to track the user who is watching.Basically i would need to send user ID and video ID.
Thanks
Yes i added the appender.What happens is that i can start it from cli,but when trying to play a video is says server not found.Then,if i stop it,i get:
waiting for processes to exit
waiting for processes to exit
waiting for processes to exit
waiting for processes to exit
waiting for processes to exit
waiting for processes to exit
killing processes which didn’t stop after 20 seconds [ OK ]