Query Issue: last_heartbeat_time in BigQuery returning null values

[turri]

I've noticed while troubleshooting our log ingestion metrics that despite SIEM logs being successfully ingested, my query to extract log types from the last 90 days returns empty results. I'm trying to understand why the last_heartbeat_time field is returning null values.

Current Query
SELECT log_type, MAX(last_heartbeat_time) AS last_heartbeat_time 
FROM `datalake.ingestion_metrics` WHERE last_heartbeat_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
GROUP BY log_type
ORDER BY log_type

The Issue

When running this query, I get no results - last_heartbeat_time is null and no log types are retrieved, even though I can verify that logs are being ingested in our SIEM system.

What I've Verified

• Logs are definitely being ingested into our SIEM system
• The issue appears specific to the last_heartbeat_time field or the time-based filtering

Has anyone encountered this discrepancy before? What could be causing the last_heartbeat_time field to be null despite active log ingestion? Are there any known issues with timestamp fields in this context?

[vaskenh]

Hi @turri, in this scenario what is the mechanism that you're using to forward the logs to SecOps?   Are you using the SecOps forwarder, or some other means to send the logs?

From our documentation page on the ingestion metrics schema, the entry for last_heartbeat_time is described as only being populated if the ingestion source is either the forwarder or the Chronicle API feed.  Are you using either of those two methods?

---

"The last timestamp at which the forwarder or API feed was active, in microseconds. This field is populated if the ingestion source is the Google Security Operations forwarder or Chronicle API feed."

---

https://cloud.google.com/chronicle/docs/reference/ingestion-metrics-schema

[turri]

Thank you for your help! I've identified that the ingestion source is causing the issue with my query.

Regarding the potential solution, the end_time field looks promising as a workaround, but before implementing it permanently, I need to understand its exact purpose in our data model.

Could you please clarify what the end_time field represents in the datalake.ingestion_metrics table?

[vaskenh]

It represents the end time boundary for which you're pulling the metrics for that log type.

Along with start_time, the two fields represent the beginning and end time boundaries for pulling the metrics you're interested in.

I've linked here an excellent post by Chris Martin (@cmmartin_google) about ingestion metrics.  It includes multiple examples and explanations that are useful to review.

https://medium.com/@thatsiemguy/chronicle-ingestion-stats-metrics-4fa14386b9fc