Investigating Violations

• Root-cause analysis and triage
• Dashboards and automated alerts
• Offline machine learning or data aggregation work

​

Quick Investigation Checklist

• Required Data: The event_id, Run ID, or a specific timestamp range from the Findings dashboard.
• Permissions: The necessary bigquery.dataViewer and bigquery.jobUser permissions to access and query the data.
• Query Best Practices: Use saved views or pivoted queries to minimize manual errors.
• Data Security: Mask or redact any sensitive fields before sharing query results outside of the security team.

​

How to access the data

​

Required Privileges

• roles/bigquery.dataViewer: To read the table data.
• roles/bigquery.jobUser: To run queries.

​

GCP Console (web UI)

1. Sign in to Google Cloud Console and select the project that owns the dataset.
2. Open BigQuery → Explorer panel → find your_project → dataset ai_security → table ai_security.
3. Click the table to view schema, details and preview rows.
4. Use the Query editor to run SQL; review results in Table / JSON view; use the Visualization tab to build simple charts.
5. Save frequently used diagnostic queries with the Console’s Saved Queries feature for reuse and sharing.

​

CLI (Cloud Shell or local with gcloud + bq)

1. Authenticate and set the project:

gcloud auth login
gcloud config set project YOUR_PROJECT_ID

2. Run a simple query with bq:

