ES|QL demo

gistfile1.txt

// Before you start with anything, please consider visiting:
// https://www.elastic.co/guide/en/elasticsearch/reference/current/esql.html

PUT sample_data
{
  "mappings": {
    "properties": {
      "client_ip": {
        "type": "ip"
      },
      "message": {
        "type": "keyword"
      }
    }
  }
}

PUT sample_data/_bulk
{"index": {}}
{"@timestamp": "2023-10-23T12:15:03.360Z", "client_ip": "172.21.2.162", "message": "Connected to 10.1.0.3", "event_duration": 3450233}
{"index": {}}
{"@timestamp": "2023-10-23T12:27:28.948Z", "client_ip": "172.21.2.113", "message": "Connected to 10.1.0.2", "event_duration": 2764889}
{"index": {}}
{"@timestamp": "2023-10-23T13:33:34.937Z", "client_ip": "172.21.0.5", "message": "Disconnected", "event_duration": 1232382}
{"index": {}}
{"@timestamp": "2023-10-23T13:51:54.732Z", "client_ip": "172.21.3.15", "message": "Connection error", "event_duration": 725448}
{"index": {}}
{"@timestamp": "2023-10-23T13:52:55.015Z", "client_ip": "172.21.3.15", "message": "Connection error", "event_duration": 8268153}
{"index": {}}
{"@timestamp": "2023-10-23T13:53:55.832Z", "client_ip": "172.21.3.15", "message": "Connection error", "event_duration": 5033755}
{"index": {}}
{"@timestamp": "2023-10-23T13:55:01.543Z", "client_ip": "172.21.3.15", "message": "Connected to 10.1.0.1", "event_duration": 1756467}


PUT clientips
{
  "mappings": {
    "properties": {
      "client_ip": {
        "type": "keyword"
      },
      "env": {
        "type": "keyword"
      }
    }
  }
}

PUT clientips/_bulk
{ "index" : {}}
{ "client_ip": "172.21.0.5", "env": "Development" }
{ "index" : {}}
{ "client_ip": "172.21.2.113", "env": "QA" }
{ "index" : {}}
{ "client_ip": "172.21.2.162", "env": "QA" }
{ "index" : {}}
{ "client_ip": "172.21.3.15", "env": "Production" }
{ "index" : {}}
{ "client_ip": "172.21.3.16", "env": "Production" }

PUT /_enrich/policy/clientip_policy
{
  "match": {
    "indices": "clientips",
    "match_field": "client_ip",
    "enrich_fields": ["env"]
  }
}

PUT /_enrich/policy/clientip_policy/_execute?wait_for_completion=false

PUT clientips_lkp
{
  "settings": {
    "index":{
      "mode":"lookup"
    }
  },
  "mappings": {
    "properties": {
      "client_ip": {
        "type": "keyword"
      },
      "env": {
        "type": "keyword"
      }
    }
  }
}

PUT clientips_lkp/_bulk
{ "index" : {}}
{ "client_ip": "172.21.0.5", "env": "Development" }
{ "index" : {}}
{ "client_ip": "172.21.2.113", "env": "QA" }
{ "index" : {}}
{ "client_ip": "172.21.2.162", "env": "QA" }
{ "index" : {}}
{ "client_ip": "172.21.3.15", "env": "Production" }
{ "index" : {}}
{ "client_ip": "172.21.3.16", "env": "Production" }

/**
 * See the documentation for a complete list of
 * features; we'll focus on the concepts and some
 * interesting examples.
 * Some queries will (intentionally) result in errors.
 */
 

// Let's get started with the equivalent of GET /
// Cover output formats, case sensitivity, KEEP, LIMIT


POST _query
{
  "query": """
    SHOW INFO //Comment
  """
}

POST _query?format=csv
{
  "query": """
    SHOW INFO
  """
}
POST _query?format=txt
{
  "query": """
    SHOW INFO
  """
}

POST _query?format=txt
{
  "query": """
    show info //Are keywords case sensitive?
  """
}
POST _query?format=txt
{
  "query": """
    SHOW INFO
    | LIMIT 1 //Removing the warning
  """
}
POST _query?format=txt
{
  "query": """
    SHOW INFO
    | LIMIT 1
    | KEEP version //Reducing the output
  """
}
POST _query?format=txt
{
  "query": """
    /* Combining it all with a
     * multi-line comment */
    SHOW INFO
    | LIMIT 1
    | KEEP version
  """
}


