Dynamic, Headcount-Aware Low Stock Alerts for Consumables in ServiceNow

[BharatC]

## The Problem

Static low-stock thresholds don't scale. 5 chargers left is fine for a 15-person site, critical for a 500-person site.

This script sets the threshold dynamically at **30% of active users** at each stockroom's location, and batches all low-stock hits into **one notification per assignment group** instead of spamming one alert per item.

```javascript
(function() {

// ---- Config ----
var THRESHOLD_PERCENT = 0.30; // 30%

var consumableQuery = gs.getProperty('u_alm.consumable_stock_check.query');
if (!consumableQuery) {
gs.error('[Consumable Stock Check] Missing required system property: u_alm.consumable_stock_check.query');
return;
}

// Group the assignment groups
var groupedLowStock = {};

// Get the count of users by using location
function getUserCountForLocation(locationId) {
if (!locationId) return 0;

var ga = new GlideAggregate('sys_user');
ga.addQuery('location', locationId);
ga.addQuery('active', true);
ga.addAggregate('COUNT');
ga.query();

var count = 0;
if (ga.next()) {
count = parseInt(ga.getAggregate('COUNT'), 10) || 0;
}

return count;
}

// Adds one low-stock item to the appropriate assignment-group bucket
function addToGroup(assignmentGroupId, assignmentGroupName, itemInfo) {
if (!groupedLowStock.hasOwnProperty(assignmentGroupId)) {
groupedLowStock[assignmentGroupId] = {
groupName: assignmentGroupName,
items: []
};
}
groupedLowStock[assignmentGroupId].items.push(itemInfo);
}

// Sends ONE notification per assignment group, listing every
// stockroom/consumable that fell below threshold under that group.
function notifyAssignmentGroups() {
for (var groupId in groupedLowStock) {
if (!groupedLowStock.hasOwnProperty(groupId)) continue;

var group = groupedLowStock[groupId];
var lines = [];

for (var i = 0; i < group.items.length; i++) {
var it = group.items[i];
lines.push(
'Stockroom: ' + it.stockroomName +
' | Quantity: ' + it.currentQty +
' | Location: ' + it.locationName +
' | Model Category: ' + it.modelCategory
);
}

var summaryMessage = '[Consumable Stock Check] LOW STOCK SUMMARY for Assignment Group: ' +
group.groupName + ' (' + group.items.length + ' item(s) below threshold)\n' +
lines.join('\n');

gs.info(summaryMessage);

gs.eventQueue(
'alm_consumable.low_stock_group_summary',
null,
groupId,
summaryMessage
);
}
}

// ---- Main ----
var consumableGR = new GlideRecord('alm_consumable');
consumableGR.addEncodedQuery(consumableQuery);
consumableGR.addNotNullQuery('stockroom'); // skip consumables with no stockroom set
consumableGR.query();

while (consumableGR.next()) {

var stockroomId = consumableGR.getValue('stockroom');

var stockroomGR = new GlideRecord('alm_stockroom');
if (!stockroomGR.get(stockroomId)) {
gs.warn('[Consumable Stock Check] Stockroom not found for consumable: ' + consumableGR.getDisplayValue());
continue;
}

var locationId = stockroomGR.getValue('location');
if (!locationId) {
gs.warn('[Consumable Stock Check] Stockroom ' + stockroomGR.getDisplayValue() + ' has no location set.');
continue;
}

var userCount = getUserCountForLocation(locationId);
if (userCount === 0) {
continue; // nobody at this location, nothing to validate against
}

var threshold = userCount * THRESHOLD_PERCENT;
var currentQty = parseFloat(consumableGR.getValue('quantity')) || 0;

if (currentQty < threshold) {
var assignmentGroupId = stockroomGR.getValue('assignment_group');
var assignmentGroupName = stockroomGR.getDisplayValue('assignment_group');

if (!assignmentGroupId) {
gs.warn('[Consumable Stock Check] Stockroom ' + stockroomGR.getDisplayValue() +
' has no assignment group - cannot notify.');
continue;
}

addToGroup(assignmentGroupId, assignmentGroupName, {
stockroomName: stockroomGR.getDisplayValue(),
currentQty: currentQty,
locationName: stockroomGR.location.getDisplayValue(),
modelCategory: consumableGR.model_category.getDisplayValue()
});
}
}

// Fire one consolidated notification per assignment group
notifyAssignmentGroups();

})();
```

---

## Key Design Points

- **Relative threshold:** `active users at location × 30%`. Scales automatically as headcount changes — no manual re-tuning per stockroom. `THRESHOLD_PERCENT` is a single config variable at the top.
- **Query externalized to a System Property** (`u_alm.consumable_stock_check.query`): the encoded query defining scope lives in System Properties, not hard-coded, so it can be adjusted without touching the script.
- **GlideAggregate for headcount**, not GlideRecord — counting is pushed to the database instead of looping every `sys_user` record in script.
- **Batched notifications:** all low-stock hits are bucketed by `assignment_group` first, then one `gs.eventQueue()` fires per group — no per-item spam.
- **Defensive guards:** every lookup (stockroom, location, assignment group) is null-checked with a `gs.warn()` + `continue`, so one bad record doesn't break the whole run.

---

## Deploying It

1. Create System Property `u_alm.consumable_stock_check.query` (string, encoded query on `alm_consumable`).
2. Create a Scheduled Job to run this script on your cadence.
3. Register the event `alm_consumable.low_stock_group_summary` (if needed).
4. Build a Notification on that event to replace the `gs.info()` placeholder.

---

## Possible Enhancements

- Make `THRESHOLD_PERCENT` a system property too
- Support per-stockroom or per-category threshold overrides
- Route the summary through Flow Designer for richer formatting
- Trend recurring low-stock offenders via a report on the event log

---