k8s-events-query1.sql

With replicasets as (
        select
            e.event.reason as reason,
            e.event.lastTimestamp as ts,
            e.event.metadata.name as name,
              REGEXP_EXTRACT(e.event.message, 'Created pod: (.*)', 1) as pod
        from
            commons.eventrouter_events e
        where
            e.event.involvedObject.kind = 'ReplicaSet'
            and e.event.metadata.namespace = 'production'
              and e.event.reason = 'SuccessfulCreate'
    ),
    pods as (
        select
            e.event.reason as reason,
            e.event.message as message,
            e.event.lastTimestamp as ts,
            e.event.involvedObject.name as name,
            REGEXP_EXTRACT(
                e.event.message,
                'pulling image "imagerepo/folder/(.*?)"',
                1
            ) as image
        from
            commons.eventrouter_events e
        where
            e.event.involvedObject.kind = 'Pod'
            and e.event.metadata.namespace = 'production'
            and e.event.message like '%pulling image%'
              and e.event.involvedObject.name like 'aggregator%'
    )

SELECT * from (
select
    MAX(p.ts) as ts, MAX(r.pod) as pod, MAX(p.image) as image, r.name
from
    pods p
    JOIN replicasets r on p.name = r.pod
GROUP BY r.name) sq
ORDER BY ts DESC
limit 100;