bq query --use_legacy_sql=false \
"SELECT timestamp, jsonPayload.ai_security.event_id, jsonPayload.ai_security.event_type
 FROM \`YOUR_PROJECT.ai_security.ai_security\`
 WHERE jsonPayload.ai_security.event_type = 'VIOLATION'
 ORDER BY timestamp DESC
 LIMIT 50;"

3. Output JSON for programmatic parsing:

bq query --use_legacy_sql=false --format=prettyjson 'SELECT ...'

4. Save queries as stored procedures in BigQuery and call them from the CLI for automation.

​

Relevant columns (top-level BigQuery fields)

• logName — Log stream name.
• resource.type — GCP resource type (e.g., k8s_container).
• resource.labels.pod_name — Pod name (k8s).
• resource.labels.location — Region / zone.
• resource.labels.namespace_name — K8s namespace.
• resource.labels.cluster_name — Cluster name.
• resource.labels.project_id — GCP project id.
• timestamp — Event timestamp (when it occurred).
• receiveTimestamp — When the log was ingested.
• insertId — Unique insert id (dedupe).
• labels.commit_hash, labels.branch, labels.full_version — Build/version metadata.

​

Payload structure (jsonPayload.ai_security)

• event_id (string) — globally unique event id.
• event_type (enum) — e.g., VIOLATION.
• event_description (string) — human readable description.
• user_id (string) — user that triggered the event.
• session_info — object with tab_id and session_tracking_token.
• action (enum) — enforcement taken (BLOCK_REQUEST / ALLOW_REQUEST).
• content_raw (string) — raw content that caused the event (user prompt or retrieved content).
• content_metadata (repeated {key,value}) — context keys such as:
  • RESOURCE_NAME, RESOURCE_ID, RESOURCE_URL,
  • AGENT_NAME, RUN_ID, CHAT_SESSION_ID, AGENT_ID, SOURCE
• validation_metadata (repeated {key,value}) — model prediction / validation debugging key-values.
• (other context fields may exist — e.g., workflow entries, LLM call details, agent spans)

message Metadata {
   string key = 1 [(options.scrub) = {method: RAW}];
   string value = 2 [(options.scrub) = {method: RAW}];
}

message AiSecurityLogEntry {
  string event_id = 1;
  EventType event_type = 2;
  string event_description = 3;
  string user_id = 4;
  SessionInfo session_info = 5;
  Action action = 6;
  repeated Metadata validation_metadata = 7;
  string content_raw = 8;
  repeated Metadata content_metadata = 9;
}

​

Common SQL patterns & examples

​

Get latest 100 violations with key content & resource id

SELECT
  timestamp,
  jsonPayload.ai_security.event_id AS event_id,
  jsonPayload.ai_security.event_type AS event_type,
  jsonPayload.ai_security.user_id AS user_id,
  jsonPayload.ai_security.action AS action,
  jsonPayload.ai_security.content_raw AS content_raw,
  cm.value AS resource_id
FROM
  `YOUR_PROJECT.ai_security.ai_security`,
  UNNEST(jsonPayload.ai_security.content_metadata) AS cm
WHERE
  jsonPayload.ai_security.event_type = 'VIOLATION'
  AND cm.key = 'RESOURCE_ID'
ORDER BY timestamp DESC
LIMIT 100;

​

Extract a single row’s full JSON for deep debugging

SELECT
  TO_JSON_STRING(t) AS full_row_json
FROM
  `YOUR_PROJECT.ai_security.ai_security` AS t
WHERE
  jsonPayload.ai_security.event_id = 'f525eda94eae4f8bb260b78cddb9b3bb';

​

Pivot content_metadata into columns (common keys)

SELECT
  timestamp,
  jsonPayload.ai_security.event_id AS event_id,
  MAX(IF(cm.key='RESOURCE_ID', cm.value, NULL)) AS resource_id,
  MAX(IF(cm.key='AGENT_NAME', cm.value, NULL)) AS agent_name,
  MAX(IF(cm.key='RUN_ID', cm.value, NULL)) AS run_id,
  jsonPayload.ai_security.content_raw AS content_raw
FROM
  `YOUR_PROJECT.ai_security.ai_security`,
  UNNEST(jsonPayload.ai_security.content_metadata) AS cm
WHERE
  jsonPayload.ai_security.event_type = 'VIOLATION'
GROUP BY timestamp, event_id, content_raw
ORDER BY timestamp DESC
LIMIT 200;

​

Count of violations by action type (daily)

SELECT
  DATE(timestamp) AS day,
  jsonPayload.ai_security.action AS action,
  COUNT(1) AS count
FROM `YOUR_PROJECT.ai_security.ai_security`
WHERE jsonPayload.ai_security.event_type = 'VIOLATION'
GROUP BY day, action
ORDER BY day DESC;

​

Top validation metadata keys/values (for model debugging)

SELECT
  vm.key, vm.value, COUNT(1) AS occurrences
FROM `YOUR_PROJECT.ai_security.ai_security`,
UNNEST(jsonPayload.ai_security.validation_metadata) AS vm
GROUP BY vm.key, vm.value
ORDER BY occurrences DESC
LIMIT 200;

​

​

Useful investigative workflows

1. Triage a single finding
   • Start with the event_id from the Findings dashboard or the Run ID.
   • Query event_id in BigQuery to fetch content_raw, content_metadata and validation_metadata.
   • Inspect LLM call traces / llm_call and agent_span fields (if present) to see prompt / response context.
2. Find similar incidents
   • Use content_metadata.RESOURCE_ID or normalized content_raw hashes to group similar violations.
   • Search by validation_metadata keys (e.g., model label or confidence buckets) to identify common false positives.
3. Root cause of skipped users / digest generation issues
   • Combine digest entries with workflow/compiler logs (workflow, workflow_compiler) in the exported fields to see enqueue vs execution differences.
4. Automated daily rollups
   • Create scheduled queries that aggregate violations by agent, action, and resource; write results to ai_security_reporting dataset for dashboards.

​

Best practices & operational recommendations

• Minimize scanned bytes: Project only needed fields (avoid SELECT *). Use UNNEST() carefully and filter early.
• Protect PII: content_raw may contain sensitive user content. Limit access via IAM and consider creating sanitized views that mask or redact content_raw before sharing with wider teams.
• Stored procedures & saved queries: Convert complex investigation SQL into stored procedures or saved queries for repeatable triage.
• Alerting: For high-severity events (e.g., many BLOCK_REQUEST in short window), schedule queries to write a metric table and tie it to Cloud Monitoring or a Cloud Function that publishes alerts.
• Retention & export: Define retention in BigQuery or set up periodic exports for offline ML if long-term analysis is required.

​

Example: create a reusable view for violation triage

CREATE OR REPLACE VIEW `YOUR_PROJECT.ai_security.violation_triage` AS
SELECT
  timestamp,
  jsonPayload.ai_security.event_id AS event_id,
  jsonPayload.ai_security.event_type AS event_type,
  jsonPayload.ai_security.user_id AS user_id,
  jsonPayload.ai_security.action AS action,
  jsonPayload.ai_security.event_description AS description,
  jsonPayload.ai_security.content_raw AS content_raw,
  ARRAY(
    SELECT AS STRUCT key, value FROM UNNEST(jsonPayload.ai_security.content_metadata)
  ) AS content_metadata,
  ARRAY(
    SELECT AS STRUCT key, value FROM UNNEST(jsonPayload.ai_security.validation_metadata)
  ) AS validation_metadata
FROM `YOUR_PROJECT.ai_security.ai_security`
WHERE jsonPayload.ai_security.event_type = 'VIOLATION';

​

Troubleshooting tips

• If queries are slow/expensive:
  • Add time filters, use partition pruning, cluster by high-cardinality fields, and avoid scanning content_raw unnecessarily.
• For reproducible investigations:
  • Capture the exact SQL and result snapshot (e.g., export query output to Cloud Storage) to preserve context for later audits.