KQLBOT

KQLBOT is a Python-based command-line tool for executing KQL (Kusto Query Language) queries against Azure Log Analytics workspaces via the REST API. It returns results in structured JSON format, making it ideal for automation workflows and data analysis.

Features

• Secure authentication using Azure client credentials
• Flexible query input from command line or file
• Configurable API parameters via config file or CLI overrides
• Multiple output formats: JSON (default) and table format for readability
• Automated credential setup with interactive PowerShell script
• Environment variable management via .env files
• Comprehensive error handling with user-friendly messages and exit codes
• Fully tested with unit and integration tests

Prerequisites

• Python 3.8 or higher
• Azure subscription with Log Analytics workspace
• Azure AD app registration with appropriate permissions

Setup

1. Clone the repository:

   git clone <repository-url>
   cd PySecAgent

2. Create and activate virtual environment:

   python -m venv .venv
   .venv\Scripts\activate  # On Windows

3. Install dependencies:

   pip install -r requirements.txt

4. Configure Azure credentials:

   Quick Setup (Recommended):

   .\setup_credentials.ps1

   Or manually create a .env file with your credentials:

   AZURE_CLIENT_ID=your-client-id
   AZURE_CLIENT_SECRET=your-client-secret
   AZURE_TENANT_ID=your-tenant-id
   AZURE_WORKSPACE_ID=your-workspace-id

   Alternative Methods:

   • Environment Variables: Set AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, AZURE_TENANT_ID, and AZURE_WORKSPACE_ID in your system environment variables
   • Manual Session Setup: Set environment variables each session (not recommended for production)

   ⚠️ Important: Do not set Azure credentials in your PowerShell profile as they will override the .env file. The recommended approach is using the .env file created by setup_credentials.ps1.

   This will interactively prompt for your credentials and create the .env file automatically.

5. Configure API parameters in API.cfg (optional, defaults provided):

   [DEFAULT]
   timeframe = 24h
   max_rows = 1000
   top_count = 100
   timespan = P1D

Usage

Basic Query Execution

python kqlbot.py "SecurityIncident | limit 1"

With Workspace ID Override

python kqlbot.py --workspace-id your-workspace-id "StormEvents | limit 10"

Query from File

python kqlbot.py --query-file query.kql

Override Config Parameters

python kqlbot.py --workspace-id your-workspace-id --max-rows 500 --timespan P7D "StormEvents | where EventType == 'Tornado'"

Output to File

python kqlbot.py --workspace-id your-workspace-id --output results.json "StormEvents | limit 10"

Output Formats

KQLBOT supports multiple output formats:

JSON Format (default):

python kqlbot.py --format json "SecurityIncident | limit 5"

Table Format:

python kqlbot.py --format table "SecurityIncident | limit 5"

The table format displays key columns in a readable grid format, perfect for quick data inspection.

Command Line Options

• --workspace-id: Azure Log Analytics workspace ID (optional if AZURE_WORKSPACE_ID is set)
• --query-file: Path to file containing KQL query
• --timeframe: Timeframe for queries (default from config)
• --max-rows: Maximum rows to return (default from config)
• --top-count: Top count for queries (default from config)
• --timespan: Timespan for queries (default from config)
• --output: Output file for results (default: stdout)
• --format: Output format - 'json' or 'table' (default: json)
• query: KQL query string (if not using --query-file)

Error Handling

KQLBOT provides comprehensive error handling with clear, actionable error messages and appropriate exit codes:

Exit Codes

• 0: Success
• 1: Unexpected error
• 2: Configuration error (missing credentials, invalid parameters)
• 3: File error (missing or inaccessible files)
• 4: Permission error
• 5: Authentication error
• 6: Query execution error
• 7: Runtime error
• 130: Operation cancelled by user (Ctrl+C)

Common Error Scenarios

Missing Azure Credentials:

❌ Configuration Error: Azure credentials must be provided via parameters or environment variables (AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, AZURE_TENANT_ID)

💡 To fix this:
   - Set environment variables:
     $env:AZURE_CLIENT_ID = 'your-client-id'
     $env:AZURE_CLIENT_SECRET = 'your-client-secret'
     $env:AZURE_TENANT_ID = 'your-tenant-id'

Missing Query File:

❌ File Error: Query file not found: nonexistent.kql

💡 To fix this:
   - Check that the query file exists
   - Verify the file path is correct
   - Use absolute path if relative path doesn't work

Invalid KQL Query:

❌ Query Error: Query failed: Bad request. Check query syntax and parameters.

💡 To fix this:
   - Check KQL syntax in your query
   - Verify table names exist in your workspace
   - Ensure column names are correct
   - Test query in Azure portal first

Authentication Issues:

❌ Authentication Error: Authentication failed: Invalid client credentials or tenant ID.

💡 To fix this:
   - Verify AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, and AZURE_TENANT_ID
   - Check that your Azure AD app has the correct permissions
   - Ensure the app is registered in the correct tenant

Testing

Run the test suite:

pytest

Logging

KQLBOT logs information about authentication and query execution. Set logging level by modifying the code if needed.

Security Notes

• Never hardcode credentials in the code
• Use environment variables or secure credential stores
• The tool uses Azure AD client credentials flow for secure access
• API.cfg is excluded from version control for security
• Important: The .env file contains sensitive credentials and is automatically excluded from version control. Never commit it to your repository.

Contributing

1. Follow PEP 8 style guidelines
2. Add tests for new features
3. Update documentation as needed

License

[Add license information]