// Where should you put the pipe?
POST _query?format=txt
{
  "query": """
    SHOW INFO | LIMIT 
    1 | KEEP 
    version
  """
}
POST _query?format=txt
{
  "query": """
    SHOW INFO |
    LIMIT 1 |
    KEEP version
  """
}
POST _query?format=txt
{
  "query": """
    SHOW INFO
    | LIMIT 1
    | KEEP version
  """
}

GET sample_data/_mapping

// Use some actual data
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | LIMIT 5
  """
}
GET _query?format=txt
{
  "query": """ //Can you use GET as well?
    FROM sample_data
    | LIMIT 5
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | LIMIT 5
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data METADATA _index, _id, _version, _score
    | LIMIT 5
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | KEEP @timestamp, message
    | LIMIT 10
  """
}


// A quick look at data types
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE client_ip = "172.21.2.162"
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE client_ip == "172.21.2.162"
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE client_ip == TO_IP("172.21.2.162")
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE client_ip IS NOT NULL
    | LIMIT 10
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE event_duration > 5000000
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE message LIKE "Connect*"
    | LIMIT 7
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | WHERE message RLIKE "Connect.*"
    | LIMIT 7
  """
}


// These are still in preview in 9.0, maybe don't use in prod
// For these add sample web logs after clicking Other sample data sets in 
// http://localhost:5601/app/home#/tutorial_directory/sampleData

POST _query?format=txt
{
  "query": """
    FROM kibana_sample_data_logs METADATA _score
      //| LIMIT 1000 // this won't work: 
      // https://www.elastic.co/docs/reference/query-languages/esql/functions-operators/search-functions#esql-match
    | WHERE MATCH(agent, "is Windows or Linux")
    | SORT @timestamp DESC 
    | KEEP _score, agent 
    | LIMIT 40
  """
}

POST _query?format=txt
{
  "query": """
    FROM kibana_sample_data_logs
    | WHERE qstr("(url:kibana) AND (machine.os:Linux)")
    | LIMIT 40
  """
}

POST _query?format=txt
{
  "query": """
    FROM kibana_sample_data_logs
    | WHERE QSTR("(url:kibana) AND (machine.os:win xp)")
    | KEEP url
    | LIMIT 40
  """
}

POST _query?format=txt
{
  "query": """
    FROM kibana_sample_data_logs
    | WHERE QSTR("(url:kibana) AND (machine.os:win xp)")
    | KEEP url
    | LIMIT 40
  """
}

POST _query?format=txt
{
  "query": """
    FROM kibana_sample_data_logs
    | WHERE KQL("tags:error")
    | KEEP tags, url, @timestamp, machine.os
    | LIMIT 40
  """
}

// End of preview


// And finally aggregations (with a query DSL example for comparison)
POST _query?format=csv
{
  "query": """
    FROM sample_data
    | keep message, client_ip, event_duration
    | STATS COUNT_DISTINCT(client_ip), AVG(event_duration)
    | LIMIT 1
  """
}
GET sample_data/_search
{
  "size": 0,
  "aggs": {
    "distinct_client-ip": {
      "cardinality": {
        "field": "client_ip"
      }
    },
    "average_event-duration": {
      "avg": {
        "field": "event_duration"
      }
    }
  }
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS AVG(event_duration), COUNT(*) BY client_ip
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS AVG(event_duration), COUNT(*) BY client_ip
    | SORT COUNT(*)
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS AVG(event_duration), mycount = COUNT(*) BY client_ip
    | SORT mycount
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS AVG(event_duration), COUNT(*) BY client_ip
    | SORT `count(*)` //Case sensitivity revisited — will this work?
    | LIMIT 10
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS AVG(event_duration), COUNT(*) BY client_ip
    | SORT `COUNT(*)` DESC
    | LIMIT 10
  """
}


// Precedural queries as a core concept
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS AVG(event_duration), COUNT(*) BY client_ip
    | LIMIT 5
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | LIMIT 5
    | STATS AVG(event_duration), COUNT(*) BY client_ip
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | LIMIT 5
    | STATS AVG(event_duration), COUNT(*) BY client_ip
    | SORT `COUNT(*)` DESC
    | LIMIT 1
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS average_duration = AVG(event_duration), count = COUNT(*) BY client_ip
    | STATS MAX(average_duration) BY count //A chained aggregation
    | LIMIT 5
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | STATS c = COUNT(*) BY bucket = BUCKET(@timestamp, 24, "2023-10-23T00:00:00Z", "2023-10-23T23:59:59Z")
    | LIMIT 5
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | KEEP @timestamp, event_duration
    | STATS median_duration = MEDIAN(event_duration) BY bucket = BUCKET(@timestamp, 24, "2023-10-23T00:00:00Z", "2023-10-23T23:59:59Z")
    | LIMIT 5
  """
}


