Insert Log file into a MySQL database
WMPTalk.com Forum Index WMPTalk.com
Discuss Windows Media Player
 
 FAQFAQ   MemberlistMemberlist     RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
 
Google
 
Web wmptalk.com
Insert Log file into a MySQL database

 
Post new topic   Reply to topic    WMPTalk.com Forum Index -> Windows Media Server
Author Message
graham
Guest
Posted: Sat Dec 17, 2005 9:30 pm    Post subject: Insert Log file into a MySQL database Reply with quote
Is there a pre-existing script/method that will insert a wms log file into a
remote mysql database ?
Normally, I use PHP to make this happen..but am a bit unclear on how to do
this with Windows Media Server.

Essentially, I'd like to insert a new WMS log record for every windows media
movie request

can someone point me in the right direction ?

many thanks

g

Back to top
Neil Smith [MVP Digital M
Guest
Posted: Mon Dec 19, 2005 1:30 am    Post subject: Re: Insert Log file into a MySQL database Reply with quote
On Sat, 17 Dec 2005 11:52:02 -0800, graham
<graham@discussions.microsoft.com> wrote:

Quote:
Is there a pre-existing script/method that will insert a wms log file into a
remote mysql database ?
Normally, I use PHP to make this happen..but am a bit unclear on how to do
this with Windows Media Server.

Essentially, I'd like to insert a new WMS log record for every windows media
movie request

can someone point me in the right direction ?

You still can do this with PHP - Add this element to your ASX file for
the broadcast :

<LOGURL href =
"http://my.server.com/logscript.php?param1=abc&param2=def" />

Look at the $_GET requests coming from the players, as well as the
user agent etc.

Add that line to each individual <ENTRY /> element in the ASX instead
if you want a separate logging hit for each item of your playlist.

More here :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmplay10/mmp_sdk/logurlelement.asp

Cheers - Neil
Back to top
graham
Guest
Posted: Tue Dec 20, 2005 9:30 am    Post subject: Re: Insert Log file into a MySQL database Reply with quote
wow :)
thanks Neil

will check it out and report back

g


"Neil Smith [MVP Digital Media]" wrote:

Quote:
On Sat, 17 Dec 2005 11:52:02 -0800, graham
graham@discussions.microsoft.com> wrote:

Is there a pre-existing script/method that will insert a wms log file into a
remote mysql database ?
Normally, I use PHP to make this happen..but am a bit unclear on how to do
this with Windows Media Server.

Essentially, I'd like to insert a new WMS log record for every windows media
movie request

can someone point me in the right direction ?

You still can do this with PHP - Add this element to your ASX file for
the broadcast :

LOGURL href =
"http://my.server.com/logscript.php?param1=abc&param2=def" /

Look at the $_GET requests coming from the players, as well as the
user agent etc.

Add that line to each individual <ENTRY /> element in the ASX instead
if you want a separate logging hit for each item of your playlist.

More here :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmplay10/mmp_sdk/logurlelement.asp

Cheers - Neil





Back to top
graham
Guest
Posted: Wed Dec 21, 2005 1:30 am    Post subject: Re: Insert Log file into a MySQL database Reply with quote
I tried the below...
But I get the error:
Can Not Play Back the File
The File Format is invalid

Is this some kind of mime issue ?
The movie plays back fine if I access the 'hard-coded' ASX file directly.

Here is the php script I am accessing from the browser

many thanks
g

<?php

$asx= "";
loopThruGetVars();
echoASX();




//----------------------------------------------------------------
// Loop Through GET VARS

Function loopThruGETVars()
{
global $asxVars;
foreach ($_GET as $key => $value) {
$asxVars .= "$key $value"."\n";
$getArray[] = $key ;
}
}

Function echoASX(){
global $asxVars;
$asxFile =<<<EOB
<ASX VERSION="3.0">
<TITLE>$asxVars</TITLE>
<LOGURL HREF="http://www.siren.cc/asx/logurl.php"/>
<ENTRY>
<REF href="mms://video.ggl.com/General/E3cut1.wmv" />
</ASX>
EOB;

header('Content-Type: video/asx');
header ("Content-Length:".strlen($asxFile));
echo $asxFile;
}


