kleong

/* Grouping events emitted in last 5 seconds by vehicleId and getting the time of the oldest event in this group */
WITH vehicles_in_last_5_seconds AS (
    SELECT
        vehicleinfo.vehicleId,
        vehicleinfo._event_time,
        vehicleinfo.speed
    from
        commons.vehicleinfo
    WHERE
        vehicleinfo._event_time > CURRENT_TIMESTAMP() - SECONDS(5)

kleong

/* Grouping events emitted in last 5 seconds by vehicleId and getting the time of the oldest event in this group */
WITH vehicles_in_last_5_seconds AS (
   SELECT
       vehicleinfo.vehicleId,
       vehicleinfo._event_time,
       vehicleinfo.latitude,
         vehicleinfo.longitude
   from
       commons.vehicleinfo
   WHERE

kleong

+------------------------------------------------------------------------------+--------------------------------------------------------------+--------------+----------------------+
| message                                                                      | name                                                         | reason       | ts                   |
|------------------------------------------------------------------------------+--------------------------------------------------------------+--------------+----------------------|
| Node ip-xx-xxx-xx-xxx.us-xxxxxx.compute.internal status is now: NodeNotReady | ip-xx-xxx-xx-xxx.us-xxxxxx.compute.internal.yyyyyyyyyyyyyyyy | NodeNotReady | 2019-09-30T02:13:19Z |
| Node ip-xx-xxx-xx-xxx.us-xxxxxx.compute.internal status is now: NodeNotReady | ip-xx-xxx-xx-xxx.us-xxxxxx.compute.internal.yyyyyyyyyyyyyyyy | NodeNotReady | 2019-09-30T02:13:19Z |
| Node ip-xx-xxx-xx-xxx.us-xxxxxx.compute.internal status is now: NodeNotReady | ip-xx-xxx-xx-xxx.us-xxxxxx.comput

kleong

With nodes as (
    select
          e.event.reason,
          e.event.message,
          e.event.lastTimestamp as ts,
          e.event.metadata.name
    from
        commons.eventrouter_events e
    where
        e.event.involvedObject.kind = 'Node'

kleong

+------------------------------------------+----------------------------------------+-----------------------------+----------------+
| image                              | name                                   | pod                         | ts                   |
|------------------------------------------+----------------------------------------+-----------------------------+----------------|
| leafagg:0.6.14.20190928-58cdee6dd4 | aggregator-c478b597.15c8811219b0c944   | aggregator-c478b597-z8fln   | 2019-09-28T04:53:05Z |
| leafagg:0.6.14.20190928-58cdee6dd4 | aggregator-c478b597.15c881077898d3e0   | aggregator-c478b597-wvbdb   | 2019-09-28T04:52:20Z |
| leafagg:0.6.14.20190928-58cdee6dd4 | aggregator-c478b597.15c880742e034671   | aggregator-c478b597-j7jjt   | 2019-09-28T04:41:47Z |
| leafagg:0.6.14.20190926-a553e0af68 | aggregator-587f77c45c.15c8162d63e918ec | aggregator-587f77c45c-qjkm7 | 2019-09-26T20:14:15Z |
| leafagg:0.6.14.20190926-a553e0af68 | aggregator-587f77c45c.15c8160fefed6631 | aggregator-58

kleong

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'

kleong

$ kubectl get events
LAST SEEN   TYPE      REASON                 KIND                      MESSAGE
5m          Normal    Scheduled              Pod                       Successfully assigned master/jobworker-c5dc75db8-7m5ln to ip-XXX-XXX-XXX-XXX.us-west-2.compute.internal
5m          Normal    Pulling                Pod                       pulling image "..."
4m          Normal    Pulled                 Pod                       Successfully pulled image "..."
...
...
...

kleong

$ kubectl describe pods jobworker-c5dc75db8-7m5ln
...
...
...
Events:
  Type     Reason     Age                From                                                    Message
  ----     ------     ----               ----                                                    -------
  Normal   Scheduled  7m                 default-scheduler                                       Successfully assigned master/jobworker-c5dc75db8-7m5ln to ip-10-202-41-139.us-west-2.compute.internal
  Normal   Pulling    6m                 kubelet, ip-XXX-XXX-XXX-XXX.us-west-2.compute.internal  pulling image "..."
  Normal   Pulled     6m                 kubelet, ip-XXX-XXX-XXX-XXX.us-west-2.compute.internal  Successfully pulled image "..."

kleong

rockset> WITH sum_days as (
           select sum(EXTRACT(day from PARSE_DATETIME_ISO8601(o."DATETIMECLOSED") -
                      PARSE_DATETIME_ISO8601(o."DATETIMEINIT"))) as days, 
                      o."REQCATEGORY"
           from "oakland-call-center" o
           where o."DATETIMECLOSED" != '' and o."DATETIMEINIT" != ''
           GROUP BY o."SOURCE"
         ),
         sum_sources as (
           select o."REQCATEGORY", count(*) as count from "oakland-call-center" o           

kleong

rockset> select count(*) as cancelled_requests
       from "oakland-call-center"
       where STATUS = 'CANCEL'
          and SOURCE = 'SeeClickFix'
           and PARSE_DATETIME_ISO8601(DATETIMEINIT) > CURRENT_DATETIME() - DAYS(3);
+----------------------+
| cancelled_requests   |
|----------------------|
| 44                   |
+----------------------+