Dalonso Security Repo

Skill by ara.so — Security Skills collection.

A comprehensive collection of Microsoft Security resources including KQL queries, threat hunting use cases, Microsoft Sentinel workbooks, and Jupyter notebooks for security analytics and investigation workflows.

What This Repository Provides

The Dalonso Security Repo is a curated knowledge base for Microsoft Security technologies:

• Threat Hunting Use Cases: Real-world KQL (Kusto Query Language) queries for proactive threat detection
• Microsoft Sentinel Workbooks: Custom visualization and analysis dashboards
• Security Analytics Notebooks: Jupyter notebooks for advanced investigation and automation
• Practical Examples: Production-ready queries and workflows for security operations

Installation

Clone the repository to access all resources:

git clone https://github.com/davidalonsod/Dalonso-Security-Repo.git
cd Dalonso-Security-Repo

For Jupyter notebooks, install required dependencies:

pip install jupyter msticpy pandas azure-monitor-query azure-identity

Repository Structure

The repository is organized by security tool and use case:

Dalonso-Security-Repo/
├── Threat-Hunting/          # KQL queries for threat hunting
├── Sentinel-Workbooks/      # Custom Sentinel workbooks (JSON)
├── Notebooks/               # Jupyter notebooks (.ipynb)
├── Detection-Rules/         # Analytics rules and detections
└── Investigation-Queries/   # Ad-hoc investigation KQL

Working with KQL Queries

Basic KQL Query Structure

KQL queries in this repo follow standard Microsoft Sentinel/Log Analytics syntax:

// Example: Detecting suspicious PowerShell execution
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4688
| where Process has "powershell.exe"
| where CommandLine has_any ("bypass", "encoded", "hidden")
| project TimeGenerated, Computer, Account, CommandLine, ParentProcessName
| order by TimeGenerated desc

Using Queries in Microsoft Sentinel

1. Navigate to Microsoft Sentinel > Logs
2. Copy the KQL query from the repository
3. Paste into the query editor
4. Adjust time ranges and parameters as needed
5. Run and analyze results

Threat Hunting Query Pattern

// Pattern: Multi-stage threat hunting
let suspiciousIPs = datatable(IPAddress:string)
[
    "192.0.2.1",
    "198.51.100.1"
];
let timeframe = 7d;
CommonSecurityLog
| where TimeGenerated > ago(timeframe)
| where DestinationIP in (suspiciousIPs)
| join kind=inner (
    SigninLogs
    | where TimeGenerated > ago(timeframe)
    | where ResultType == "0"
) on $left.SourceIP == $right.IPAddress
| project TimeGenerated, SourceIP, DestinationIP, UserPrincipalName, DeviceDetail
| summarize count() by SourceIP, UserPrincipalName

Working with Sentinel Workbooks

Importing a Workbook

1. Download the workbook JSON file from the repository
2. In Microsoft Sentinel > Workbooks > Add workbook
3. Click Edit > Advanced Editor (</> icon)
4. Replace content with the downloaded JSON
5. Click Apply > Save

Workbook JSON Structure

{
  "version": "Notebook/1.0",
  "items": [
    {
      "type": 3,
      "content": {
        "version": "KqlItem/1.0",
        "query": "SecurityEvent\n| summarize count() by Computer\n| top 10 by count_",
        "size": 0,
        "title": "Top 10 Event Sources"
      }
    }
  ]
}

Using Security Analytics Notebooks

Setting Up Jupyter Environment

# Install required packages
# pip install msticpy azure-monitor-query azure-identity pandas matplotlib

import msticpy as mp
from msticpy.data import QueryProvider
from azure.identity import AzureCliCredential
import pandas as pd
import os

# Initialize MSTICPy
mp.init_notebook()

Connecting to Microsoft Sentinel

# Authenticate using Azure CLI or environment variables
qry_prov = QueryProvider("MSSentinel")

# Configure workspace
workspace_id = os.getenv("SENTINEL_WORKSPACE_ID")
tenant_id = os.getenv("AZURE_TENANT_ID")

qry_prov.connect(
    connection_str=f"loganalytics://code().tenant('{tenant_id}').workspace('{workspace_id}')"
)

Running Threat Hunting Queries

# Example: Hunt for lateral movement
query = """
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID == 4624
| where LogonType == 3
| summarize LogonCount = count() by Account, Computer, IpAddress
| where LogonCount > 10
| order by LogonCount desc
"""

results = qry_prov.exec_query(query)
print(f"Found {len(results)} potential lateral movement events")
results.head(10)

