[question] How do you use "alert's" views?
Opened this issue · 2 comments
How do you use "alert's" views created by dataform to trigger an alert?
Do you have any example that you can share?
Great question. The alert views provide the underlying queries for continuous monitoring, utilizing the lookup tables (IP/user/etc.) when applicable for cost efficiencies and performance speedup. There are multiple ways to trigger alert queries and respond to alert findings depending on companies' own tooling and security response workflow. It's one of the reasons this component is not yet provided as part of this repo as 'it depends'. Happy to discuss design here or offline and we welcome your feedback and/or PR contributions.
Some recommended options for triggering alerts, also based on what we see with customers:
Option A:
You can use BigQuery scheduled queries to repeatedly run these alert queries on a given sliding window interval (15min minimum). Upon execution, scheduled queries emits Cloud Monitoring metrics, in particular last_execution_job_rows which specifies how many rows written (or findings detected) by this alert query. You can then set up an alert policy in Cloud Monitoring based on that metric, and configure one or more notification channels like PagerDuty, Email, Pub/Sub, Slack, etc. Refer to this architecture diagram of this blog post with more context.
Option B:
You can use Dataform workflow configurations to schedule executions of these alerts on a regular basis. This can be a higher frequency (down to a minute interval). Another advantage is the consistency of using Dataform workflows for scheduled executions not only for summary tables, lookup tables, report tables but also for these alerts. However, you have to keep in mind the Dataform API quota, currently at 60 invocations/minute/project/region. You will also have to change the alert definitions to tables so that results are persisted, and an audit trail is logged, in particular protoPayload.metadata.tableDataChange.insertedRowsCount field with the number of rows written (or findings detected). You could then set up a log-based alert in Cloud Monitoring based on those BigQuery Data Access Audit logs (which are enabled by default for all projects). Similar to approach A, you can then set up an alert policy and configure one or more notification channels like PagerDuty, Email, Pub/Sub, Slack, etc.
Option C:
Use your existing third-party tooling or BI tool on top of BigQuery like Looker or Grafana for continuous monitoring and alerting.
In summary, there are multiple moving parts. However B is relatively easier to provide out-of-the-box in this repo, as additional Dataform workflows. Both A or B requires IaC in Terraform for the Cloud Monitoring resources which is fairly straightforward but could benefit from community contributions.
Thank you for your complete answer and with good options.
I'll try option B, but option A seems "easier" 😊