SAIL Chargeback Report

Serverless application for tracking and allocating Atlassian and ServiceNow license costs across TP ICAP business units

What It Does

Purpose

Automatically identifies all Atlassian (Jira, Confluence) and ServiceNow licensed users across TP ICAP, matches them to HR data (division, department, cost centre), and presents an interactive dashboard for license cost allocation, usage reporting, and license removal management.

Key Capabilities

Real-time billable user tracking across Jira, Confluence & ServiceNow
HR matching by email + UPN for cost centre allocation
External user detection (GMEX, Liquidnet, subsidiaries)
Interactive D3 bubble chart with drill-down
License usage reports with inactive user detection
Exclusion lists for protected accounts
AD removal script generation from dashboard
Removal audit history
CSV export at every level
Automated hourly refresh (8am-6pm)

Architecture

┌─────────────────────────────────────────────────────────────────────┐
│  DATA COLLECTION (Your Machine — Scheduled Task, hourly 8am-6pm)   │
└──────────────────────────┬──────────────────────────────────────────┘
                           │
          ┌────────────────┼────────────────┐
          ▼                ▼                ▼
  ┌──────────────┐  ┌──────────────┐  ┌──────────────────┐
  │  Atlassian   │  │  Quarterback │  │  Atlassian Site  │
  │  Org Admin   │  │  HR API      │  │  Group Members   │
  │  API         │  │              │  │  API             │
  └──────┬───────┘  └──────┬───────┘  └────────┬─────────┘
         │                 │                     │
         │  9,000 org      │  6,776 HR           │  External users
         │  users          │  records            │  (GMEX, etc.)
         ▼                 ▼                     ▼
  ┌─────────────────────────────────────────────────────────────────┐
  │  Refresh_Data.ps1 — Match, Merge, Build JSON                   │
  └──────────────────────────┬──────────────────────────────────────┘
                           │
                    POST /upload
                    (API Key auth)
                           │
┌──────────────────────────┼──────────────────────────────────────────┐
│  AWS (eu-west-1)         ▼                                         │
│         ┌────────────────────────────┐                             │
│         │  API Gateway (HTTP API)    │                             │
│         │  h7larzbbb6                │                             │
│         └────────────┬───────────────┘                             │
│                      ▼                                             │
│         ┌────────────────────────────┐    ┌───────────────────┐    │
│         │  Lambda                    │───▶│  S3 Bucket        │    │
│         │  chargeback-dashboard      │◀───│  tpicap-chargeback │    │
│         │  Node.js 20               │    │  -data             │    │
│         └────────────────────────────┘    └───────────────────┘    │
│                                                                    │
└────────────────────────────────────────────────────────────────────┘
                           │
                    GET / (HTML)
                    GET /chargeback (JSON)
                           │
                           ▼
                  ┌──────────────────┐
                  │  Browser          │
                  │  D3 Bubble Chart  │
                  │  + Drill-down     │
                  └──────────────────┘

Dashboard Features

Visualisation

D3 packed bubble chart — Divisions → Departments → Users
Product filter toggles: All, Billable, Jira, Confluence, Both, No License, Unmatched
Division & department summary tables with distribution bars
Breadcrumb navigation
Tooltips with user counts and percentages

Data & Export

Global user search across all divisions
Sortable user tables with per-column search
Product-aware CSV exports — ServiceNow exports use ServiceNow-specific headers and filenames; Atlassian exports use Jira/Confluence headers
CSV export: all data, current view, division, department, search results, summary, unmatched, individual reports, all inactive
Unmatched users with reason classification (service account, external user, no HR record)
Refresh button to reload latest data

AWS Resources

Resource	Name	Purpose
Lambda	chargeback-dashboard	Serves dashboard HTML, JSON data, and accepts uploads. Node.js 20, 512MB, 120s timeout
API Gateway	chargeback-api (h7larzbbb6)	HTTP API. Routes: GET / (HTML), GET /chargeback (JSON), GET /servicenow (JSON), POST /upload, /upload-sn, /removal-log. Custom domain configured
Custom Domain	sail-chargeback.dev.sail.tpicapcloud.com	ACM cert + Route 53 alias in dev.sail.tpicapcloud.com hosted zone
S3	tpicap-chargeback-data	Stores dashboard.html, about.html, chargeback-data.json, servicenow-data.json, exclusion-list.json, removal-logs/. Versioning enabled (50 versions)
IAM Role	chargeback-devops-lambda-role	S3 read/write + Lambda execution + CloudWatch logs

Schedule & Cost

Automated Refresh

Windows Task Scheduler on local machine
Runs daily at 8am, repeating hourly until 6pm
Refreshes Atlassian + ServiceNow data each run
HR data smart-cached (refreshes every 4 hours)
Uploads via API Gateway — no AWS SSO needed
S3 versioning keeps last 50 data snapshots

License Tracking

Jira	1,800 licenses
Confluence	2,250 licenses
ServiceNow	1,100 licenses

KPIs show used/total with % utilisation. License removal scripts generated from dashboard with AD + Atlassian API support, audit logging, and escalation workflow.

Monthly AWS Cost

Lambda	~$0.00 (free tier)
API Gateway	~$0.00 (free tier)
S3 (with versioning)	~$0.02
Secrets Manager	~$0.80
Total	< $1/month