Elasticsearch SQL Example with Apache Log for Webinar

elasticsearch-sql-examples.js

##################################################
# Elasticsearch SQL Example with Apache Log
##################################################

#
# Operations
#

POST _xpack/sql
{
  "query": "SHOW tables"
}

POST _xpack/sql
{
  "query": "SHOW functions"
}

POST _xpack/sql
{
  "query": "DESCRIBE apache"
}

POST _xpack/sql?format=yaml
{
  "query": "DESC apache"
}

#
# Observing the dataset
#

POST _xpack/sql?format=txt
{
  "query": "SELECT count(*) AS c FROM apache"
}

POST _xpack/sql?format=txt
{
  "query": "SELECT \"@timestamp\" AS t FROM apache ORDER BY t ASC LIMIT 1"
}

POST _xpack/sql?format=txt
{
  "query": "SELECT \"@timestamp\" AS t FROM apache ORDER BY t DESC LIMIT 1"
}

POST _xpack/sql?format=txt
{
  "query": "SELECT count(*) AS c FROM apache WHERE \"@timestamp\" > '2014-09-25T00:00:00.000Z'"
}

# Use cursor
# Note: `LIMIT` will not work as `fetch_size`

POST _xpack/sql?format=json
{
  "query": "SELECT \"@timestamp\" AS t, clientip, request FROM apache ORDER BY t ASC",
  "fetch_size": 5
}

POST _xpack/sql?format=json
{
  "cursor": ""
}

POST _xpack/sql/close?format=json
{
  "cursor": ""
}

#
# Where are they from?
#

POST _xpack/sql?format=txt
{
  "query": "SELECT geoip.country_name FROM apache"
}

# Note: SELECT DISTINCT not yet supported
POST _xpack/sql?format=txt
{
  "query": "SELECT COUNT(DISTINCT geoip.country_name) FROM apache"
}

# Per country name
POST _xpack/sql?format=txt
{
  "query": "SELECT geoip.country_name, count(*) FROM apache GROUP BY geoip.country_name"
}

# Limit by frequency
POST _xpack/sql?format=txt
{
  "query": "SELECT geoip.country_name, count(*) FROM apache GROUP BY geoip.country_name HAVING COUNT(*) > 1000"
}

# Limit to Asia by the continent code
POST _xpack/sql?format=txt
{
  "query": "SELECT geoip.country_name, count(*) FROM apache WHERE geoip.continent_code = 'AS' GROUP BY geoip.country_name"
}

#
# How many of them?
#

POST _xpack/sql?format=txt
{
  "query": "SELECT COUNT(DISTINCT clientip.raw) FROM apache"
}

# How often?
POST _xpack/sql?format=txt
{
  "query": "SELECT clientip, COUNT(*) FROM apache GROUP BY clientip HAVING COUNT(*) > 10"
}

# The below returns an error as the composite aggregation does not support ordering by anything other than the term
POST _xpack/sql?format=txt
{
  "query": "SELECT clientip, COUNT(*) AS c FROM apache  GROUP BY clientip ORDER BY c"
}

#
# Do some analysis
#

# Response codes
POST _xpack/sql?format=txt
{
  "query": "SELECT response, COUNT(*) FROM apache GROUP BY response"
}

# Response codes
POST _xpack/sql?format=txt
{
  "query": "SELECT verb, COUNT(*) FROM apache GROUP BY verb"
}

# Operating systems
POST _xpack/sql?format=txt
{
  "query": "SELECT useragent.os, COUNT(*) FROM apache GROUP BY useragent.os"
}

# Broswer or agent
POST _xpack/sql?format=txt
{
  "query": "SELECT useragent.name, COUNT(*) FROM apache GROUP BY useragent.name"
}

# The following does not work. Single expresion can be used with GROUP BY.
POST _xpack/sql?format=txt
{
  "query": "SELECT useragent.os, useragent.name, COUNT(*) FROM apache GROUP BY useragent.os, useragent.name"
}

# How much did they download?
POST _xpack/sql?format=txt
{
  "query": "SELECT clientip, SUM(bytes) FROM apache GROUP BY clientip"
}

# in KB
POST _xpack/sql?format=txt
{
  "query": "SELECT ROUND(AVG(bytes) / 1024) AS \"AVG (KB)\", ROUND(MAX(bytes) / 1024) AS \"MAX (KB)\", ROUND(PERCENTILE(bytes, 95) / 1024) AS \"Percentile (KB)\" FROM apache"
}

# KURT - Try vertical bar chart
POST _xpack/sql?format=txt
{
  "query": "SELECT KURTOSIS(bytes) FROM apache"
}

#
# Do some full text searches
#

# Query String Query

POST _xpack/sql?format=txt
{
  "query": "SELECT request, count(*) from apache WHERE request LIKE '%log' GROUP BY request"
}

POST _xpack/sql?format=txt
{
  "query": "SELECT SCORE(), agent FROM apache WHERE QUERY('chrome linux', 'default_field=agent', 'default_operator=or') ORDER BY SCORE() DESC"
}

# Multi Match Query
# Try default_operator=and
POST _xpack/sql?format=txt
{
  "query": "SELECT SCORE(), agent FROM apache WHERE MATCH('agent', 'chrome linux', 'operator=and') ORDER BY SCORE() DESC"
}

# Filter GROUP BY with QUERY/MATCH
POST _xpack/sql?format=txt
{
  "query": "SELECT useragent.os, COUNT(*) FROM apache WHERE MATCH('agent', 'chrome linux', 'operator=and') GROUP BY useragent.os"
}

#
# Miscs
#

# Wildcard vs alias

POST _xpack/sql?format=txt
{
  "query": "SELECT useragent.name, COUNT(*) FROM \"apache-*\" GROUP BY useragent.name"
}

GET _cat/aliases?v&s=alias