About EasyMorph Tutorials & Examples Web-help

Query Server Log Like SQL Using Apache Drill!

Hi,

Thought this may be useful for anyone that needs to monitor EasyMorph Server and doesn’t necessarily want to trawl through the log file (or even log in to the server). This is also super useful for setting up a Tableau dashboard with an Alert (which is our use case)…

The information below is not comprehensive, and I am no Drill expert so, it is provided as-is.

First off, download and set up Apache Drill and its prerequisites as described on the project site:
Apache Drill Install Guidance

If you just want to prototype this, you can launch the drill executable found in %ChosenDrillDirectory%\bin\drill-embedded.bat - You can also configure this as a service using the Windows Scheduler that executes on machine startup as a hidden service (Google is your friend on this one).

Once the Drill service is up-and-running, you can navigate to http://%MachineName%:8047 - This is a handy web interface that allows you to set up ‘Drill Bits’ and test queries.

Click on the Storage tab located at the top of the interface, then under the Plugin Management heading, click the Create button

In The Storage Name field, use an appropriate name that would be appropriate to create a pseudo Schema - I chose easymorph.

In The Configuration field, paste the below and substitute the values between the %% signs:

{
  "type": "file",
  "connection": "file:///%EasyMorphServerDriveLetter%:/",
  "config": null,
  "workspaces": {
    "easymorph": {
      "location": "/%EasyMorphServerLogDirectoryWithoutDriveLetter%/",
      "writable": false,
      "defaultInputFormat": "easymorph-server",
      "allowAccessOutsideWorkspace": false
    }
  },
  "formats": {
    "easymorph-server": {
      "type": "logRegex",
      "regex": ".*(\\d{2}/\\d{2}/\\d{4})\\s(\\d{2}:\\d{2}:\\d{2})\\s{4}(\\d{6})\\s(\\w+)\\s+(.+)",
      "extension": "log",
      "maxErrors": 0,
      "schema": [
        {
          "fieldName": "Event_Date",
          "fieldType": "DATE",
          "format": "dd/MM/yyyy"
        },
        {
          "fieldName": "Event_Time",
          "fieldType": "TIME",
          "format": "HH:mm:ss"
        },
        {
          "fieldName": "Event_ID",
          "fieldType": "INT"
        },
        {
          "fieldName": "Message_Type"
        },
        {
          "fieldName": "Message"
        }
      ]
    }
  },
  "enabled": true
}

Here is my complete configuration file which can be used as a reference:

Configuration File: easymorph.txt (1.1 KB)

Click Create and hopefully, you will get no parse errors!!

Next navigate to the query tab to try your new creation!

An example query (take note of the quotation marks) is:

SELECT *

FROM easymorph.easymorph.`EasyMorphServer.log`

WHERE Message_Type = 'ERROR'

ORDER BY Event_Date DESC, Event_Time DESC

LIMIT 20;

Here is your Top 20 Errors from EasyMorph Server in Descending date and as up-to-date as the query run time!

Modern versions of Tableau Desktop have a built in Apache Drill connector which is plug-and-play with the above.

Enjoy!

1 Like

Excellent! Thank you!

To learn more about EasyMorph visit easymorph.com.