Skip to content

Org Usage History Report (Past Periods)

This runbook explains how to pull an organization's past usage from MongoDB when the dashboard is not enough - for example when Sales or CSM asks "how much did this customer actually use over the last two years?".

The data comes from the License Server's monthly usage rollups.

Production data

This is a read-only report, but it runs against the production licenseStore database. Use a read-only user, never write to usageAggregate, and do not share raw customer usage outside the requesting team.

Purpose and Scope

  • Find an organization's historical usage per month, broken down by license attribute.
  • Cover a fixed window (default: the last 24 full months, current month excluded).
  • Export the result as JSON or CSV.

Out of scope: live/current-period usage (read it from the Appcircle dashboard) and per-profile or per-build detail (this collection stores organization-level totals only).

Where the Data Lives

Item Value
Database licenseStore
Collection usageAggregate

Document shape (Appcircle.LicenseServer.WebApi.DataAccess.StoreObjects.UsageAggregate):

{
  _id: "<organizationId>|<key>|<periodType>|<yyyy-MM-dd>",
  organizationId: BinData(3, "..."),   // .NET Guid, legacy UUID representation
  key: 1,                              // LicenseAttributeType
  aggregateStartDate: ISODate("2026-07-01T00:00:00Z"),
  periodType: 4,                       // LicensePeriodType.Monthly
  totalValue: "1234"                   // stored as string
}

Two details that trip people up:

  • organizationId is not a UUID field. The .NET driver writes it as BinData subtype 3 (legacy C# GUID byte order), so a plain UUID("...") filter returns nothing.
  • totalValue is a string, so it must be cast before any arithmetic.

Period Types (LicensePeriodType)

0 None, 1 Minutely, 2 Hourly, 3 Daily, 4 Monthly, 5 Yearly.

The report uses 4 (Monthly).

Attribute Keys (LicenseAttributeType)

0 BuildDuration, 1 BuildCount, 2 TotalStorageSize, 3 DevicePreviewDuration, 4 TesterEmailCount, 5 TeamMemberCount, 6 ReportingAndLogs, 7 BuildConcurrency, 8 BuildTimeLimitPerBuild, 9 StarterOrganizationCount, 10 EnterpriseStoreDownloadCount, 11 SubOrganizationCreateCount, 12 PublishCount, 13 TestingDownloadCount, 14 CodepushDownloadCount, 15 TotalCodepushReleaseCount, 16 MachinePlan.

BuildDuration and DevicePreviewDuration are durations, TotalStorageSize is a size; the rest are plain counts.

Prerequisites

  • mongosh (the script also runs in the legacy mongo shell).
  • A read-only connection string for the environment you are reporting on.
  • The target organization id as a standard GUID string.

1) Find the Organization Id

The report is keyed by organization id, so start there. Two ways to get it, depending on what you already know.

Option A - Admin panel

If you can reach the organization in the admin panel, open its license page. The panel calls:

GET /identity/v1/organizations/{organizationId}/license

The {organizationId} segment of that request is the id you need. Read it from the browser address bar, or from the request URL in the browser DevTools Network tab:

/identity/v1/organizations/c0de8b37-56e3-402f-9d8e-3a91b64b33b5/license

The response also confirms you are on the right organization (plan and license attributes), which is worth a glance before running the report.

Option B - Keycloak

If you only have the user, go through Keycloak. Organizations are represented as groups, so find the user, open the group they belong to, and take the organization id from that group.

  1. Open the Keycloak admin console and select the Appcircle realm.
  2. Users -> search by email -> open the user -> Groups tab.
  3. Find the group that corresponds to the organization and read its id.

A user can belong to more than one organization group, so match on the organization the request is actually about rather than taking the first group in the list.

Cross-check

Whichever path you used, the id must be a standard 36-character GUID (8-4-4-4-12, no braces). Anything else will fail the guidToCSharpLegacyBinData check in the next step.

2) Run the Report

Save the script below as org-usage-report.mongo.js, replace the organization id on the guidToCSharpLegacyBinData(...) line, then run:

mongosh "<connection-string>" org-usage-report.mongo.js

For CSV, set OUTPUT_FORMAT = "csv" and redirect the output:

mongosh "<connection-string>" --quiet org-usage-report.mongo.js > report.csv

// Org usage history report - last 24 months, current month excluded
// Source: usageAggregate (PeriodType=Monthly)

// Standard GUID string -> Mongo BinData subtype 3 (.NET "CSharpLegacy" GUID layout).
// .NET's Guid.ToByteArray() byte-swaps the first int32 and the two int16 fields
// (little-endian) but leaves the last 8 bytes as-is; this mirrors that exactly.
// Written without Buffer/Binary so it runs in both legacy `mongo` shell and `mongosh`.
function hexToBytes(hex) {
  const bytes = [];
  for (let i = 0; i < hex.length; i += 2) bytes.push(parseInt(hex.substr(i, 2), 16));
  return bytes;
}