Function sendMail(){
// Send
//mail('grahamanderson@ggl.com', 'ASX Variables', $asx);
// Send Again
//mail('grahamanderson@ggl.com', 'ASX Variables', "this is a test");
}


?>
Back to top
Troels Hansen
Guest
Posted: Wed Dec 21, 2005 9:30 am    Post subject: Re: Insert Log file into a MySQL database Reply with quote
graham wrote:

Quote:
header('Content-Type: video/asx');

Try this header. It works for me.
Header ("Content-Type: video/x-ms-asf");

Secondly, I'm not sure, but I have found it to be a good idear to add
\r\n at every newline. Especially Win98 seems to dislike unbroken lines
in the asx file.
Back to top
jmstolting
Guest
Posted: Wed Dec 21, 2005 9:30 am    Post subject: Re: Insert Log file into a MySQL database Reply with quote
Graham, I think you need to close your <ENTRY> tag to make it work..
like this:
<ENTRY>
<REF href="mms://video.ggl.com/General/E3cut1.wmv" />
</ENTRY>

apart from that, I have an issue myself, in that whatever I try my PHP
$_GET array never seems to get populated with the variables from the
client. My log script is called, but (in the above example), my
$asxVars variable remains empty..

can someone point me in the right direction, 'cause this is starting to
drive me crazy.. (using php 5.0, out-of-the-box fresh install)

thanks,
jms


--
jmstolting
------------------------------------------------------------------------
jmstolting's Profile: http://forums.techarena.in/member.php?userid=12456
View this thread: http://forums.techarena.in/showthread.php?t=424832
Free Computer Help - http://forums.techarena.in
Back to top
jmstolting
Guest
Posted: Wed Dec 21, 2005 1:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
I am using the $_GET array, but..

..I was under the impression that just using the LOGURL tag in the asx
would prompt the player to send it's log data, but considering your
mentioning of a query string, I may have been wrong.. what would such a
query look like?

I found out that the wmplayer will perform a POST aswell if I return
the same html that the MS wmsiislog.dll does, but still no luck
accessing the log variables.


