✄𐘗
text code block:
✄𐘗from logs-gcp.audit-* metadata _id, _index, _version
| where data_stream.dataset == "gcp.audit"
    and service.name == "k8s.io"
    and event.outcome == "success"
    and event.action in (
      "io.k8s.certificates.v1.certificatesigningrequests.create",
      "io.k8s.certificates.v1.certificatesigningrequests.approval.update"
    )
    and client.user.email is not null
    and gcp.audit.resource_name is not null
    and not client.user.email in (
      "system:gcp-controller-manager",
      "system:kube-controller-manager",
      "system:serviceaccount:kube-system:certificate-controller",
      "kubelet-bootstrap",
      "kubelet-nodepool-bootstrap"
    )
    and not client.user.email like "system:node:*"
| eval Esql.csr_name = replace(gcp.audit.resource_name, "/approval", "")
| stats
    Esql.create_count = count(*) where event.action == "io.k8s.certificates.v1.certificatesigningrequests.create",
    Esql.approval_count = count(*) where event.action == "io.k8s.certificates.v1.certificatesigningrequests.approval.update",
    Esql.event_action_values = values(event.action),
    Esql.timestamp_first_seen = min(@timestamp),
    Esql.timestamp_last_seen = max(@timestamp),
    Esql.source_ip_values = values(source.ip),
    Esql.user_agent_original_values = values(user_agent.original),
    Esql.data_stream_namespace_values = values(data_stream.namespace)
  by client.user.email, Esql.csr_name
| where Esql.create_count >= 1
    and Esql.approval_count >= 1
    and date_diff("seconds", Esql.timestamp_first_seen, Esql.timestamp_last_seen) <= 300
| keep client.user.email, Esql.*