Analyze logs with BigQuery

BigQuery is a powerful tool for performing analytics. You can use it to store longer-term logs and to perform SQL-like queries against the data. To use BigQuery for analysis, you must explicitly route your logs to BigQuery, as described in the next section.

Route logs to BigQuery

  1. In the Logs Explorer, create a filter that isolates the Fleet Engine logs: Fleetengine.googleapis.com/Fleet.
  2. In the Query Results pane, click the Actions or More Actions menu and choose Create Sink.
  3. Specify a sink name (for example, FleetEngineLogsSink). Click Next.
  4. In the Sink Destination, select BigQuery dataset.
  5. In Select BigQuery dataset, select Create new BigQuery dataset.
  6. In the Create dataset dialog, enter a Dataset ID.
  7. Leave everything else as is and click Create dataset.
  8. Check Use partitioned tables. Click Next.
  9. Leave Choose logs to include in sink and Choose logs to filter out of sink as they are.
  10. Click Create Sink.

Your logs should now begin to populate the BigQuery dataset, which can take a short while. See also Route logs to supported destinations.

Once you are routing log data to BigQuery, several tables under the FleetEngineLogs dataset are automatically populated, one for each log type:

  • CreateVehicle
  • GetVehicle
  • ListVehicles
  • SearchVehicles
  • UpdateVehicle
  • CreateTrip
  • GetTrip
  • UpdateTrip
  • ListTrips

The table names use the following pattern:

project_id.data_set.log_name

For example, if the project is called test-project and the dataset name is FleetEngineLogs, the CreateTrip table has the following name:

test-project.FleetEngineLogs.fleetengine_googleapis_com_create_trip

Example queries for BigQuery

The following example queries show how you can search for different log entries in BigQuery.

Number of CreateTrips logs grouped by hour

    SELECT TIMESTAMP_TRUNC(timestamp, HOUR) as hour,
           count(*) as num_trips_created
    FROM
    `ProjectId.FleetEngineLogs.fleetengine_googleapis_com_create_trip`
    GROUP BY hour
    ORDER by hour

Number of stops per vehicle per hour

    SELECT
      jsonpayload_v1_updatevehiclelog.request.vehicleid AS vehicle,
      TIMESTAMP_TRUNC(timestamp, HOUR) AS hour,
      COUNT(*) AS num_stops
    FROM
      `ProjectId.FleetEngineLogs.fleetengine_googleapis_com_update__vehicle`
    WHERE
    ARRAY_LENGTH(jsonpayload_v1_updatevehiclelog.request.vehicle.remainingvehiclejourneysegments) > 0
    AND jsonpayload_v1_updatevehiclelog.request.vehicle.remainingvehiclejourneysegments[
    OFFSET
    (0)].stop.state = 'VEHICLE_STOP_STATE_LOG_ARRIVED'
    GROUP BY
    1,
    2
    ORDER BY
    2

For example, this query could tell you that in the last hour:

  • Vehicle A completed 10 stops in hour 12 and 8 stops in hour 13.
  • Vehicle B completed 5 stops in hour 11 and 7 stops in hour 12.
  • Vehicle C completed 12 stops in hour 13 and 9 stops in hour 14.

See also View logs routed to BigQuery.

Integrate BigQuery with Looker Studio

BigQuery can be integrated with business intelligence tools to create dashboards for business analytics. See Looker Studio.

The following example shows how to build a Looker Studio dashboard for visualizing trips and vehicle movements on a map.

  1. Launch a new Looker Studio dashboard and select BigQuery as the data connection.
  2. Select Custom Query and manually enter or select the Cloud Project to which it should be billed.
  3. Enter one of the following queries into the query box.

On-demand trips example query

    SELECT
     timestamp,
     labels.vehicle_id,
    jsonpayload_v1_updatevehiclelog.response.lastlocation.location.latitude AS lat,
    jsonpayload_v1_updatevehiclelog.response.lastlocation.location.longitude AS lng
    FROM
    `ProjectId.TableName.fleetengine_googleapis_com_update_vehicle`

Scheduled Tasks example query

    SELECT
    labels.delivery_vehicle_id,
    jsonpayload_v1_updatedeliveryvehiclelog.response.lastlocation.rawlocation.longitude as lat, jsonpayload_v1_updatedeliveryvehiclelog.response.lastlocation.rawlocation.latitude as lng
    FROM `ProjectID.TableName.fleetengine_googleapis_com_update_delivery_vehicle`
  1. Select Chart Type as Bubble Map, and then select the location field.
  2. Select Add a Field.
  3. Name the field and add the following formula: CONCAT(lat, ",", lng).
  4. Set the type to Geo->Latitude, Longitude.
  5. You can add controls to the dashboard to filter data. For example, select the Date-range filter.
  6. Edit the date range box to select a default date range.
  7. You can add additional drop-down list controls for vehicle_id. With these controls, you can visualize the movement of the vehicle or the movement within a trip.

Looker Studio example output:

Looker Studio example output

What's next

To comply with data retention policies, see Restrict log retention.