Play around with ES|QL in Elasticsearch

access_log

127.0.0.1 - frank [10/Oct/2024:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326
127.0.0.1 - john [10/Oct/2024:14:00:10 -0700] "POST /login HTTP/1.1" 302 512
192.168.0.1 - mary [10/Oct/2024:14:01:55 -0700] "GET /dashboard HTTP/1.1" 200 1034
203.0.113.1 - jane [10/Oct/2024:14:03:22 -0700] "GET /products HTTP/1.1" 200 2048
127.0.0.1 - frank [10/Oct/2024:14:04:45 -0700] "GET /logout HTTP/1.0" 200 324
203.0.113.3 - mary [10/Oct/2024:14:10:13 -0700] "POST /cart/add HTTP/1.1" 201 256
192.168.0.2 - sam [10/Oct/2024:14:15:40 -0700] "GET /profile HTTP/1.1" 200 875
127.0.0.1 - frank [10/Oct/2024:14:18:10 -0700] "GET / HTTP/1.0" 200 1280
198.51.100.4 - john [10/Oct/2024:14:20:04 -0700] "POST /checkout HTTP/1.1" 500 132
127.0.0.1 - jane [10/Oct/2024:14:25:55 -0700] "GET /images/logo.png HTTP/1.0" 200 1502
203.0.113.5 - frank [10/Oct/2024:14:30:25 -0700] "GET /contact HTTP/1.1" 404 512
192.168.0.3 - john [10/Oct/2024:14:32:00 -0700] "GET /pricing HTTP/1.1" 200 2187
127.0.0.1 - sam [10/Oct/2024:14:35:11 -0700] "POST /login HTTP/1.0" 401 254
203.0.113.6 - jane [10/Oct/2024:14:40:30 -0700] "GET /support HTTP/1.1" 200 1908
192.168.0.4 - mary [10/Oct/2024:14:45:12 -0700] "GET /faq HTTP/1.1" 200 1224
127.0.0.1 - frank [10/Oct/2024:14:50:45 -0700] "GET /blog HTTP/1.0" 200 3432
198.51.100.5 - sam [10/Oct/2024:14:55:29 -0700] "POST /newsletter HTTP/1.1" 201 332
203.0.113.7 - john [10/Oct/2024:15:00:12 -0700] "GET /about HTTP/1.1" 200 1308
127.0.0.1 - mary [10/Oct/2024:15:02:45 -0700] "GET /careers HTTP/1.0" 200 2789
192.168.0.5 - frank [10/Oct/2024:15:05:11 -0700] "POST /feedback HTTP/1.1" 200 415
203.0.113.8 - jane [10/Oct/2024:15:07:30 -0700] "GET / HTTP/1.1" 200 1456
192.168.0.6 - sam [10/Oct/2024:15:10:55 -0700] "GET /terms HTTP/1.1" 200 2154
127.0.0.1 - john [10/Oct/2024:15:15:22 -0700] "GET /privacy HTTP/1.0" 200 1320
203.0.113.9 - mary [10/Oct/2024:15:20:13 -0700] "GET /search?q=test HTTP/1.1" 200 908
192.168.0.7 - frank [10/Oct/2024:15:25:09 -0700] "POST /contact HTTP/1.1" 200 355
127.0.0.1 - jane [10/Oct/2024:15:30:45 -0700] "GET /products/123 HTTP/1.0" 200 1760
198.51.100.6 - sam [10/Oct/2024:15:35:55 -0700] "GET /blog/page/2 HTTP/1.1" 200 2400
192.168.0.8 - john [10/Oct/2024:15:40:00 -0700] "GET /images/banner.jpg HTTP/1.1" 200 5096
203.0.113.10 - mary [10/Oct/2024:15:42:30 -0700] "GET /signup HTTP/1.1" 200 278
127.0.0.1 - frank [10/Oct/2024:15:45:10 -0700] "POST /register HTTP/1.0" 201 512
192.168.0.9 - jane [10/Oct/2024:15:50:45 -0700] "GET /docs HTTP/1.1" 200 1244
203.0.113.11 - sam [10/Oct/2024:15:52:11 -0700] "GET /forum HTTP/1.1" 200 1967
127.0.0.1 - john [10/Oct/2024:15:55:09 -0700] "POST /forum/new HTTP/1.0" 403 623
198.51.100.7 - mary [10/Oct/2024:16:00:05 -0700] "GET /categories HTTP/1.1" 200 1536
203.0.113.12 - frank [10/Oct/2024:16:02:45 -0700] "GET /products/456 HTTP/1.1" 404 1024
192.168.0.10 - jane [10/Oct/2024:16:05:35 -0700] "POST /cart/remove HTTP/1.1" 200 154
127.0.0.1 - sam [10/Oct/2024:16:10:45 -0700] "GET /special-offers HTTP/1.0" 200 2000
203.0.113.13 - john [10/Oct/2024:16:15:10 -0700] "GET /downloads HTTP/1.1" 200 1768
192.168.0.11 - mary [10/Oct/2024:16:20:00 -0700] "GET /tutorials HTTP/1.1" 200 3040
127.0.0.1 - frank [10/Oct/2024:16:25:30 -0700] "POST /admin/login HTTP/1.0" 401 332
203.0.113.14 - jane [10/Oct/2024:16:30:12 -0700] "GET /news HTTP/1.1" 200 1456
192.168.0.12 - sam [10/Oct/2024:16:35:40 -0700] "GET /media HTTP/1.1" 200 3876
127.0.0.1 - john [10/Oct/2024:16:40:25 -0700] "GET /profile/settings HTTP/1.0" 200 2345
203.0.113.15 - mary [10/Oct/2024:16:45:10 -0700] "GET /events HTTP/1.1" 200 1987
192.168.0.13 - frank [10/Oct/2024:16:50:45 -0700] "POST /feedback HTTP/1.1" 200 415
127.0.0.1 - jane [10/Oct/2024:16:55:33 -0700] "GET /partners HTTP/1.0" 200 1750

insert-data.sh

#!/bin/bash

# Define variables
ES_HOST="http://localhost:9200"
INDEX_NAME="access_logs"
ACCESS_LOG_FILE="access.log"
BULK_FILE="bulk.json"
API_KEY="YOUR_API_KEY_HERE"

# Drop the existing index if it exists
curl -X DELETE "$ES_HOST/$INDEX_NAME" -H "Authorization: ApiKey $API_KEY"

# Create the index with the appropriate mapping
curl -X PUT "$ES_HOST/$INDEX_NAME" -H "Content-Type: application/json" -H "Authorization: ApiKey $API_KEY" -d '{
  "mappings": {
    "properties": {
      "ip": {
        "type": "ip"
      },
      "user": {
        "type": "keyword"
      },
      "timestamp": {
        "type": "date",
        "format": "dd/MMM/yyyy:HH:mm:ss Z"
      },
      "method": {
        "type": "keyword"
      },
      "url": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "protocol": {
        "type": "keyword"
      },
      "status_code": {
        "type": "integer"
      },
      "size": {
        "type": "integer"
      },
      "message": {
        "type": "text"
      }
    }
  }
}'

