✄𐘗
text code block:
✄𐘗from logs-network_traffic.tls-*
| where
    network.protocol == "tls"
    and network.transport == "tcp"
    and tls.established == true
    and source.ip is not null
    and destination.ip is not null
    and tls.server.x509.not_before is not null
    and tls.server.x509.issuer.distinguished_name is not null
    and tls.server.x509.subject.distinguished_name is not null
    and tls.server.x509.issuer.distinguished_name == tls.server.x509.subject.distinguished_name
    and tls.server.x509.not_before >= now() - 30 days
    and tls.server.x509.not_before <= now()
    and CIDR_MATCH(
      source.ip,
      "10.0.0.0/8",
      "100.64.0.0/10",
      "172.16.0.0/12",
      "192.168.0.0/16",
      "fc00::/7"
    )
    and not CIDR_MATCH(
      destination.ip,
      "0.0.0.0/8",
      "10.0.0.0/8",
      "100.64.0.0/10",
      "127.0.0.0/8",
      "169.254.0.0/16",
      "172.16.0.0/12",
      "192.0.0.0/24",
      "192.0.2.0/24",
      "192.168.0.0/16",
      "192.175.48.0/24",
      "192.31.196.0/24",
      "192.52.193.0/24",
      "192.88.99.0/24",
      "198.18.0.0/15",
      "198.51.100.0/24",
      "203.0.113.0/24",
      "224.0.0.0/4",
      "240.0.0.0/4",
      "::/128",
      "::1/128",
      "2001:db8::/32",
      "fc00::/7",
      "fe80::/10",
      "ff00::/8"
    )
| stats
    Esql.event_count = COUNT(*),
    Esql.first_seen = MIN(@timestamp),
    Esql.last_seen = MAX(@timestamp),
    Esql.destination_port_values = MV_SLICE(VALUES(destination.port), 0, 9),
    Esql.tls_client_server_name_values = MV_SLICE(VALUES(tls.client.server_name), 0, 9),
    Esql.tls_server_x509_subject_common_name_values = MV_SLICE(VALUES(tls.server.x509.subject.common_name), 0, 9),
    Esql.tls_server_x509_serial_number_values = MV_SLICE(VALUES(tls.server.x509.serial_number), 0, 4),
    Esql.tls_server_hash_sha1_values = MV_SLICE(VALUES(tls.server.hash.sha1), 0, 4),
    Esql.tls_server_hash_sha256_values = MV_SLICE(VALUES(tls.server.hash.sha256), 0, 4),
    Esql.tls_server_x509_not_after_values = MV_SLICE(VALUES(tls.server.x509.not_after), 0, 4),
    Esql.network_community_id_values = MV_SLICE(VALUES(network.community_id), 0, 9),
    Esql.host_name_values = MV_SLICE(VALUES(host.name), 0, 9),
    Esql.observer_name_values = MV_SLICE(VALUES(observer.name), 0, 19)
  by
    source.ip,
    destination.ip,
    tls.server.x509.subject.distinguished_name,
    tls.server.x509.not_before
| keep
    source.ip,
    destination.ip,
    tls.server.x509.subject.distinguished_name,
    tls.server.x509.not_before,
    Esql.event_count,
    Esql.first_seen,
    Esql.last_seen,
    Esql.destination_port_values,
    Esql.tls_client_server_name_values,
    Esql.tls_server_x509_subject_common_name_values,
    Esql.tls_server_x509_serial_number_values,
    Esql.tls_server_hash_sha1_values,
    Esql.tls_server_hash_sha256_values,
    Esql.tls_server_x509_not_after_values,
    Esql.network_community_id_values,
    Esql.host_name_values,
    Esql.observer_name_values