es|ql

FROM

METADATA _index can be added to the FROM command to include the listed metadata fields.

CONCAT

Populate final_string with STRING VALUE_OF_FIELD. There can be more than one set of things to concatenate in the same command, so keep adding to it.

eval final_string = CONCAT(“STRING”, TO_STRING(FIELD)[, …])

examples

Get a list of IP addresses and the count of events from those IPs from logs containing log.source.address. It has to remove the port from the address, and only outputs the IP and count. This example is using cisco_ios logs

FROM logs-* | WHERE log.source.address IS NOT NULL AND event.dataset == "cisco_ios.log" | DISSECT log.source.address """%{ipaddress}:%{port}""" | KEEP ipaddress |
STATS c = COUNT(*) BY ipaddress | KEEP ipaddress,c | SORT c DESC

Show the dashboards being used

Show dashboards being used and the number of times each was used in 1hr buckets.

FROM elastic-cloud-logs-* | WHERE KQL("""kibana.saved_object.type:"dashboard" """) | STATS count = COUNT(*) BY kibana.saved_object.name,hour = BUCKET(@timestamp, 1 hour) | KEEP hour,kibana.saved_object.name,count | SORT hour,count DESC | LIMIT 100

Show users accessing a specific space

FROM elastic-cloud-logs-* | WHERE KQL("""kibana.space_id:"SPACE_ID" """) | STATS count = COUNT(*) BY user.name,hour = BUCKET(@timestamp, 1 hour) | KEEP hour,username,count | SORT hour

compare @timestamp

FROM logs-* | WHERE @timestamp > NOW() - 1 hour

change an int to float

FROM logs-* | EVAL something = TO_DOUBLE(int_field)

use case like in programming

... | eval stuff = case(status == 'Active', 0, status == 'Inactive', 2)

calculate ingest delay

FROM logs-* | eval timestamp_epoch = to_long(@timestamp)
| eval ingested_epoch = to_long(event.ingested)
| eval time_difference_minutes = (ingested_epoch - timestamp_epoch) / 60000
| stats avg(time_difference_minutes) by event.dataset
| RENAME `avg(time_difference_minutes)` as avg_ingest_delay_minutes
| KEEP event.dataset, avg_ingest_delay_minutes
| sort avg_ingest_delay_minutes DESC

multiple stats

FROM logs-*
| WHERE response > 400
| STATS
    total_errors = COUNT(*),
    avg latency = AVG(latency),
    max_latency = MAX(latency)
  BY client_ip
| SORT total_errors DESC
| LIMIT 10