# Prepare the bulk JSON file
> $BULK_FILE  # Clear or create the bulk file

while read -r line; do
  # Extract information from each log line (assuming Common Log Format)
  IP=$(echo "$line" | awk '{print $1}')
  USER=$(echo "$line" | awk '{print $3}')
  TIMESTAMP=$(echo "$line" | awk '{print $4, $5}' | sed 's/\[//; s/\]//')
  METHOD=$(echo "$line" | awk '{print $6}' | sed 's/\"//')
  URL=$(echo "$line" | awk '{print $7}')
  PROTOCOL=$(echo "$line" | awk '{print $8}' | sed 's/\"//')
  STATUS_CODE=$(echo "$line" | awk '{print $9}')
  SIZE=$(echo "$line" | awk '{print $10}')

  # Replace double quotes with single quotes in the log line for the message field
  MESSAGE=$(echo "$line" | sed "s/\"/'/g")

  # Create a JSON object for each log entry (single-line format)
  cat <<EOF >> $BULK_FILE
{ "index": { "_index": "$INDEX_NAME" } }
{"ip": "$IP", "user": "$USER", "timestamp": "$TIMESTAMP", "method": "$METHOD", "url": "$URL", "protocol": "$PROTOCOL", "status_code": $STATUS_CODE, "size": $SIZE, "message": "$MESSAGE"}
EOF

done < "$ACCESS_LOG_FILE"

# Use Elasticsearch bulk API to insert data
curl -X POST "$ES_HOST/_bulk" -H "Content-Type: application/json" -H "Authorization: ApiKey $API_KEY" --data-binary @$BULK_FILE

# Cleanup
rm $BULK_FILE