sandbox-reports/README.md

AI Tools Usage Report System

Automated reporting system for AI tool usage analytics with Microsoft Azure AD Single Sign-On. Track usage across multiple AI tools including VEO3 video generation, text-to-image, text-to-voice, and more.

Features

• Multi-Tool Support: Track usage for 6+ AI tools:
  • VEO3 Video Generation
  • Text to Image (Dalle, SD3, Leo, etc.)
  • Text to Voice (ElevenLabs)
  • Speech to Speech
  • Document Translation
  • Video Query Analysis
• Dynamic Tool Detection: Automatically adapts to new tool types
• PHP Web Interface: Interactive dashboard with tool filtering
• SSO Authentication: Microsoft Azure AD (MSAL) integration for secure access
• Python Automation: Automated daily/weekly/monthly email reports via SMTP
• Granular Analytics:
  • Last 24 hours activity
  • Last 7 days trends
  • Last 30 days overview
  • Tool usage breakdown
  • Top 25 users per period
• Professional Styling: Montserrat font, custom yellow (#FFC407) branding

Installation

Prerequisites

• PHP: 7.4 or higher
• Python: 3.7 or higher
• Composer: For PHP dependency management
• Web Server: Apache or Nginx (for production) or PHP built-in server (for development)

Step 1: Clone the Repository

git clone git@bitbucket.org:zlalani/sandbox-reports.git
cd sandbox-reports

Step 2: Install PHP Dependencies

composer install

This installs:

• Microsoft Authentication Library (MSAL)
• Firebase JWT for token validation
• GuzzleHTTP for API requests

Step 3: Install Python Dependencies

pip install -r requirements.txt

This installs:

• requests - For webhook API calls
• python-dotenv - For environment variable management
• schedule - For automated task scheduling
• pytz - For timezone handling

Step 4: Configure Environment Variables

cp .env.example .env
nano .env  # or use your preferred editor

Required configuration:

# Webhook Configuration
WEBHOOK_URL=https://hook.us1.make.celonis.com/your-webhook-id

# SMTP Settings (for email reports)
SMTP_SERVER=smtp.mailgun.org
SMTP_PORT=587
SMTP_USER=your-smtp-username
SMTP_PASSWORD=your-smtp-password
SENDER_EMAIL=reports@yourdomain.com

# Email Recipients (comma-separated)
REPORT_RECIPIENTS=user1@example.com,user2@example.com

# SSO Configuration (see SSO-SETUP.md for details)
SSO_ENABLED=true
AZURE_TENANT_ID=your-tenant-id
AZURE_CLIENT_ID=your-client-id
REDIRECT_URI=http://localhost:8000/auth.php

# Session Configuration
SESSION_SECRET=your-random-secret-key-here
SESSION_LIFETIME=86400

Step 5: Set Up SSO (Optional)

For production deployments with authentication:

1. Follow the complete guide in SSO-SETUP.md
2. Register your application in Azure AD
3. Configure redirect URIs
4. Set SSO_ENABLED=true in .env

For development without SSO:

# In .env
SSO_ENABLED=false

Step 6: Set Up Directory Permissions

# Create logs directory
mkdir -p logs
chmod 755 logs

# Ensure PHP can write webhook response
chmod 755 .

Step 7: Start the Application

For Development (Local)

# Start PHP built-in server
php -S localhost:8000

Access the application:

• Fetch Data: http://localhost:8000/webhook_caller.php
• View Report: http://localhost:8000/report.php

For Production (Apache/Nginx)

See DEPLOYMENT.md for complete production deployment instructions.

Usage

Web Interface

1. Fetch Data from Webhook

   • Navigate to webhook_caller.php
   • Select date range (defaults to last 12 weeks)
   • Click "Fetch Data"
   • Data is saved to webhook_response.json

2. View Reports

   • Navigate to report.php
   • View comprehensive analytics dashboard
   • Filter by specific AI tools using the filter dropdown
   • Use "Refresh Data" button to update from webhook

Python Email Reports

Generate and send email reports manually:

python veo3_report.py

The script will:

1. Fetch data from the webhook
2. Analyze usage for 24h, 7d, and 30d periods
3. Generate HTML report (email_report.html)
4. Send email to configured recipients

Automated Reporting

Production Server (Systemd Service - Recommended)

# Deploy to server
scp veo3_report.py veo3_scheduler.py requirements.txt .env \
    veo3-report.service deploy.sh user@your-server:/tmp/veo3-deploy/

# Run deployment script
ssh user@your-server
cd /tmp/veo3-deploy
chmod +x deploy.sh
sudo ./deploy.sh

The service runs daily at 7:00 PM EST and includes:

• ✅ Auto-restart on failure
• ✅ Starts on server boot
• ✅ Logging with journalctl
• ✅ Status monitoring

See DEPLOYMENT.md for full details.

Alternative: Cron Job

# Edit crontab
crontab -e

# Add line for daily report at 9 AM
0 9 * * * cd /path/to/sandbox-reports && /usr/bin/python3 veo3_report.py

Configuration

Tool Costs

You can adjust estimated costs per tool in both files:

Python (veo3_report.py):

TOOL_COSTS = {
    'VEO3': 3.20,
    'TEXT2IMAGE': 0.04,
    'TEXT2VOICE': 0.30,
    'SPEECH2SPEECH': 0.50,
    'DOCUMENT_TRANSLATION': 0.10,
    'VIDEOQUERY': 0.25,
}

PHP (report.php):

$toolCosts = [
    'VEO3' => 3.20,
    'TEXT2IMAGE' => 0.04,
    'TEXT2VOICE' => 0.30,
    'SPEECH2SPEECH' => 0.50,
    'DOCUMENT_TRANSLATION' => 0.10,
    'VIDEOQUERY' => 0.25,
];

Tool Display Names

Customize how tool names appear in reports:

Python (veo3_report.py):

TOOL_NAMES = {
    'VEO3': 'VEO3 Video Generation',
    'TEXT2IMAGE': 'Text to Image',
    # ... add more
}

PHP (report.php):

$toolNames = [
    'VEO3' => 'VEO3 Video Generation',
    'TEXT2IMAGE' => 'Text to Image',
    // ... add more
];

Project Structure

sandbox-reports/
├── README.md                    # This file
├── .env.example                # Environment variable template
├── .env                        # Your configuration (not in git)
├── .gitignore                  # Git ignore rules
├── .htaccess                   # Apache security & configuration
│
├── Web Interface
│   ├── report.php              # Main dashboard with filtering
│   ├── webhook_caller.php      # Data fetching interface
│   ├── config.php              # PHP configuration
│   ├── env_loader.php          # Environment loader
│   └── webhook_response.json   # Cached data (auto-generated)
│
├── SSO Authentication
│   ├── AuthMiddleware.php      # Authentication middleware
│   ├── JWTValidator.php        # JWT token validation
│   ├── auth.php                # Auth API endpoint
│   ├── composer.json           # PHP dependencies
│   └── SSO-SETUP.md           # SSO setup guide
│
├── Python Reports
│   ├── veo3_report.py          # Email report generator
│   ├── veo3_scheduler.py       # Scheduling daemon
│   ├── requirements.txt        # Python dependencies
│   └── email_report.html       # Generated report (auto-created)
│
└── Deployment
    ├── deploy.sh               # Deployment script
    ├── veo3-report.service     # Systemd service file
    ├── DEPLOYMENT.md           # Deployment guide
    └── QUICKSTART.md          # Quick start guide

Features in Detail

Tool Filtering

The web interface allows filtering by specific AI tools:

• Select "All Tools" to see complete usage
• Select individual tools to focus on specific usage
• Filter applies to all user tables and statistics

Dynamic Tool Detection

The system automatically handles new tool types:

• Unknown tools are displayed with their raw name
• Cost defaults to $0 for undefined tools
• No code changes needed when new tools are added

Multi-Period Analytics

Each report shows three time periods:

• Last 24 Hours: Recent activity snapshot
• Last 7 Days: Weekly trends
• Last 30 Days: Monthly overview

Each period includes:

• Total requests and costs
• Unique user count
• Top 25 most active users
• Activity distribution

Performance Optimization

• Refresh button fetches only last 12 weeks (not all data)
• Efficient data caching with webhook_response.json
• Responsive design for mobile viewing
• Collapsible sections to reduce page load

Troubleshooting

PHP Issues

Problem: "Class 'Dotenv' not found"

# Solution: Install composer dependencies
composer install

Problem: "Permission denied" writing webhook_response.json

# Solution: Fix directory permissions
chmod 755 .
chmod 666 webhook_response.json  # if file exists

Python Issues

Problem: "ModuleNotFoundError: No module named 'dotenv'"

# Solution: Install Python dependencies
pip install -r requirements.txt

Problem: Email not sending

# Solution: Check SMTP credentials in .env
# Test with:
python veo3_report.py
# Check console output for specific error

SSO Issues

Problem: Redirect URI mismatch

• Ensure Azure AD app registration matches exactly
• Check REDIRECT_URI in .env
• See SSO-SETUP.md for complete guide

Data Issues

Problem: No data showing in reports

# Solution: Fetch data from webhook first
# 1. Visit webhook_caller.php
# 2. Click "Fetch Data"
# 3. Verify webhook_response.json was created

Development

Adding New Tool Types

The system is designed to be extensible. To add a new tool type:

1. Add to tool costs configuration (optional):

# Python
TOOL_COSTS['NEW_TOOL'] = 0.50

# PHP
$toolCosts['NEW_TOOL'] = 0.50;

2. Add display name (optional):

# Python
TOOL_NAMES['NEW_TOOL'] = 'New Tool Name'

# PHP
$toolNames['NEW_TOOL'] = 'New Tool Name';

If you don't add the tool, it will still be tracked and displayed with its raw name and $0 cost.

Security

Application Security

• SSO Authentication: Microsoft Azure AD integration
• Session Management: Secure cookies with httponly and samesite flags
• JWT Token Validation: Cryptographic verification of tokens
• Environment Variables: Sensitive data stored in .env (not in git)
• HTTPS: Strongly recommended for production (force HTTPS in .htaccess)

Apache Security (.htaccess)

The included .htaccess file provides basic security:

• Directory Browsing Disabled: Prevents listing of files
• Default Document: Sets report.php as the default page
• Environment File Protection: Blocks access to .env files
• Git Directory Protection: Blocks access to .git directory

License

Internal use only.

Support

For issues or questions, contact your system administrator or refer to:

• SSO-SETUP.md - SSO configuration
• DEPLOYMENT.md - Production deployment
• QUICKSTART.md - Quick start guide