« Previous Next »

Thread: Replacing URL (request) with Friendly Names??

Last post 05-31-2007 12:03 PM by LogParser User : Greg C. 1 replies.

Average Rating Rate It (5)

RSS

Page 1 of 1 (2 items)

Sort Posts:

Shortcuts

Search | Active Posts | Unanswered Posts | View all users

  • 05-30-2007, 1:38 PM

    Replacing URL (request) with Friendly Names??

    Reply Contact

    Hi,

    I'm using LogParser to import an Apache web server log into an MS SQL server db. I can't figure out how to clean up the URL's at the same time...

    My command:
    LogParser "SELECT RemoteHostName, DateTime, Request, StatusCode, BytesSent INTO MyTable FROM D:\IBMHTTPServer6\logs\access.log" -i:NCSA -o:SQL -server:MyServer -database:MyDB -driver:"SQL Server" -username:xxxx -password:xxxx -createTableN -iCheckPoint:\web\weblog.lpc

    The input referer field looks like:

    GET /portal/site/marketintel/menuitem.13d8b98ff8e164fca120231020248a0c/ HTTP/1.1
    GET /portal/site/marketintel/menuitem.d6831dc3617f54fca120231020248a0c/ HTTP/1.1
    GET /portal/site/marketintel/menuitem.45dc4e7d2e1e54fca120231020248a0c/ HTTP/1.1
    GET /portal/site/marketintel/menuitem.6dc3d475ebbc54fca120231020248a0c/ HTTP/1.1

    I want to replace the first (...13d8b...) with "Home" and the next (...d683...) with "News Page", so that the referer field in the DB looks like:

    Home
    News Page
    Reseach Page
    About

    I figured out how to use REPLACE_STR but only for one 'url/page'...

    Thanks!

    Greg
    Tags:

  • 05-31-2007, 12:03 PM In reply to

    RE: Replacing URL (request) with Friendly Names??

    Mark as Answer
    Reply Contact

    FYI: Found the answer on SQL forums - This works...:

    select remotehostname, datetime,
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(
    replace(request,'GET /portal/site/marketintel/menuitem.6dc3d475ebbc54fca120231020248a0c/ HTTP/1.1','Home')
    , 'GET /portal/site/marketintel/menuitem.45dc4e7d2e1e54fca120231020248a0c/ HTTP/1.1','News')
    , 'GET /portal/site/marketintel/menuitem.d6831dc3617f54fca120231020248a0c/ HTTP/1.1','Research')
    , 'GET /portal/site/marketintel/menuitem.13d8b98ff8e164fca120231020248a0c/ HTTP/1.1','Case Studies')
    , 'GET /portal/site/marketintel/template.PAGE/menuitem.45dc4e7d2e1e54fca120231020248a0c/?addCatFilter=','News -Filter:')
    , 'GET /portal/site/marketintel/template.PAGE/menuitem.13d8b98ff8e164fca120231020248a0c/?addCatFilter=','Case Studies-Filter:')
    , 'GET /portal/site/marketintel/template.PAGE/menuitem.d6831dc3617f54fca120231020248a0c/?addCatFilter=','Research-Filter:')
    , '&removeCatFilter=&moreCatList=&removeSearchText=&pastDays=10000&goingBack=10000&sources=0&industries=0 HTTP/1.1','')
    , statuscode, bytessent
    from.....
    Tags:
Page 1 of 1 (2 items)
Microsoft Communities