This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
- Google Cloud Security
- Security Forums
- SecOps SIEM
- Bridging SecOps Data & AI Analysis using the SecOp...
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Solved
LinkedIn
Twitter
Post Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Reply posted on
--/--/---- --:-- AM
Post Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You may already be aware that we announced Google SecOps API Wrapper SDK last month. There are couple of posts here and here.
The SDK provides a comprehensive command-line interface (CLI) that makes it easy to interact with Google Security Operations products from your terminal.
I'm using a simple command-line workflow that bridges the gap. It uses the standard SecOps SDK CLI tool and a custom Python script (gemini-shell.py) acting as a wrapper for the Gemini API.
We have 2 components
1-SecOps SDK CLI: Provides access to query SecOps data. We use it here to run queries (like SecOps stats ...) and export structured data summaries (JSON). It's our way to get the needed data out.
2-gemini-shell.py (Appendix 1) (Custom Script you can find it below): A Python script that acts as a command-line interface to the Gemini API.(you need to Get a Gemini API key)
Now, let's get into the action
I want to analyze the logs ingestion trends over time using stats query as the following
secops stats --query "metadata.product_name != \"\" \$date = timestamp.get_date(metadata.event_timestamp.seconds, \"America/Los_Angeles\") match:metadata.product_name, \$date outcome:\$event_count = count(metadata.id)"
This provides a lengthy json output (Appendix 2), you can see that manually analyzing this information would be difficult and time consuming.
I’ll run the stats command again but this time I will pass the JSON output through Gemini instead of viewing the raw data:
secops stats --query "metadata.product_name != \"\" \$date = timestamp.get_date(metadata.event_timestamp.seconds, \"America/Los_Angeles\") match:metadata.product_name, \$date outcome:\$event_count = count(metadata.id)" | python gemini-shell.py
This time I get the following actionable analysis directly in the terminal:
**Security Data Analytics Report**
**Analysis Period:** February 3, 2025 - April 18, 2025 (Based on provided log entry dates)
**Methodology:**
The analysis focuses on identifying anomalies through baseline deviation and event correlation, alongside runtime analysis of log volume velocity (spikes) and consistency (potential drops/silence). Baselines are inferred from the observed event counts for each product across the provided dates.
**Executive Summary:**
The analysis reveals a significant surge in overall log volume on **April 10, 2025**, driven by exceptionally high counts from multiple sources, most notably `Microsoft-Windows-Sysmon` and `ESENT`. The `ESENT` spike represents a massive deviation from its established baseline. Correlated increases across system monitoring (`Sysmon`), database engine (`ESENT`), package management (`GooGet`, `OSConfigAgent`), and security auditing tools on this date suggest a major system event, potentially an update, deployment, configuration change, or a security incident, requiring investigation. A relative dip in log volume for key sources (`Sysmon`, `Service Control Manager`) was noted on **February 5, 2025**. Additionally, significant gaps exist in the reporting timeline for high-volume Windows sources between mid-February and early April, which could indicate potential blind spots if continuous logging was expected.
**1. Runtime Analysis: Log Flow Velocity & Consistency**
* **Significant Spikes:**
* **Date:** April 10, 2025
* **Observation:** A dramatic increase in overall log volume compared to other observed days.
* **Affected Sources & Quantification:**
* `Microsoft-Windows-Sysmon`: **332,031 events**. This is the highest recorded volume, approximately **3x** its average observed volume (~100k-110k) and significantly higher than the next highest day (176k on Feb 3).
* `ESENT`: **576 events**. This is an extreme anomaly. Its typical daily volume in this dataset is <15 events (average ~44 due to this spike, median ~7). This represents an **~80x increase** over its median baseline.
* `GooGet`: **426 events**. Nearly double the count from the previous day (218 on Apr 9).
* `OSConfigAgent`: **270 events**. Double the count from the previous day (134 on Apr 9).
* `Service Control Manager`: **249 events**. Over **2x** its typical volume (average ~112, median ~98).
* `Microsoft-Windows-Security-Auditing`: **290 events**. Over **2x** its typical volume (average ~122, median ~99).
* `Microsoft-Windows-Security-SPP`: **56 events**. Roughly **3x** its typical volume (average ~18, median ~16).
* **Significance:** Such a widespread and correlated spike across system internals (Sysmon, ESENT, SCM), security logs (Security Auditing, SPP), and potentially cloud management tools (GooGet, OSConfigAgent) strongly indicates a significant system-wide event. This requires immediate investigation to determine the cause (e.g., large software deployment, patching cycle, misconfiguration, or malicious activity).
* **Potential Blind Spots / Consistency Issues:**
* **Relative Volume Drop (Feb 5, 2025):**
* `Microsoft-Windows-Sysmon`: Dropped to **36,365 events**, significantly lower than Feb 3 (176k) and Feb 4 (106k), and subsequent days in Feb (84k, 59k).
* `Service Control Manager`: Dropped to **28 events**, compared to Feb 3 (171) and Feb 4 (93).
* **Significance:** While not complete silence, this synchronized dip in volume for key monitoring components compared to adjacent days is unusual. It could indicate reduced system activity, but warrants checking for potential partial logging failures or service interruptions on that day.
* **Reporting Gaps:**
* **Observation:** There's a significant gap in reported logs for core Windows sources (like Sysmon, Security Auditing, Service Control Manager) between **February 12, 2025, and April 9, 2025**. Only sporadic, low-volume events from sources like `Google Cloud Platform`, `Google Cloud IAM`, and `WinEventLog:Security` are present during March.
* **Significance:** If the systems generating these Windows logs were operational and expected to be logging continuously during this period (late Feb - March), this gap represents a major potential **blind spot**. It could be due to log shipping issues, system outages, or deliberate tampering. This needs verification against system uptime and logging configuration records.
**2. Anomaly Detection**
* **Extreme Baseline Deviation (ESENT):**
* **Observation:** The `ESENT` (Extensible Storage Engine - used by many Windows components like Windows Update, Active Directory, Search Indexing) log volume spiked to **576 events on April 10, 2025**.
* **Baseline Comparison:** Its typical volume on other days ranges from 3 to 15 events.
* **Significance:** This >50x increase over its normal maximum observed rate is highly anomalous. It could indicate database corruption, very heavy database activity (perhaps related to updates or indexing triggered by the events of Apr 10), or a specific error state. This specific anomaly requires focused investigation.
* **Correlated Activity Spike (April 10, 2025):**
* **Observation:** As noted in the runtime analysis, multiple, diverse log sources spiked simultaneously on April 10th.
* **Significance:** Simple pattern matching might miss this if looking at sources individually without considering their typical baselines. The *correlation* of spikes across `Sysmon` (deep system activity), `ESENT` (database engine), `SecAudit` (security events), `SCM` (service changes), `GooGet`/`OSConfigAgent` (GCP management/updates), and `SPP` (Licensing/Activation) strongly suggests a coordinated or system-wide event impacting multiple facets of the operating system and its management layer.
* **Elevated Activity (Feb 3, 2025):**
* **Observation:** `Microsoft-Windows-Sysmon` (176k), `Service Control Manager` (171), and `Microsoft-Windows-Security-Auditing` (184) all showed relatively high counts compared to most other days in February.
* **Significance:** While not as extreme as April 10th, this earlier date also shows elevated activity across key monitoring sources, potentially indicating another significant event or the start of related activity.
**Recommendations:**
1. **Investigate April 10th Event:** Prioritize investigation into the cause of the massive, correlated log spike on April 10, 2025. Examine the specific Sysmon, Security Auditing, ESENT, and Service Control Manager events from that day for indicators of compromise, large-scale deployment issues, or significant configuration changes. Correlate with known change management activities.
2. **Analyze ESENT Anomaly:** Drill down into the specific ESENT events from April 10th to understand the nature of the spike (e.g., specific error codes, database operations).
3. **Verify Logging Consistency:** Investigate the potential blind spot between Feb 12 and Apr 9. Confirm if systems were operational and if logs were expected. Check log forwarder health and configuration. Examine the cause of the relative volume dip on Feb 5th.
4. **Establish Robust Baselines:** Continue monitoring these sources to refine baselines over a longer period, enabling more accurate future anomaly detection. Consider percentile-based thresholds in addition to standard deviations for high-volume, variable sources like Sysmon.
So, as you can see from the detailed report generated, this simple workflow transforms the raw lengthy JSON output from the SecOps CLI into actionable security intelligence.
Using this simple approach can help to spot anomalies, analyze trends, and identify potential blind spots directly from the command line.
I hope this provides a practical way to bridge the gap and bring powerful AI analysis to your SecOps data. Let me know your thoughts or if you have similar workflows!
(Find the gemini-shell.py script used in this example and json output below.)
Appendix 1
##gemini-shell.py
import base64
import os
import sys
from google import genai
from google.genai import types
def generate(user_prompt):
api_key = os.environ.get("GEMINI_API_KEY")
if not api_key:
print("Error: GEMINI_API_KEY environment variable not set.", file=sys.stderr)
sys.exit(1)
try:
client = genai.Client(api_key=api_key)
except Exception as e:
print(f"Error initializing Google GenAI client: {e}", file=sys.stderr)
sys.exit(1)
model_name = "models/gemini-2.5-pro-preview-03-25"
contents = [
types.Content(
role="user",
parts=[
types.Part.from_text(text=user_prompt),
],
),
]
system_instruction_text = """As a specialist in Security Data Analytics, your task is to analyze provided security logs to generate actionable insights. Focus on detecting anomalies by identifying subtle deviations, unusual event sequences, or correlations indicative of potential threats or misconfigurations, leveraging baseline comparisons beyond simple pattern matching. Simultaneously, perform runtime analysis of log flow velocity and consistency to pinpoint significant spikes—quantifying sharp volume increases against the norm and noting affected sources/event types—and potential blind spots by detecting periods of unexpected silence or drastically reduced volume from expected sources, thereby flagging possible logging failures or system outages. Prioritize clear communication in your analysis, explaining the significance of any identified anomalies or volume changes and supporting your findings with specific log evidence."""
generate_content_config_object = types.GenerateContentConfig(
response_mime_type="text/plain",
system_instruction=[
types.Part.from_text(text=system_instruction_text),
],
)
try:
response_stream = client.models.generate_content_stream(
model=model_name,
contents=contents,
config=generate_content_config_object,
)
for chunk in response_stream:
try:
if chunk.text:
print(chunk.text, end="", flush=True)
except ValueError:
pass
print()
except Exception as e:
print(f"\nAn error occurred during content generation: {e}", file=sys.stderr)
sys.exit(1)
if __name__ == "__main__":
user_prompt = None
if not sys.stdin.isatty():
user_prompt = sys.stdin.read().strip()
elif len(sys.argv) > 1:
user_prompt = " ".join(sys.argv[1:])
if user_prompt:
generate(user_prompt)
else:
script_name = os.path.basename(sys.argv[0])
print(f"Usage:", file=sys.stderr)
print(f" echo \"Your prompt\" | python {script_name}", file=sys.stderr)
print(f" python {script_name} Your prompt here as arguments", file=sys.stderr)
print(f"\nError: No prompt provided via pipe or arguments.", file=sys.stderr)
sys.exit(1)
Appendix 2
{
"total_rows": 104,
"columns": [
"metadata.product_name",
"date",
"event_count"
],
"rows": [
{
"metadata.product_name": "Microsoft-Windows-Security-SPP",
"date": "2025-02-03",
"event_count": 18
},
{
"metadata.product_name": "ESENT",
"date": "2025-04-09",
"event_count": 9
},
{
"metadata.product_name": "Google Cloud Platform",
"date": "2025-02-07",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Security-SPP",
"date": "2025-02-04",
"event_count": 7
},
{
"metadata.product_name": "Microsoft-Windows-Winlogon",
"date": "2025-02-12",
"event_count": 2
},
{
"metadata.product_name": "C:\\Program Files\\Google\\Compute Engine\\metadata_scripts\\GCEMetadataScripts.exe",
"date": "2025-02-03",
"event_count": 2
},
{
"metadata.product_name": "GooGet",
"date": "2025-04-09",
"event_count": 218
},
{
"metadata.product_name": "User32",
"date": "2025-02-03",
"event_count": 1
},
{
"metadata.product_name": "Desktop Window Manager",
"date": "2025-04-09",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Kernel-General",
"date": "2025-04-09",
"event_count": 1
},
{
"metadata.product_name": "User32",
"date": "2025-02-04",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Winlogon",
"date": "2025-02-05",
"event_count": 2
},
{
"metadata.product_name": "WinEventLog:Security",
"date": "2025-03-19",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-02-12",
"event_count": 35
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-02-12",
"event_count": 59371
},
{
"metadata.product_name": "Microsoft-Windows-Security-SPP",
"date": "2025-04-09",
"event_count": 27
},
{
"metadata.product_name": "User32",
"date": "2025-02-05",
"event_count": 1
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-02-05",
"event_count": 28
},
{
"metadata.product_name": "edgeupdate",
"date": "2025-02-03",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Kernel-General",
"date": "2025-04-10",
"event_count": 4
},
{
"metadata.product_name": "Microsoft-Windows-WindowsUpdateClient",
"date": "2025-02-05",
"event_count": 2
},
{
"metadata.product_name": "ESENT",
"date": "2025-02-04",
"event_count": 3
},
{
"metadata.product_name": "ESENT",
"date": "2025-02-03",
"event_count": 15
},
{
"metadata.product_name": "ESENT",
"date": "2025-02-05",
"event_count": 6
},
{
"metadata.product_name": "edgeupdate",
"date": "2025-04-10",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-LoadPerf",
"date": "2025-04-09",
"event_count": 4
},
{
"metadata.product_name": "Microsoft-Windows-Search",
"date": "2025-04-09",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-02-04",
"event_count": 117
},
{
"metadata.product_name": "Google Compute Engine",
"date": "2025-02-20",
"event_count": 64
},
{
"metadata.product_name": "Windows Error Reporting",
"date": "2025-02-03",
"event_count": 5
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-02-05",
"event_count": 36365
},
{
"metadata.product_name": "Microsoft-Windows-LoadPerf",
"date": "2025-02-11",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-02-03",
"event_count": 176780
},
{
"metadata.product_name": "Google Cloud IAM",
"date": "2025-02-25",
"event_count": 9
},
{
"metadata.product_name": "Desktop Window Manager",
"date": "2025-02-04",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-02-11",
"event_count": 78
},
{
"metadata.product_name": "Google Cloud Platform",
"date": "2025-02-25",
"event_count": 25
},
{
"metadata.product_name": "C:\\Program Files\\Google\\Compute Engine\\metadata_scripts\\GCEMetadataScripts.exe",
"date": "2025-02-05",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-04-10",
"event_count": 290
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-02-11",
"event_count": 64
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-02-03",
"event_count": 171
},
{
"metadata.product_name": "Microsoft-Windows-Kernel-General",
"date": "2025-02-11",
"event_count": 1
},
{
"metadata.product_name": "ESENT",
"date": "2025-02-11",
"event_count": 6
},
{
"metadata.product_name": "edgeupdate",
"date": "2025-04-09",
"event_count": 1
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-04-09",
"event_count": 103
},
{
"metadata.product_name": "Microsoft-Windows-LoadPerf",
"date": "2025-02-03",
"event_count": 6
},
{
"metadata.product_name": "Windows Error Reporting",
"date": "2025-04-10",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Winlogon",
"date": "2025-02-04",
"event_count": 3
},
{
"metadata.product_name": "Microsoft-Windows-Security-SPP",
"date": "2025-04-10",
"event_count": 56
},
{
"metadata.product_name": "Microsoft-Windows-DistributedCOM",
"date": "2025-02-04",
"event_count": 1
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-02-12",
"event_count": 79
},
{
"metadata.product_name": "edgeupdate",
"date": "2025-02-11",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-02-05",
"event_count": 52
},
{
"metadata.product_name": "User32",
"date": "2025-02-12",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-WindowsUpdateClient",
"date": "2025-02-11",
"event_count": 10
},
{
"metadata.product_name": "EventLog",
"date": "2025-04-09",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Search",
"date": "2025-04-10",
"event_count": 2
},
{
"metadata.product_name": "ESENT",
"date": "2025-04-10",
"event_count": 576
},
{
"metadata.product_name": "Microsoft-Windows-LoadPerf",
"date": "2025-04-10",
"event_count": 6
},
{
"metadata.product_name": "Windows Error Reporting",
"date": "2025-02-11",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Time-Service",
"date": "2025-02-11",
"event_count": 1
},
{
"metadata.product_name": "Google Cloud Platform",
"date": "2025-02-20",
"event_count": 5
},
{
"metadata.product_name": "Microsoft-Windows-WindowsUpdateClient",
"date": "2025-04-10",
"event_count": 14
},
{
"metadata.product_name": "EventLog",
"date": "2025-02-12",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-02-11",
"event_count": 84077
},
{
"metadata.product_name": "C:\\Program Files\\Google\\Compute Engine\\metadata_scripts\\GCEMetadataScripts.exe",
"date": "2025-04-09",
"event_count": 2
},
{
"metadata.product_name": "Desktop Window Manager",
"date": "2025-04-10",
"event_count": 3
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-04-09",
"event_count": 136382
},
{
"metadata.product_name": "Google Cloud Platform",
"date": "2025-04-18",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Security-SPP",
"date": "2025-02-05",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Winlogon",
"date": "2025-02-03",
"event_count": 3
},
{
"metadata.product_name": "EventLog",
"date": "2025-02-03",
"event_count": 1
},
{
"metadata.product_name": "Chrome",
"date": "2025-04-10",
"event_count": 2
},
{
"metadata.product_name": "Desktop Window Manager",
"date": "2025-02-03",
"event_count": 1
},
{
"metadata.product_name": "GooGet",
"date": "2025-04-10",
"event_count": 426
},
{
"metadata.product_name": "Microsoft-Windows-Time-Service",
"date": "2025-04-10",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-WindowsUpdateClient",
"date": "2025-04-09",
"event_count": 9
},
{
"metadata.product_name": "Google Cloud Platform",
"date": "2025-02-03",
"event_count": 11
},
{
"metadata.product_name": "Chrome",
"date": "2025-02-04",
"event_count": 2
},
{
"metadata.product_name": "OSConfigAgent",
"date": "2025-04-09",
"event_count": 134
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-04-10",
"event_count": 249
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-02-04",
"event_count": 106238
},
{
"metadata.product_name": "Service Control Manager",
"date": "2025-02-04",
"event_count": 93
},
{
"metadata.product_name": "Microsoft-Windows-LoadPerf",
"date": "2025-02-04",
"event_count": 4
},
{
"metadata.product_name": "User32",
"date": "2025-04-10",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Winlogon",
"date": "2025-04-10",
"event_count": 6
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-02-03",
"event_count": 184
},
{
"metadata.product_name": "Microsoft-Windows-Search",
"date": "2025-02-03",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-WindowsUpdateClient",
"date": "2025-02-03",
"event_count": 14
},
{
"metadata.product_name": "C:\\Program Files\\Google\\Compute Engine\\metadata_scripts\\GCEMetadataScripts.exe",
"date": "2025-04-10",
"event_count": 4
},
{
"metadata.product_name": "Microsoft-Windows-Security-SPP",
"date": "2025-02-11",
"event_count": 14
},
{
"metadata.product_name": "Microsoft-Windows-Security-Auditing",
"date": "2025-04-09",
"event_count": 99
},
{
"metadata.product_name": "Microsoft-Windows-DistributedCOM",
"date": "2025-04-10",
"event_count": 2
},
{
"metadata.product_name": "Google Cloud Platform",
"date": "2025-03-17",
"event_count": 2
},
{
"metadata.product_name": "OSConfigAgent",
"date": "2025-04-10",
"event_count": 270
},
{
"metadata.product_name": "User32",
"date": "2025-04-09",
"event_count": 1
},
{
"metadata.product_name": "Windows Error Reporting",
"date": "2025-04-09",
"event_count": 2
},
{
"metadata.product_name": "Microsoft-Windows-Sysmon",
"date": "2025-04-10",
"event_count": 332031
},
{
"metadata.product_name": "Google Cloud IAM",
"date": "2025-04-18",
"event_count": 5
},
{
"metadata.product_name": "Microsoft-Windows-WindowsUpdateClient",
"date": "2025-02-04",
"event_count": 7
},
{
"metadata.product_name": "Microsoft-Windows-DistributedCOM",
"date": "2025-02-03",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Search",
"date": "2025-02-04",
"event_count": 1
},
{
"metadata.product_name": "Microsoft-Windows-Time-Service",
"date": "2025-02-05",
"event_count": 1
},
{
"metadata.product_name": "Google Cloud IAM",
"date": "2025-02-20",
"event_count": 1
}
]
}
0 REPLIES 0
Top Labels in this Space
-
Admin
64 -
AI
5 -
API
71 -
Applied Analytics
3 -
BigQuery
11 -
Browser Management
3 -
Chrome Enterprise
4 -
Chronicle
271 -
Compliance
9 -
Curated Detections
29 -
Custom List
1 -
Dashboard
76 -
Data Management
59 -
Ingestion
196 -
Investigation
48 -
Logs
2 -
MCP
1 -
Parsers
158 -
Rules Engine
156 -
Search
76 -
SecOps
274 -
Security Command Center
1 -
SIEM
499 -
Siemplify
1 -
Slack
1 -
SOAR
51 -
Spotlight series
1 -
Threat Intelligence
37 -
Troubleshooting
62
- « Previous
- Next »