function bytesToBase64(bytes) {
  const chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/";
  let result = "";
  for (let i = 0; i < bytes.length; i += 3) {
    const b1 = bytes[i];
    const b2 = i + 1 < bytes.length ? bytes[i + 1] : 0;
    const b3 = i + 2 < bytes.length ? bytes[i + 2] : 0;
    const triplet = (b1 << 16) | (b2 << 8) | b3;
    result += chars[(triplet >> 18) & 63];
    result += chars[(triplet >> 12) & 63];
    result += i + 1 < bytes.length ? chars[(triplet >> 6) & 63] : "=";
    result += i + 2 < bytes.length ? chars[triplet & 63] : "=";
  }
  return result;
}

function guidToCSharpLegacyBinData(guidStr) {
  const hex = guidStr.replace(/-/g, "");
  if (hex.length !== 32) throw new Error(`Not a valid GUID: ${guidStr}`);
  const swapped =
    hex.substr(6, 2) + hex.substr(4, 2) + hex.substr(2, 2) + hex.substr(0, 2) + // a, reversed
    hex.substr(10, 2) + hex.substr(8, 2) + // b, reversed
    hex.substr(14, 2) + hex.substr(12, 2) + // c, reversed
    hex.substr(16); // d+e, as-is
  return BinData(3, bytesToBase64(hexToBytes(swapped)));
}

const orgId = guidToCSharpLegacyBinData("c0de8b37-56e3-402f-9d8e-3a91b64b33b5");

const ATTRIBUTE_NAMES = {
  0: "BuildDuration",
  1: "BuildCount",
  2: "TotalStorageSize",
  3: "DevicePreviewDuration",
  4: "TesterEmailCount",
  5: "TeamMemberCount",
  6: "ReportingAndLogs",
  7: "BuildConcurrency",
  8: "BuildTimeLimitPerBuild",
  9: "StarterOrganizationCount",
  10: "EnterpriseStoreDownloadCount",
  11: "SubOrganizationCreateCount",
  12: "PublishCount",
  13: "TestingDownloadCount",
  14: "CodepushDownloadCount",
  15: "TotalCodepushReleaseCount",
  16: "MachinePlan",
};

const PERIOD_TYPE_MONTHLY = 4;
const OUTPUT_FORMAT = "json"; // "csv" or "json"

const now = new Date();
const currentMonthStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth(), 1));
const rangeStart = new Date(Date.UTC(now.getUTCFullYear(), now.getUTCMonth() - 24, 1));

const rows = db.usageAggregate.aggregate([
  {
    $match: {
      organizationId: orgId,
      periodType: PERIOD_TYPE_MONTHLY,
      aggregateStartDate: { $gte: rangeStart, $lt: currentMonthStart },
    },
  },
  {
    $project: {
      _id: 0,
      key: 1,
      month: { $dateToString: { format: "%Y-%m", date: "$aggregateStartDate" } },
      totalValue: 1,
    },
  },
  { $sort: { month: 1, key: 1 } },
]).toArray();

if (rows.length === 0) {
  print("No monthly aggregates found for this org/date range.");
} else {
  // Pivot: month x attribute
  const pivot = {};
  const namesSeen = {};
  rows.forEach((r) => {
    const name = ATTRIBUTE_NAMES[r.key] ?? `Key${r.key}`;
    namesSeen[name] = true;
    pivot[r.month] = pivot[r.month] || {};
    pivot[r.month][name] = Number(r.totalValue);
  });
  const months = Object.keys(pivot).sort();
  const names = Object.keys(namesSeen).sort();

  if (OUTPUT_FORMAT === "json") {
    const result = months.map((m) => {
      const obj = { month: m };
      names.forEach((n) => (obj[n] = pivot[m][n] ?? 0));
      return obj;
    });
    print(JSON.stringify(result, null, 2));
  } else {
    print(["month"].concat(names).join(","));
    months.forEach((m) => {
      print([m].concat(names.map((n) => pivot[m][n] ?? 0)).join(","));
    });
  }
}

3) Read the Output

One row per month, one column per attribute that the org has any data for. A month with no value for an attribute is reported as 0.

[
  { "month": "2025-09", "BuildCount": 412, "BuildDuration": 38214, "TeamMemberCount": 12 },
  { "month": "2025-10", "BuildCount": 388, "BuildDuration": 35110, "TeamMemberCount": 12 }
]

Adjusting the Window

  • Change 24 in rangeStart for a different lookback.
  • To include the current (partial) month, drop the $lt: currentMonthStart clause. Note that the current month is still being aggregated and its value will change.
  • For yearly rollups set PERIOD_TYPE_MONTHLY to 5 and widen the range; for daily set it to 3 and expect a much larger result set.

Troubleshooting

  • No monthly aggregates found - most often a wrong organization id, or an org that produced no usage in the window. Sanity-check with db.usageAggregate.findOne({ organizationId: orgId }) (no other filters).
  • Not a valid GUID - the id was passed with braces or in a non-standard format. Pass the plain 36-character 8-4-4-4-12 form.
  • Numbers look wrong - totalValue is a string; confirm the cast (Number(...)) is in place and that you are reading the attribute you think you are.