// More language features, using ROW for sample data
POST _query?format=txt
{
  "query": """
    ROW a = "piotr.przybyl@elastic.co"
    | WHERE a RLIKE ".*@elastic.co"
    | KEEP a
  """
}

POST _query?format=txt
{
  "query": """
     ROW a = 1 | 
     EVAL b = a + "32"
  """
}
POST _query?format=txt
{
  "query": """
    ROW a = "2023-01-23T12:15:00.000Z - some text - 127.0.0.1"
    | DISSECT a "%{date} - %{msg} - %{ip}"
    //| KEEP date, msg, ip
    | DROP a
    | LIMIT 1
  """
}
POST _query?format=txt
{
  "query": """
    ROW a = "2023-01-23T12:15:00.000Z 127.0.0.1 some.email@foo.com 42"
    | GROK a "%{TIMESTAMP_ISO8601:date} %{IP:ip} %{EMAILADDRESS:email} %{NUMBER:num:int}"
    // Note the impact of switching KEEP and EVAL around
    | KEEP date, ip, email, num
    | EVAL date = DATE_EXTRACT("year", TO_DATETIME(date))
    | LIMIT 1
  """
}
POST _query?format=txt
{
  "query": """
    ROW first_name = "Piotr", last_name = "Przybył", height = 1.90
    | EVAL height_feet = height * 3.281, height_cm = height * 100
  """
}
POST _query?format=txt
{
  "query": """
    ROW height = 1.88, weight = 95
    | EVAL BMI = ROUND(weight / POW(height, 2), 1)
    | EVAL healthy = CASE(BMI < 18.5, false, BMI >= 25, false, true)
  """
}


// Multivalued (arrays) fields can be read but functions on them
// return null (unless otherwise noted in the docs)
POST _query?format=txt
{
  "query": """
    ROW a = 5, b = [ 1, 2 ]
    | EVAL a - 3, b + 2, a + b
    | LIMIT 1
  """
}


// Enrich your data
GET _enrich/policy/clientip_policy
POST _query?format=txt
{
  "query": """
    FROM clientips
    | LIMIT 5
  """
}
POST _query?format=txt
{
  "query": """
    FROM sample_data
    | KEEP @timestamp, client_ip, event_duration
    | EVAL client_ip = TO_STRING(client_ip)
    | ENRICH clientip_policy ON client_ip WITH env
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | KEEP @timestamp, client_ip, event_duration
    | EVAL client_ip = TO_STRING(client_ip)
    | ENRICH clientip_policy ON client_ip WITH env
    | STATS median_duration = MEDIAN(event_duration) BY env
  """
}

// Or go lookups
POST _query?format=txt
{
  "query": """
    FROM clientip_lkp
    | LIMIT 10
  """
}

POST _query?format=txt
{
  "query": """
    FROM sample_data
    | KEEP @timestamp, client_ip, event_duration
    | EVAL client_ip = TO_STRING(client_ip)
    | LOOKUP JOIN clientips_lkp ON client_ip
  """
}

// Parameters
// More at https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-rest.html#esql-rest-params

POST /_query?format=txt
{
  "params": ["172.21.0.5", "172.21.3.15", "172.21.2.162"],
  "query": """
    FROM sample_data
    | WHERE client_ip IN (?, ?, ?)
    | LIMIT 5
    """
}
POST /_query?format=txt
{
  "params": ["172.21.0.5", "172.21.3.15", "172.21.2.162"],
  "query": """
    FROM sample_data
    | WHERE client_ip IN (?2, ?1, ?3)
    | LIMIT 5
    """
}
POST /_query?format=txt
{
  "params": [{"one": "172.21.0.5"}, {"two": "172.21.3.15"}, {"three": "172.21.2.162"}],
  "query": """
    FROM sample_data
    | WHERE client_ip IN (?one, ?three, ?two)
    | LIMIT 5
    """
}