jkel.org

Sprache / Language

Manual for JkLog2Sql

  1. Supported log formats:
    NameExample lineSupported
    Common Log Format127.0.0.1 - admin [12/Jan/2016:16:43:48 +0100] "GET /index.php HTTP/1.1" 200 2500YES
    Combined Log Format
    (Apache default)
    127.0.0.1 - admin [12/Jan/2016:16:43:48 +0100] "GET /index.php HTTP/1.1" 200 2500 "http://www.jkel.org" "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:43.0) Gecko/20100101 Firefox/43.0"YES
    W3 Extended Log File Format
    (IIS default)
    16:43:48 GET /index.phpNO

  2. Installation:
    There is no setup program.
    So, just unzip JkLog2Sql.zip.

  3. Create database
    Create a new database in MySQL or MSSQL.
    Advice: Do NOT use a database from your web hosting, but install MySQL or MSSQL on your local PC.
    Both provide free versions for private users.

  4. Use JkLog2Sql (Windows Version)
    Start JkLog2Sql.Win.exe.
    JkLog2Sql Screenshot
    Usage should be self-explanatory. A tooltip shows up at important options.

  5. Use JkLog2Sql (Command line version)
    Start JkLog2Sql.Console.exe with the following parameters (all 3 required, sequence doesn't matter).
    ParameterMeaning
    iInput file path. Can be .log file or archive (zip, gz, 7z).
    oPath of SQL output file
    dDatabase type. One of the following:
    • MySQL
    • MSSQL_yyyyMMdd
    • MSSQL_yyyyddMM

    Example:
    JkLog2Sql.Console -i example.log -o example.sql -d MSSQL_yyyyddMM

    With the console version, you can only convert one file at a time.
    For multiple files, use an archive as input or call JkLog2Sql several times.

  6. Import SQL data into database
    JkLog2Sql.Win.exe creates several files in the output folder:
    JkLog2Sql Dateien
    First import 01-create-table-logfiles.*.sql into the database you had created in step 3.
    Afterwards all other SQL files.

    JkLog2Sql.Console.exe creates only the file specified by the parameter.

    Tip: With the application HeidiSQL you can import many SQL files at once!

  7. Do analyses
    Now you can analyze the log files with all the possibilities of SQL.
    In the JkLog2Sql.zip, the folder sample-queries contains some examples.

  8. Explanation of the columns
    ColumnMeaningExample
    OriginalLineThe original line from the log file.127.0.0.1 - admin [12/Jan/2016:16:43:48 +0100] "GET /pages/en/web-tools/jklog2sql/manual.php?what=ever HTTP/1.1" 200 2500 "http://www.jkel.org/index.php" "Mozilla/5.0 (Windows NT 10.0; WOW64; rv:43.0) Gecko/20100101 Firefox/43.0"
    IPIP of visitor127.0.0.1
    IdentIn most cases empty.
    UserIdIf user is logged in via htaccess, the username.admin
    DateTimeStringDate of request as text, as in original line12/Jan/2016:16:43:48 +0100
    DateTimeRealDate of request in DateTime format of database2016-01-12 16:43:48
    RequestMethode.g. GET, POSTGET
    RequestThe request. Original urlencoded, as in log file.GET /pages/de/web-tools/jklog2sql/manual.php?what=ever HTTP/1.1
    RequestedPathRequested URL (folder and filename). NOT urlencoded. WITH query string./pages/en/web-tools/jklog2sql/manual.php?what=ever
    RequestedPathWithoutQueryRequested URL (folder and filename). NOT urlencoded. WITHOUT query string./pages/en/web-tools/jklog2sql/manual.php
    RequestedFolderRequested URL (ONLY folder). NOT urlencoded./pages/en/web-tools/jklog2sql
    RequestedFileRequested file (ONLY filename). NOT urlencoded. WITHOUT query string.manual.php
    RequestedFileExtensionRequested file (ONLY extension, without dot).php
    QueryStringquery string.what=ever
    HttpVersionHttp versionHTTP/1.1
    Status-Codee.g. "200", "404"200
    BytesTransferred bytes of request2500
    RefererReferer (referencing URL)
    "http://spam/", if the original referer occurs in referer spam list
    http://www.jkel.org/index.php
    RefererDomainReferer (referencing URL) (ONLY domain)
    "SPAM", if the original referer occurs in referer spam list
    www.jkel.org
    RefererSearchEngineReferer (referencing URL) (IF search engine, its name)Google
    RefererSearchTermReferer (referencing URL) (IF search engine, search term)
    Info: some search engines obfuscate the search term. In this case it is empty here.
    jkel
    UserAgentUser Agent, as in original lineMozilla/5.0 (Windows NT 10.0; WOW64; rv:43.0) Gecko/20100101 Firefox/43.0
    UserAgentFamilyName of browser.Firefox
    UserAgentMajorVersionMajor version of browser.43
    UserAgentMinorVersionMinor version of browser.0
    OsFamilyName of operating systemWindows 10
    OsMajorVersionMajor version of operating system.
    Not filled at Windows.
    0
    OsMinorVersionMinor version of operating system.0
    DeviceFamilyName of device (only at mobile/tablet)
    For search engine bots, the text is "Spider".
    Other
    VisitorIdIf this value is same for two log lines, it is assumed, to be both of the same visitor.
    (same IP, same day, same browser)
    908C1E633375E2AD3B77C631928E0DFE