log-insight-query-for-mysql-and-postgresql

log-insight-query.txt

# For MySql

fields @timestamp, @message
| filter @message like /Query_time/
| parse  @message /User@Host:\s*(?<user>[^;]*)\s*Query_time:\s*(?<Query_time_in_sec>[0-9]+(?:\.[0-9]+)?)\s*Lock_time:\s*(?<Lock_time_in_sec>[0-9]+(?:\.[0-9]+)?)[\s\S]*?;\s*(?<Query>[^;]*)/
| display @timestamp, user, Query_time_in_sec, Lock_time_in_sec, Query
| sort Query_time_in_sec desc
| limit 100


# For PostgreSQL

fields @timestamp, @message
| filter @message like /duration/
| parse @message /\):(?<user>\S+)\@(?<db>\S+):(?<id>.*):(?<log>.*):(?<space>\s+)duration\: (?<time_in_ms>.\S+) ms (?<statement>.*): (?<query>.*)/
| display @timestamp, user, db, time_in_ms, query
| filter (time_in_ms > '200')
| sort time_in_ms desc
| limit 20


# To select specific log stream

filter @logStream like "log-stream/1234"

example:

fields @timestamp, @message
| filter @logStream like "log-stream/1234"
| filter @message like /Query_time/
| parse  @message /User@Host:\s*(?<user>[^;]*)\s*Query_time:\s*(?<Query_time_in_sec>[0-9]+(?:\.[0-9]+)?)\s*Lock_time:\s*(?<Lock_time_in_sec>[0-9]+(?:\.[0-9]+)?)[\s\S]*?;\s*(?<Query>[^;]*)/
| display @timestamp, user, Query_time_in_sec, Lock_time_in_sec, Query
| sort Query_time_in_sec desc
| limit 100


## For VPN logs or json fileds
fields @timestamp, @message
| parse @message "connection-log-type\": \"*\"," as connection_log_type
| parse @message "connection-attempt-status\": \"*\"," as connection_attempt_status
| parse @message "connection-attempt-failure-reason\": \"*\"," as connection_attempt_failure_reason
| parse @message "connection-id\": \"*\"," as connection_id
| parse @message "client-vpn-endpoint-id\": \"*\"," as client_vpn_endpoint_id
| parse @message "transport-protocol\": \"*\"," as transport_protocol
| parse @message "connection-start-time\": \"*\"," as connection_start_time
| parse @message "connection-last-update-time\": \"*\"," as connection_last_update_time
| parse @message "client-ip\": \"*\"," as client_ip
| parse @message "common-name\": \"*\"," as common_name
| parse @message "username\": \"*\"," as username
| parse @message "device-type\": \"*\"," as device_type
| parse @message "device-ip\": \"*\"," as device_ip
| parse @message "port\": \"*\"," as port
| parse @message "ingress-bytes\": \"*\"," as ingress_bytes
| parse @message "egress-bytes\": \"*\"," as egress_bytes
| parse @message "ingress-packets\": \"*\"," as ingress_packets
| parse @message "egress-packets\": \"*\"," as egress_packets
| parse @message "connection-end-time\": \"*\"," as connection_end_time
| parse @message "connection-reset-status\": \"*\"," as connection_reset_status
| parse @message "connection-duration-seconds\": \"*\"" as connection_duration_seconds
| filter ispresent(client_ip) AND client_ip != "NA"
| display connection_log_type, connection_attempt_status, connection_attempt_failure_reason, connection_id, client_vpn_endpoint_id, transport_protocol, connection_start_time, connection_last_update_time, client_ip, common_name, username, device_type, device_ip, port, ingress_bytes, egress_bytes, ingress_packets, egress_packets, connection_end_time, connection_reset_status, connection_duration_seconds