Data Analysis Pattern

# Analyze authentication anomalies
import matplotlib.pyplot as plt

def analyze_auth_patterns(dataframe):
    """Analyze authentication patterns for anomalies"""
    
    # Group by hour
    dataframe['Hour'] = pd.to_datetime(dataframe['TimeGenerated']).dt.hour
    hourly_counts = dataframe.groupby('Hour').size()
    
    # Plot distribution
    plt.figure(figsize=(12, 6))
    hourly_counts.plot(kind='bar')
    plt.title('Authentication Events by Hour')
    plt.xlabel('Hour of Day')
    plt.ylabel('Event Count')
    plt.show()
    
    # Identify anomalies (simple threshold)
    mean_count = hourly_counts.mean()
    std_count = hourly_counts.std()
    threshold = mean_count + (2 * std_count)
    
    anomalies = hourly_counts[hourly_counts > threshold]
    return anomalies

# Use the function
auth_data = qry_prov.exec_query("SigninLogs | where TimeGenerated > ago(7d)")
anomalies = analyze_auth_patterns(auth_data)
print(f"Anomalous hours: {anomalies.index.tolist()}")

Common Detection Patterns

Credential Access Detection

// Detect LSASS memory access
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID == 4656
| where ObjectName has "lsass.exe"
| where AccessMask has "0x1010"
| project TimeGenerated, Computer, SubjectUserName, ProcessName, ObjectName

Persistence Mechanism Detection

// Monitor registry run keys
SecurityEvent
| where TimeGenerated > ago(24h)
| where EventID in (4657, 4663)
| where ObjectName has_any (
    "\\Software\\Microsoft\\Windows\\CurrentVersion\\Run",
    "\\Software\\Microsoft\\Windows\\CurrentVersion\\RunOnce"
)
| project TimeGenerated, Computer, Account, ObjectName, ProcessName

Command and Control Detection

// Detect beaconing behavior
CommonSecurityLog
| where TimeGenerated > ago(24h)
| summarize ConnectionCount = count(), 
            AvgBytes = avg(SentBytes + ReceivedBytes),
            TimeVariance = stdev(bin(TimeGenerated, 1m))
            by SourceIP, DestinationIP
| where ConnectionCount > 50
| where TimeVariance < 5  // Consistent timing
| where AvgBytes < 1000   // Small payloads

Configuration

Environment Variables

Set up authentication for notebooks:

export AZURE_TENANT_ID="your-tenant-id"
export SENTINEL_WORKSPACE_ID="your-workspace-id"
export AZURE_CLIENT_ID="your-service-principal-id"
export AZURE_CLIENT_SECRET="your-service-principal-secret"

MSTICPy Configuration

Create msticpyconfig.yaml in your working directory:

AzureSentinel:
  Workspaces:
    Default:
      WorkspaceId: ${SENTINEL_WORKSPACE_ID}
      TenantId: ${AZURE_TENANT_ID}

TIProviders:
  VirusTotal:
    Args:
      AuthKey: ${VT_API_KEY}
    Primary: true
    Provider: "VirusTotal"

Troubleshooting

KQL Query Timeout

If queries timeout, optimize with:

// Use time filters early
| where TimeGenerated > ago(1h)
// Limit columns
| project TimeGenerated, Computer, Account
// Use summarize instead of distinct when possible
| summarize count() by Computer

Notebook Authentication Issues

# Try interactive authentication
from azure.identity import InteractiveBrowserCredential

credential = InteractiveBrowserCredential()
qry_prov.connect(credential=credential)

Missing Data in Queries

Verify data connectors are enabled and tables exist:

// List available tables
search *
| distinct $table
| order by $table asc

Performance Optimization

// Use materialize() for reusable subqueries
let suspiciousEvents = materialize(
    SecurityEvent
    | where TimeGenerated > ago(24h)
    | where EventID in (4624, 4625, 4688)
);
suspiciousEvents
| where EventID == 4624
| summarize count() by Computer

Best Practices

1. Always use time filters early in queries to reduce data scanned
2. Test queries on small timeframes before expanding scope
3. Document custom queries with comments explaining detection logic
4. Version control workbooks by exporting JSON regularly
5. Use environment variables for sensitive configuration
6. Leverage MSTICPy for complex investigation workflows
7. Schedule notebooks for automated threat hunting runs

Additional Resources

• David Alonso LinkedIn
• Microsoft Sentinel Documentation
• KQL Reference
• MSTICPy Documentation