YQL table for Apache access logs

Question

YQL SHOW TABLES has CSV and HTML. What about a table for Apache access logs?

Answer 1

Apache logs actually have a customizable format so I'm assuming that you mean the common log format or one of th defaults. If we add something like this it will likely be with a regex based line reader that you could then apply to apache logs. Thanks for the suggestion.

Answer 2

There is now a regex table

http://developer.yahoo.com/yql/console/?q=select%20*%20from%20regex%20where%20expression%20%3D%20%22%28.*%29%22%20and%20text%3D%22test%22&env=http%3A%2F%2Fdatatables.org%2Falltables.env

If you have a regex for your log format, you can use that table to parse it.

Answer 3

Here's the start of a common log parsing table. The code as-is will blindly split on empty spaces, which isn't accurate, but it's a start. You'd probably want to pass in the url of the log file, split the entries on newline, and then parse each line.

<?xml version="1.0" encoding="UTF-8"?>
<table xmlns="http://query.yahooapis.com/v1/schema/table.xsd"&gt;
    <meta>
        <author></author>
        <sampleQuery>select * from {table}</sampleQuery>
    </meta>
    <bindings>
        <select itemPath="" produces="XML">
            <inputs>
                <key id="url" type="xs:string" paramType="variable"/>
            </inputs>
            <execute><![CDATA[

                    //http://en.wikipedia.org/wiki/Common_Log_Format
            var entry = '208.240.243.170 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326';

            var names = ['IP', 'RFC 1413', 'userid', 'date', 'request', 'status', 'size'];
            var values = entry.split(' ');

            var resp = {};

            for (var i in names) {
                var name = names[i];
                resp[name] = values[i];
            }

            response.object = resp;

      ]]></execute>
        </select>
    </bindings>
</table>

You can run it like this: use "http://{your domain}/table.xml" as table; select * from table

You could then extend it look up geo data by ip: use "http://{your domain}/table.xml" as table; select * from pidgets.geoip where ip in (select IP from table)