--
jmstolting
------------------------------------------------------------------------
jmstolting's Profile: http://forums.techarena.in/member.php?userid=12456
View this thread: http://forums.techarena.in/showthread.php?t=424832
Free Computer Help - http://forums.techarena.in
Back to top
Neil Smith [MVP Digital M
Guest
Posted: Wed Dec 21, 2005 1:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
On Wed, 21 Dec 2005 14:47:36 +0530, jmstolting
<jmstolting.20duzl@DoNotSpam.com> wrote:

Quote:

Graham, I think you need to close your <ENTRY> tag to make it work..
like this:
ENTRY
REF href="mms://video.ggl.com/General/E3cut1.wmv" /
/ENTRY

apart from that, I have an issue myself, in that whatever I try my PHP
$_GET array never seems to get populated with the variables from the
client. My log script is called, but (in the above example), my
$asxVars variable remains empty..

The LOGURL used by Graham didn't send any query string, so there will
be nothing in the $_GET array. What does your LOGURL look like ?

Quote:
can someone point me in the right direction, 'cause this is starting to
drive me crazy.. (using php 5.0, out-of-the-box fresh install)

Just to confirm, you're using $_GET to access the query string, not
trying $HTTP_GET_VARS global ? That went away in PHP5 (thankfully).

Cheers - Neil
Back to top
Neil Smith [MVP Digital M
Guest
Posted: Wed Dec 21, 2005 1:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
On Tue, 20 Dec 2005 15:29:02 -0800, graham
<graham@discussions.microsoft.com> wrote:

Quote:
I tried the below...
But I get the error:
Can Not Play Back the File
The File Format is invalid
Is this some kind of mime issue ?


Yes, your script needs to send the header as detailed by Troles Hansen
in this newsgroup thread.

Also, your $_GETarray is *not* populated using the code below, because
your LOGURL is just making a page request to logurl.php and not
actually passing up any GET variables in the query string.

One thing I'd add is a series of proxy headers to prevent cacheing, as
well as an expiry time in the past to ensure the browser passes up the
most recently grabbed ASX file.

Cheers - Neil

Quote:
The movie plays back fine if I access the 'hard-coded' ASX file directly.

Here is the php script I am accessing from the browser

many thanks
g

?php

$asx= "";
loopThruGetVars();
echoASX();




//----------------------------------------------------------------
// Loop Through GET VARS

Function loopThruGETVars()
{
global $asxVars;
foreach ($_GET as $key => $value) {
$asxVars .= "$key $value"."\n";
$getArray[] = $key ;
}
}

Function echoASX(){
global $asxVars;
$asxFile =<<<EOB
ASX VERSION="3.0"
TITLE>$asxVars</TITLE
LOGURL HREF="http://www.siren.cc/asx/logurl.php"/
ENTRY
REF href="mms://video.ggl.com/General/E3cut1.wmv" /
/ASX
EOB;

header('Content-Type: video/asx');
header ("Content-Length:".strlen($asxFile));
echo $asxFile;
}


Function sendMail(){
// Send
//mail('grahamanderson@ggl.com', 'ASX Variables', $asx);
// Send Again
//mail('grahamanderson@ggl.com', 'ASX Variables', "this is a test");
}


?
Back to top
jmstolting
Guest
Posted: Wed Dec 21, 2005 5:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
after having put some more thought in the issue above..

I suspect you mean the custom parameters in the URL? In that case, I
get your drift, and am able to obtain my own GET variables.

what I am looking for, however, is to obtain the wmplayer log
variables, as get sent to the wmsiislog.dll when I use that DLL for
logging, such as

c-ip date
time
c-dns
cs-uri-stem

etc..

my apache log shows a POST which presumably contains that data, but I
haven't been able to obtain the actual parameters in PHP..

thanks for your help!


--
jmstolting
------------------------------------------------------------------------
jmstolting's Profile: http://forums.techarena.in/member.php?userid=12456
View this thread: http://forums.techarena.in/showthread.php?t=424832
Free Computer Help - http://forums.techarena.in
Back to top
graham
Guest
Posted: Wed Dec 21, 2005 9:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
Ok, I think I understand now.
I need a script that can automatically parse the WMS log file...like a
WMSLog.pl
is there some preexisting perl/ cgi script I can use to accomplish this ?
Essentially, I need the whole WMS log file.
Am I going to need to install Apache on each media server to access the cgi
script?


many thanks for all the help so far...
g


so this....
<LOGURL HREF="http://www.ggl.com/graham/logurl.php?cmd=mailLogVars" />
should be changed to this....
<LOGURL HREF="http://www.ggl.com/WMSLog.pl" />


Here is the script so far:
<?php

if(isset($_GET['cmd']))
{
global $asxVars;

loopThruGETVars();
sendMail();

}else{
loopThruGetVars();
buildASX();

}

//----------------------------------------------------------------
// Loop Through GET VARS

Function loopThruGETVars(){
global $asxVars;
foreach ($_GET as $key => $value) {
$asxVars .= "$key $value"."\n";
$getArray[] = $key ;
}
//print "this is the array of GET variables: ".$asx;
}


//----------------------------------------------------------------
//Build the ASX File

Function buildASX(){
global $asxVars;
$asxFile =<<<EOB
<ASX VERSION="3.0">
<TITLE>Dynamic ASX File Test</TITLE>
<LOGURL HREF="http://www.ggl.com/graham/logurl.php?cmd=mailLogVars" />
<ENTRY>
<REF href="mms://video.ggl.com/General/E3cut1.wmv" />
</ENTRY>
</ASX>
EOB;

// Output the ASX file
header('Content-Type: video/x-ms-asf');
header ("Content-Length:".strlen($asxFile));
echo $asxFile;
}

//----------------------------------------------------------------
//Send an email of all the WMS log variables for the movie
Function sendMail(){
global $asxVars;
// Send
mail('grahamanderson@ggl.com', 'ASX Variables', $asxVars);
// Send Again
//mail('grahamanderson@ggl.com', 'ASX Variables', "this is a test");
}


?>
Back to top
Neil Smith [MVP Digital M
Guest
Posted: Wed Dec 21, 2005 9:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
On Wed, 21 Dec 2005 19:59:08 +0530, jmstolting
<jmstolting.20ebnp@DoNotSpam.com> wrote:

Quote:

after having put some more thought in the issue above..

I suspect you mean the custom parameters in the URL? In that case, I
get your drift, and am able to obtain my own GET variables.

what I am looking for, however, is to obtain the wmplayer log
variables, as get sent to the wmsiislog.dll when I use that DLL for
logging, such as

c-ip date
time
c-dns
cs-uri-stem

Ah I see. Well, it's probably worth (if you haven't already) digesting
this rather long documentation to fully appreciate which parts of the
logs come from where.

http://www.microsoft.com/windows/windowsmedia/howto/articles/LoggingModel.aspx

Some are server-generated, and some are sent by the client. The fields
above do seem to be created by the server at connection time (see
"Start of the Connection" section of the documentation).

c-dns apparently is always blank to hide the client's DNS settings,
and c-ip might get pulled by the server for WMP8 or earlier. Don't
know why, in both these cases ;-)

You probably >want< cs-url rather than cs-uri-stem, as you might want
to recover the entire query string which was split off when moving
from WMS4.1 to WMS9. So in WMS9, only the URI and not the query string
is reported in cs-uri-stem : See "Fields That Changed in Windows Media
Services 9 Series".

Quote:

my apache log shows a POST which presumably contains that data, but I
haven't been able to obtain the actual parameters in PHP..

OK I guess I wonder how you're recording the POST data. Some log
formats can be XML data, so if you're looking at that in a browser,
any content in non-HTML tags will probably not appear (the non-HTML
nodes aren't displayed if you echo that into a <body /> tag)

Supposing you set up your PHP script like this :

<?php
define("PATH_TO_LOG_FILE", "/var/tmp/wmplog.txt");
$log_output = var_export($_POST, true);
$fp = fopen(PATH_TO_LOG_FILE, "a+");
fwrite($fp, $log_output);
fclose($fp);
?>

Do you see anything (including a file being created) and any data in
the file ? What happens if you var_export $_SERVER instead - you
should see *something* from those client requests.

If you can't get any result I'll try to have a go over the weekend,
once the mince pies and bubbly has settled down a bit.

HTH
Cheers - Neil
Back to top
Neil Smith [MVP Digital M
Guest
Posted: Thu Dec 22, 2005 1:30 pm    Post subject: Re: Insert Log file into a MySQL database Reply with quote
On Wed, 21 Dec 2005 12:50:05 -0800, graham
<graham@discussions.microsoft.com> wrote:

Quote:
Ok, I think I understand now.
I need a script that can automatically parse the WMS log file...like a
WMSLog.pl

Well, if you know perl, I guess sure - why not. You could look at
these documents for some ideas :

http://www.microsoft.com/downloads/details.aspx?FamilyID=09dfc342-648b-4119-b7eb-783b0f7d1178&DisplayLang=en

Quote:
is there some preexisting perl/ cgi script I can use to accomplish this ?

http://www.windowsitpro.com/WindowsScripting/Article/ArticleID/43757/43757.html

Quote:
Essentially, I need the whole WMS log file.

Well, the script below won't give you that. It will be sending logurl-
specified logging parameters to http://www.ggl.com/graham/logurl.php
rather than (or as well as) to the media server - maybe I'm
misunderstanding your intent.

What do you actually *need* to do, and what is a "nice to have" ?

Quote:
Am I going to need to install Apache on each media server to access the cgi
script?

I don't see why ? Surely the operation of the perl script depends on
your plans. If you *plan* to dump the data to a MySQL server, say on
an hourly basis - why would Apache have to come into this ? Your perl
script would just connect to your specified MySQL server and send it's
data or run the query inserts (though I'd recommend a bulk insert)

Cheers - Neil
Back to top
 
Post new topic   Reply to topic    WMPTalk.com Forum Index -> Windows Media Server All times are GMT
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Microsoft Office Forum New Topics
Powered by phpBB