reporting

What this skill does

# LimaCharlie Reporting Skill

---

## LimaCharlie Integration

> **Prerequisites**: Run `/init-lc` to initialize LimaCharlie context.

### LimaCharlie CLI Access

All LimaCharlie operations use the `limacharlie` CLI directly:

```bash
limacharlie <noun> <verb> --oid <oid> --output yaml [flags]
```

For command help and discovery: `limacharlie <command> --ai-help`

### Critical Rules

| Rule | Wrong | Right |
|------|-------|-------|
| **CLI Access** | Call MCP tools or spawn api-executor | Use `Bash("limacharlie ...")` directly |
| **Output Format** | `--output json` | `--output yaml` (more token-efficient) |
| **Filter Output** | Pipe to jq/yq | Use `--filter JMESPATH` to select fields |
| **LCQL Queries** | Write query syntax manually | Use `limacharlie ai generate-query` first |
| **Timestamps** | Calculate epoch values | Use `date +%s` or `date -d '7 days ago' +%s` |
| **OID** | Use org name | Use UUID (call `limacharlie org list` if needed) |

---

## Overview

This skill enables AI-assisted generation of comprehensive security and operational reports across LimaCharlie organizations. It provides structured access to billing data, usage statistics, detection summaries, sensor health, and configuration audits. Supports both per-tenant detailed reports and cross-tenant aggregated roll-ups.

**Core Philosophy**: Accuracy over completeness. This skill prioritizes data accuracy with strict guardrails that make fabricated metrics impossible. Reports clearly document what data is available, what failed, and what limits were applied.

## Purpose

- Generate multi-tenant reports across 50+ organizations
- Provide billing and usage summaries for customer invoicing
- Analyze security detection trends across customer base
- Monitor sensor health and deployment status
- Audit organizational configurations
- Track operational metrics for capacity planning
- Support partial report generation with clear error documentation

## When to Use This Skill

Use this skill when you need to:

### Multi-Tenant MSSP Reports
- **"Generate monthly report for all my customers"** - Comprehensive overview across all organizations
- **"Billing summary for November 2025"** - Usage and billing data for invoicing period
- **"Show me customer health dashboard"** - Sensor status and detection trends across clients
- **"Which customers had the most detections this month?"** - Security activity ranking
- **"Export usage data for all organizations"** - Bulk data extraction for analysis

### Single Organization Deep Dives
- **"Detailed report for Client ABC"** - Complete organizational analysis
- **"Security posture for organization XYZ"** - Detection and rule effectiveness
- **"Sensor health for customer PDQ"** - Endpoint deployment and status

### Billing and Usage Analysis
- **"Usage trends across all customers"** - Comparative analysis for capacity planning
- **"Which orgs are using the most data?"** - Resource consumption identification
- **"Show subscription status for all clients"** - Billing health check

### Operational Monitoring
- **"How many sensors are offline across all orgs?"** - Fleet health monitoring
- **"Detection volume trends this month"** - Security activity patterns
- **"Which customers need attention?"** - Issue identification and prioritization

## Report Templates

This skill supports structured JSON templates that define input schemas, output schemas, and data sources for each report type. Templates are located in `skills/reporting/templates/`.

| Template | Description | Scope |
|----------|-------------|-------|
| `billing-report.json` | Invoice-focused billing data with SKU breakdown | single / all |
| `mssp-executive-report.json` | High-level fleet health for MSSP leadership | single / all |
| `customer-health-report.json` | Comprehensive customer success tracking | single / all |
| `detection-analytics-report.json` | Detection volume, categories, and trends | single / all |

### Template Structure

Each template defines:
- **Input schema**: Required and optional parameters with types and validation
- **Output schema**: Expected JSON structure for structured data consumers
- **Data sources**: Which API calls populate each field

### Using Templates

1. **Read the template** to understand required inputs
2. **Validate user input** against the input schema
3. **Use the orchestration layer** (parallel subagents) for data collection
4. **Format output** to match the output schema
5. **Display results in the console** as formatted markdown/tables (default) - HTML output only when user explicitly requests it

Templates ensure consistency across reports and enable:
- Programmatic consumption of report data
- Validation of inputs and outputs
- Clear documentation of data sources
- Reproducible report generation

## Critical Prerequisites

### Authentication
Ensure you are authenticated to LimaCharlie with access to target organizations:
- User must have permissions across multiple organizations (MSSP/partner account)
- Billing data requires admin/owner role per organization
- Usage statistics accessible with standard read permissions

### Understanding Organization IDs (OIDs)
**⚠️ CRITICAL**: Organization ID (OID) is a **UUID** (like `c7e8f940-1234-5678-abcd-1234567890ab`), **NOT** the organization name.

- Use `limacharlie org list` to get OID from organization name
- All API calls require the UUID, not the friendly name
- OIDs are permanent identifiers (names can change)

### Time Range Requirements

**⚠️ MANDATORY: Prompt User for Time Range**

Before generating any report that requires detection or event data, you MUST ask the user to confirm or specify the time range using the `AskUserQuestion` tool:

```
AskUserQuestion(
  questions=[{
    "question": "What time range should I use for this report?",
    "header": "Time Range",
    "options": [
      {"label": "Last 24 hours", "description": "Most recent day of data"},
      {"label": "Last 7 days", "description": "Past week of activity"},
      {"label": "Last 30 days", "description": "Past month of activity"},
      {"label": "Custom range", "description": "I'll specify exact dates"}
    ],
    "multiSelect": false
  }]
)
```

If user selects "Custom range", follow up to get specific start/end dates.

**Core Requirements:**
- All reports MUST specify explicit time ranges
- Time windows MUST be displayed in every report section
- NEVER assume a default time range without user confirmation
- Maximum recommended range: 90 days (API limitations)

**⚠️ CRITICAL: Dynamic Timestamp Calculation**

**NEVER use hardcoded epoch values from examples or documentation!**

ALWAYS calculate timestamps dynamically using bash before making API calls:

```bash
# Get current Unix timestamp
NOW=$(date +%s)

# Calculate relative time ranges
HOURS_24_AGO=$((NOW - 86400))        # 24 hours = 86400 seconds
DAYS_7_AGO=$((NOW - 604800))          # 7 days = 604800 seconds
DAYS_30_AGO=$((NOW - 2592000))        # 30 days = 2592000 seconds
DAYS_90_AGO=$((NOW - 7776000))        # 90 days = 7776000 seconds

# For specific date ranges (user-provided)
START=$(date -d "2025-11-01 00:00:00 UTC" +%s)
END=$(date -d "2025-11-30 23:59:59 UTC" +%s)

# Display human-readable for confirmation
echo "Time range: $(date -d @$START) to $(date -d @$END)"
```

**Why This Matters:**
- The detection API (`get_historic_detections`) uses Unix epoch timestamps in SECONDS
- Using stale or example timestamps (like those in documentation) returns NO DATA
- The API only returns detections within the specified time window
- Incorrect timestamps = empty results = incorrect reports

**Validation Before API Call:**
```bash
# Verify timestamps are reasonable
if [ $START -gt $END ]; then
  echo "ERROR: Start time is after end time"
  exit 1
fi

if [ $END -gt $NOW ]; then
  echo "WARNING: End time is in the future, using current time"
  END=$NOW
fi
```

## Data Accuracy Guardrails

### Principle 1: NEVER Fabricate Data

**Absolute Rules:**
- ❌ NEVER estimate, infer, o