Log Analytics with BigQuery and Looker

In today’s data-driven world, leveraging the full potential of your cloud data is essential. This blog delves into the strategic advantage of streaming Google Cloud audit logs into BigQuery and visualising them through Looker Studio. Discover how this powerful combination can unlock deeper insights, fortify security monitoring, and transform raw log data into actionable intelligence that drives smarter, more informed decision-making.

Business Challenges

• Limited Log Analysis Capabilities: Current log analysis tools may lack the scalability and advanced querying capabilities needed to effectively analyse large volumes of audit log data.
• Security Monitoring Gaps: Real-time monitoring and analysis of security-related events are crucial for identifying and responding to potential threats promptly.
• Lack of Data Centralisation: Analysing audit logs across different services and projects can be cumbersome without a centralised repository.
• Difficulty in Data Visualisation: Extracting meaningful insights from raw audit log data can be challenging without proper visualisation tools.

Proposed Solution

• Stream Audit Logs to BigQuery: Leverage Google Cloud's built-in capabilities to continuously stream audit logs into BigQuery, a highly scalable and cost-effective data warehouse.  
• Utilise BigQuery's Analytical Power: Use BigQuery's SQL capabilities to perform complex queries and analysis on the audit log data, including ad-hoc exploration, trend analysis, and anomaly detection.  
• Visualise Data with Looker Studio: Create interactive dashboards and reports in Looker Studio to visualise key metrics, trends, and security insights derived from the audit log data.  

Benefits

• Enhanced Security Monitoring:
• Improved Operational Efficiency:
• Data-Driven Decision Making:
• Cost Optimisation:

Implementation Plan

• Enable Audit Logging: Ensure audit logging is enabled for relevant Google Cloud services and projects.
• Configure Log Streaming: Set up log sinks to export audit logs to BigQuery.
• Design BigQuery Schema: Define the appropriate schema in BigQuery to store and organise audit log data.
• Develop Looker Studio Dashboards: Create interactive dashboards and reports in Looker Studio to visualise key metrics and insights.  
• Training and Documentation: Provide training and documentation to relevant stakeholders on how to use the new tools and interpret the data.

How could you get the data in?

This is how it could be done. This is just an example and not our full production-ready implementation. Please ensure best practices are considered from a performance, cost and security perspective.

1. Setting up Log Streaming to BigQuery

This involves enabling audit logging for the desired services and creating a sink to route logs to BigQuery. You can do this via the Google Cloud Console or using the gcloud command-line tool.

a) Using the gcloud CLI:

# Replace the following: # PROJECT_ID: Your Google Cloud project ID # DATASET_ID: The BigQuery dataset ID to store logs # FILTER: A filter expression to select specific audit logs gcloud logging sinks create audit-log-sink \ bigquery.googleapis.com/projects/PROJECT_ID/datasets/DATASET_ID \ --log-filter="FILTER"        

Example filter expressions:

• To capture all Admin Activity audit logs: logName:"logs/cloudaudit.googleapis.com%2Factivity"

• To capture Data Access audit logs for BigQuery: logName:"logs/cloudaudit.googleapis.com%2Fdata_access" AND protoPayload.serviceName="bigquery.googleapis.com"
• To capture specific events like google.cloud.bigquery.v2.JobService.InsertJob: protoPayload.methodName="google.cloud.bigquery.v2.JobService.InsertJob"

b) Using the Google Cloud Console:

1. Go to Logging > Logs Router.
2. Click Create Sink.
3. Choose BigQuery dataset as the sink destination.
4. Select your project and dataset.
5. Define a filter to select specific audit logs.
6. Create the sink.
7. Querying Audit Logs in BigQuery

Once logs are streamed to BigQuery, you can use SQL to query and analyse the data.

Example SQL query:

SELECT timestamp, protoPayload.authenticationInfo.principalEmail, protoPayload.methodName, protoPayload.resourceName FROM 

`PROJECT_ID.DATASET_ID.cloudaudit_googleapis_com_ACTIVITY` WHERE protoPayload.methodName LIKE 'google.cloud.bigquery.v2.%' ORDER BY timestamp DESC LIMIT 100;        

This query retrieves the timestamp, user email, method name, and resource name for the 100 most recent BigQuery audit logs.

1. Connecting BigQuery to Looker Studio
2. Go to Looker Studio and create a new report.
3. Click Add data.
4. Select BigQuery as your data source.
5. Choose your project and dataset.
6. Select the relevant table containing audit logs.
7. Click Connect.
8. Visualising Data in Looker Studio

This code and guidance will help you get started with streaming Google Cloud audit logs to BigQuery and visualising them in Looker Studio. Remember to adapt the code and visualisations to your specific needs and use cases.

Looking at Looker?

Looker Studio offers a versatile platform for visualising and analysing Google Cloud audit log data stored in BigQuery. Now you can use Looker Studio's features to build visualisations and dashboards.

• Choose appropriate charts: Use bar charts, line graphs, geomaps, tables, etc., to represent your data effectively.
• Apply filters and controls: Allow users to filter data by time range, user, service, or other relevant criteria.
• Create calculated fields: Derive new metrics from the raw data, such as success rate, error rate, or resource usage.
• Customise the look and feel: Style your dashboards with colours, logos, and formatting to enhance readability and presentation.

We represented the data in the following ways:  

Security Monitoring Dashboards:

• Real-time threat detection: Create dashboards with visualisations like:
• Alerting: Configure Looker Studio to send alerts based on specific thresholds or conditions, such as a sudden spike in failed login attempts.  

Operational Efficiency Dashboards:

• Resource usage:
• User activity:
• Error tracking:

Compliance and Audit Reporting:

• Access control:
• Data changes:
• Generate reports: Create scheduled reports to be delivered to auditors or compliance officers, ensuring adherence to regulatory requirements.  

Interactive Data Exploration:

• Filters and drill-downs: Allow users to filter data by various criteria (e.g., time range, user, service) and drill down into specific events for detailed analysis.  
• Data blending: Combine audit log data with other relevant datasets (e.g., billing data, performance metrics) for a more comprehensive view.  
• Custom visualisations: Utilise Looker Studio's extensive library of charts and graphs or create custom visualisations to meet specific needs.  

Visualisations:

• A bar chart showing the number of successful and failed login attempts over time.
• A geomap visualising the location of user activity or users accessing sensitive data.
• A table listing all changes made to a specific database table, including user, timestamp, and changes made.
• A line graph showing the trend of API calls for a particular service.
• A table listing all data access events with details like user, timestamp, and resource accessed.

By leveraging Looker Studio's capabilities, you can transform raw audit log data into actionable insights, improve security posture, optimise operations, and ensure compliance.

Success Measurement

Reduction in security incidents: Track the number and severity of security incidents before and after implementing the solution.

Improved incident response time: Measure the time taken to detect and respond to security incidents.

Increased operational efficiency: Monitor key performance indicators (KPIs) related to resource utilisation and issue resolution.

Enhanced data-driven decision making: Evaluate the impact of data-driven insights on business decisions and outcomes.

Conclusion

Streaming Google Cloud audit logs into BigQuery and visualising the data in Looker Studio offers a powerful solution for enhancing security monitoring, improving operational efficiency, and enabling data-driven decision-making. By implementing this solution, organisations can gain valuable insights from their audit log data and achieve significant business benefits.

Ready to Unlock the Power of Your Audit Log Data? If you're looking to enhance security, improve operational efficiency, and drive data-driven decision-making with Google Cloud audit logs, we’re here to help. Reach out today to discuss how we can design a custom solution that transforms your data into valuable insights.