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:
Real-time analysis of audit logs for suspicious activities.
Proactive identification and mitigation of security threats.
Improved compliance with security regulations and standards.
Improved Operational Efficiency:
Gain insights into resource usage, API calls, and user activity.
Identify performance bottlenecks and optimise resource allocation.
Troubleshoot issues faster with detailed audit trails.
Data-Driven Decision Making:
Visualise key trends and patterns in audit log data.
Generate reports for auditing, compliance, and performance analysis.
Make informed decisions based on comprehensive data insights.
Cost Optimisation:
BigQuery's pay-as-you-go pricing model ensures cost-effectiveness.
Looker Studio's free access allows for cost-efficient data visualisation.
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.
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:
Go to Logging > Logs Router.
Click Create Sink.
Choose BigQuery dataset as the sink destination.
Select your project and dataset.
Define a filter to select specific audit logs.
Create the sink.
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.
Connecting BigQuery to Looker Studio
Go to Looker Studio and create a new report.
Click Add data.
Select BigQuery as your data source.
Choose your project and dataset.
Select the relevant table containing audit logs.
Click Connect.
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:
Geomap: Display the geographical origin of suspicious login attempts.
Timeline: Show the frequency of security events over time to identify patterns or anomalies.
Bar charts: Compare the number of failed login attempts across different users or services.
Tables: List recent security events with detailed information like user, IP address, and event type.
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:
Pie charts: Show the distribution of API calls across different services.
Line graphs: Track resource consumption (e.g., storage, compute) over time to identify trends and optimize resource allocation.
User activity:
Heatmaps: Visualise user activity patterns across different time periods and identify peak usage hours.
Scatter plots: Analyse the correlation between user activity and performance metrics.
Error tracking:
Bar charts: Display the frequency of different error types across services.
Tables: List recent errors with detailed information to aid in troubleshooting.
Compliance and Audit Reporting:
Access control:
Tables: List users with access to sensitive data or resources.
Matrix: Visualise the relationship between users and their permissions.
Data changes:
Timelines: Track changes to critical data over time.
Tables: Log all data modifications with details like user, timestamp, and changes made.
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.
