work-tracing/README.md

Employee Workstation Activity Tracking System

A comprehensive system for tracking user activity on Windows workstations and reporting it to a central server. The system logs user logon events and monitors active/inactive periods, providing detailed reports through a web dashboard.

Overview

This system consists of two main components:

1. Client Agent: A PowerShell script that runs on Windows workstations to detect user idle time and report activity state changes.
2. Server Application: A Flask-based web application that receives activity reports, stores them in a database, and provides reporting capabilities.

The system tracks when users are "working" or "stopped" based on a 5-minute inactivity threshold, allowing organizations to measure effective working time.

Client-Side Setup

Prerequisites

• Windows 10/11 workstations
• PowerShell 3.0 or higher
• Administrative access (for setting up scheduled tasks)

Installation

1. Copy the report.ps1 script to a secure location on the workstation (e.g., C:\Scripts\)

2. Copy config.env to the same location as the PowerShell script, or set the following environment variables:

   • API_ENDPOINT: URL of the server's reporting endpoint
   • IDLE_THRESHOLD_MINUTES: Inactivity threshold in minutes (default: 5)
   • POLL_INTERVAL_SECONDS: How often to check for idle state (default: 60)

3. Run the schedule_task.ps1 script with administrative privileges to create the scheduled task:

   .\schedule_task.ps1 -ScriptPath "C:\Scripts\report.ps1"
   

The script will automatically run when users log on to the workstation.

Server-Side Setup

Prerequisites

• Python 3.9 or higher
• SQLite (development) or PostgreSQL (production)
• pip (Python package manager)

Installation

1. Clone this repository to your server

2. Create and activate a virtual environment:

   python -m venv venv
   source venv/bin/activate  # On Windows: venv\Scripts\activate
   

3. Install the required packages:

   pip install -r requirements.txt
   

4. Create a configuration file:

   cp config.env.example config.env
   

5. Edit config.env with your specific settings:

   • DATABASE_URI: Connection string for your database
   • API_ENDPOINT: URL for the reporting endpoint
   • Other configuration parameters

6. Set up the database:

   # For SQLite
   sqlite3 work_events.db < create_db.sql
   
   # For PostgreSQL
   psql -U username -d database_name -f create_db.sql
   

7. Run the application:

   # Development
   python run.py
   
   # Production (with Gunicorn)
   gunicorn -w 4 -b 0.0.0.0:5000 "app:create_app()"
   

System Architecture

Client Agent

The PowerShell script:

• Is launched at user logon via Task Scheduler
• Monitors user idle time using the quser command or Win32 API calls
• Reports state changes to the server via HTTP POST requests
• Uses the built-in Invoke-RestMethod cmdlet for API communication
• Implements error handling and local logging
• Supports configuration via environment variables or config.env file

Server Application

The Flask application:

• Uses a modular structure with separate components for models, views, and API endpoints
• Exposes a RESTful API endpoint at /api/report
• Stores activity events in a relational database
• Provides reporting functionality via SQL aggregation
• Includes a web dashboard for visualizing activity data
• Supports SQLite for development and PostgreSQL for production

Application Structure

user_work_tracking/
├── app/                  # Application package
│   ├── api/              # API endpoints
│   │   ├── events.py     # Event reporting endpoints
│   │   └── reports.py    # Data reporting endpoints
│   ├── utils/            # Utility functions
│   │   ├── formatting.py # Data formatting functions
│   │   └── queries.py    # SQL query functions
│   ├── views/            # Web views
│   │   └── dashboard.py  # Dashboard views
│   ├── models.py         # Database models
│   └── errors.py         # Error handlers
├── instance/             # Instance-specific data
├── static/               # Static files
│   ├── css/              # CSS stylesheets
│   └── js/               # JavaScript files
├── templates/            # HTML templates
├── create_db.sql         # Database schema creation script
├── config.env            # Configuration file
├── report.ps1            # Client-side PowerShell script
├── schedule_task.ps1     # Task scheduler setup script
├── requirements.txt      # Python dependencies
└── run.py                # Application entry point

API Reference

Report Activity Endpoint

URL: /api/report Method: POST Auth: None (LAN-restricted)

Request Body:

{
  "user": "username",
  "state": "working",
  "ts": "2023-07-08T12:30:45Z"
}

Fields:

• user: Windows username
• state: Either "working" or "stopped"
• ts: ISO 8601 timestamp (optional, defaults to server time)

Success Response:

{
  "success": true
}

Reporting

Access the dashboard by navigating to http://your-server-address:5000/ in a web browser.

The system provides:

• Daily, weekly, and monthly summaries of working time
• First login time tracking
• User activity breakdowns with detailed logs
• Interactive date navigation

Troubleshooting

Client Issues

• Check the log file at %USERPROFILE%\AppData\Local\Temp\user_work_tracking_client.log
• Verify the scheduled task is configured correctly in Task Scheduler
• Test the script manually: .\report.ps1
• Make sure the config.env file or environment variables are correctly set

Server Issues

• Check the application logs in the instance directory
• Verify database connectivity
• Ensure the server is accessible from client workstations
• When using PostgreSQL, ensure column names are properly quoted (especially "user" which is a reserved keyword)
• If users appear incorrectly in the dashboard, check the SQL queries to ensure proper schema specification (e.g., "public.work_events")

PostgreSQL-Specific Issues

• Reserved Keywords: Be careful with reserved keywords like "user". Always quote them with double quotes in SQL queries.
• Schema Specification: Explicitly reference the schema (e.g., "public.work_events" instead of just "work_events") to avoid potential naming conflicts.
• Connection String: Ensure the database URI in config.env uses the correct username, password, and database name.
• Database vs Login Role: The PostgreSQL username used to connect to the database is separate from the usernames stored in the "user" column of work_events table.

Security Considerations

• The system is designed for internal LAN use only and lacks authentication
• Ensure the server is not exposed to the public internet
• Consider implementing network-level access controls

License

This project is licensed under the MIT License - see the LICENSE file for details.