Skip to content

Asset Missing Information Developer Guide

Ed Mozley edited this page Nov 20, 2026 · 1 revision

Missing information - Developer Guide

GH #404 Β· 5.8.0 Β· user page: Missing information Β· design background: Asset type profiles decision 2 ("any later completeness check refers to the built-in field itself and flags rather than refuses")

In plain words

Two small tables and one service. A type's expected fields are rows; a per-asset Not applicable is a row. The list of what is missing is not stored - it is a query over the assets as they are now. That one choice gives the issue's hardest requirement for free: "filling a field removes its finding; clearing it makes it outstanding again ... from the UI, imports and API". No write route had to change, because none of them is asked to keep anything up to date.

The files

File What
🟣 includes/services/asset_completeness.php AssetCompleteness: the built-in field list, expectations, the findings query, Not applicable
🟣 api/assets/missing_info.php GET the list / one asset; POST Not applicable on or off
🟣 api/assets/save_asset_type.php, api/assets/get_asset_types.php carry expected (and expected_custom) with a type, like layout
🟣 asset-management/missing/index.php the Missing information page
🟣 asset-management/includes/header.php the header button, only once something is expected
🟣 asset-management/index.php the Key info line, loadMissingNotice()
🟣 asset-management/settings/index.php the type dialog's Expected information block, expSetup() / expCollect()
🟣 includes/services/asset_merge.php Not applicable follows a merge
πŸ—„οΈ database/freeitsm.sql, includes/db_verify_schema.php, api/system/db_verify.php, includes/db_verify_indexes.php (generated) the two tables, their foreign keys and unique keys
🟣 includes/analyst_lifecycle.php set_by_analyst_id is history
πŸ§ͺ tests/asset-missing-info.php 28 checks

The tables

CREATE TABLE IF NOT EXISTS `asset_type_expected_fields` (
    `id`                INT NOT NULL AUTO_INCREMENT,
    `asset_type_id`     INT NOT NULL,
    `field_key`         VARCHAR(40) NOT NULL,   -- 'purchase_date', 'supplier_id', ... or 'custom:<asset_fields.id>'
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_asset_type_expected` (`asset_type_id`, `field_key`),
    CONSTRAINT `fk_ate_type` FOREIGN KEY (`asset_type_id`) REFERENCES `asset_types` (`id`) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS `asset_field_not_applicable` (
    `id`                INT NOT NULL AUTO_INCREMENT,
    `asset_id`          INT NOT NULL,
    `field_key`         VARCHAR(40) NOT NULL,
    `set_by_analyst_id` INT NULL,              -- lifecycle: history
    `set_datetime`      DATETIME NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_asset_field_na` (`asset_id`, `field_key`),
    CONSTRAINT `fk_afna_asset` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE
);

No row means nothing expected - so an install that never ticks a box behaves exactly as before, and the header button never appears (acceptance criterion 1). field_key is a string rather than a column per field so a custom field and a built-in one are the same kind of thing.

The built-in fields

public const BUILTIN = [
    'asset_tag'       => ['identity',  'asset_tag',       'text'],
    'service_tag'     => ['identity',  'service_tag',     'text'],
    'location_id'     => ['identity',  'location_id',     'ref'],
    'manufacturer'    => ['make_model', 'manufacturer',   'text'],
    'model'           => ['make_model', 'model',          'text'],
    'purchase_date'   => ['financial', 'purchase_date',   'value'],
    'purchase_cost'   => ['financial', 'purchase_cost',   'value'],
    'supplier_id'     => ['financial', 'supplier_id',     'ref'],
    'cost_centre_id'  => ['financial', 'cost_centre_id',  'ref'],
    'order_number'    => ['financial', 'order_number',    'text'],
    'warranty_expiry' => ['financial', 'warranty_expiry', 'value'],
];

[section, column, kind]. The kind decides what "missing" means - criterion 3 of the issue:

kind Missing when So
text NULL or TRIM(col) = '' spaces-only is missing
ref NULL or 0 an unselected reference
value NULL only a cost of 0 is filled in

The hardware and operating-system fields are deliberately not offered: the agent writes them, and a person cannot usefully "fill in" a CPU. Adding a field later is one line here, one label string, nothing else.

The findings query

missingSql() turns one key into a SQL test on alias a:

private static function missingSql(string $key): array
{
    if (isset(self::BUILTIN[$key])) {
        [, $col, $kind] = self::BUILTIN[$key];
        if ($kind === 'text') return ["(a.`$col` IS NULL OR TRIM(a.`$col`) = '')", []];
        if ($kind === 'ref')  return ["(a.`$col` IS NULL OR a.`$col` = 0)", []];
        return ["a.`$col` IS NULL", []];
    }
    $fieldId = (int)substr($key, 7);
    // A custom value row counts when any of its typed columns holds something
    // (blank text does not); boolean false and number 0 are values.
    return ["NOT EXISTS (SELECT 1 FROM asset_field_values v WHERE v.asset_id = a.id AND v.field_id = ?
             AND ((v.value_text IS NOT NULL AND TRIM(v.value_text) <> '') OR v.value_number IS NOT NULL
                  OR v.value_date IS NOT NULL OR v.value_boolean IS NOT NULL OR v.value_ref_id IS NOT NULL))", [$fieldId]];
}

Column names only ever come from BUILTIN, never from input, and the custom field id is a bound parameter.

findings() runs one query per asset type that expects something, selecting one 0/1 flag per expected field:

foreach ($keys as $i => $k) {
    [$sql, $p] = self::missingSql($k);
    // Missing, and not marked Not applicable for this asset.
    $sel[] = "(($sql) AND NOT EXISTS (SELECT 1 FROM asset_field_not_applicable n WHERE n.asset_id = a.id AND n.field_key = ?)) AS m$i";
    $params = array_merge($params, $p, [$k]);
}
$where = 'a.asset_type_id = ?' . ($hasRetired ? ' AND a.retired_datetime IS NULL' : '') . $tSql;
...
$stmt = $conn->prepare('SELECT a.id, a.hostname, a.asset_tag, ' . implode(', ', $sel) . " FROM assets a WHERE $where ORDER BY a.hostname, a.id");

$tSql is activeTenantReadFilter($conn, $analystId, 'a') - the same company scope every asset list uses, including the All companies view (criterion 5). Retired assets are left out. A row is returned only when at least one flag is 1, with the missing keys in screen order (orderKeys(): the built-in order above, then custom fields by id).

Read it straight from SQL - every laptop with no purchase date and no exception:

SELECT a.id, a.hostname
  FROM assets a
 WHERE a.asset_type_id = 4
   AND a.retired_datetime IS NULL
   AND a.purchase_date IS NULL
   AND NOT EXISTS (SELECT 1 FROM asset_field_not_applicable n
                    WHERE n.asset_id = a.id AND n.field_key = 'purchase_date');

Saving expectations

public static function saveExpected(PDO $conn, int $typeId, $keys): void
{
    if (!self::ready($conn)) return;
    $allowedCustom = array_column(self::customCandidates($conn, $typeId), 'key');
    $clean = [];
    foreach (is_array($keys) ? $keys : [] as $k) {
        $k = (string)$k;
        if (isset(self::BUILTIN[$k]) || in_array($k, $allowedCustom, true)) $clean[$k] = true;
    }
    $conn->prepare('DELETE FROM asset_type_expected_fields WHERE asset_type_id = ?')->execute([$typeId]);
    ...

Unknown keys and custom fields not in the type's field sets are dropped, never stored. customCandidates() is the live fields of the sets attached through asset_type_field_sets.

save_asset_type.php calls it only when expected is in the request - like layout, so an older caller that saves just the name cannot wipe them:

if (array_key_exists('expected', $data)) {
    AssetCompleteness::saveExpected($conn, (int)$id, $data['expected']);
}

⚠️ TRAP - get_asset_types.php has two paths. A company's own types are rebuilt in a second array (scoped.own) for the settings dialog. The comment there already explains that icon and layout once got wiped because that array lacked them; expected and expected_custom are added to it too. Leave either out and Save on a company's own type empties its expectations.

Not applicable

public static function setNotApplicable(PDO $conn, int $assetId, string $key, bool $on, int $analystId, string $label): void
{
    if (self::sectionOf($key) === null) throw new InvalidArgumentException('Unknown field.');
    if ($on) {
        $stmt = $conn->prepare('INSERT IGNORE INTO asset_field_not_applicable (asset_id, field_key, set_by_analyst_id, set_datetime) VALUES (?, ?, ?, UTC_TIMESTAMP())');
        $stmt->execute([$assetId, $key, $analystId ?: null]);
    } else {
        $stmt = $conn->prepare('DELETE FROM asset_field_not_applicable WHERE asset_id = ? AND field_key = ?');
        $stmt->execute([$assetId, $key]);
    }
    // History only when something changed: marking a field twice is not two events.
    if ($stmt->rowCount() > 0) {
        $conn->prepare('INSERT INTO asset_history (...) VALUES (?, ?, ?, ?, ?, UTC_TIMESTAMP())')
             ->execute([$assetId, $analystId ?: null, 'Not applicable: ' . $label, $on ? null : 'Yes', $on ? 'Yes' : null]);
    }
}

It never touches the field (criterion 4). The endpoint checks requireModuleWriteJson('assets'), analystCanAccessAsset() and CSRF first.

Merges: AssetMergeService::REFERENCES gained ['asset_field_not_applicable', 'asset_id', 'unique', ['field_key']] - the mark moves to the kept asset unless it already has the same one. tests/asset-merge.php has a guard that fails when any column pointing at an asset is missing from that list; it caught this one.

The endpoint

api/assets/missing_info.php

Request Answers
GET (?type_id= ?section= ?field=) rows [{asset_id, name, asset_tag, type, missing[]}], labels (every expected key β†’ its name), sections, types, can_write
GET ?asset_id=N expected, missing, not_applicable [{key, by, at}], labels, can_write
POST {asset_id, field, not_applicable} the asset's new picture

Built-in labels come from asset-management.missing.field_<key>; custom ones from asset_fields.label. can_write is analystModuleLevel(..., 'assets') === 'full' - levels are read and full.

The screens

  • Settings β†’ Asset types β†’ a type. The block is drawn only when the tables exist (AssetCompleteness::ready()). expSetup(type, item) ticks from item.expected and builds the custom ticks from item.expected_custom; Save sends payload.expected = expCollect().
  • Header. The button shows when AssetCompleteness::anyExpected() - one LIMIT 1 lookup - or on the page itself.
  • Missing information page. One fetch; the type and field dropdowns fill on the first load; every chip has Not applicable for writers. Built with textContent throughout.
  • The asset. loadMissingNotice() runs when an asset opens and after every field save, so a filled field leaves the line at once. Hidden when the type expects nothing.

Tests

tests/asset-missing-info.php - 28 checks, ZZ fixtures removed in finally: nothing expected β†’ nothing listed; unknown keys and out-of-set custom fields dropped; every expected field named; an asset still saves; 0 cost and dates filled in, spaces-only text and no supplier missing; custom field with and without a value, and a blank one; filling clears and clearing restores; changing expectations changes the list; Not applicable hides, leaves the field NULL, shows on the asset, writes one history line (not two for a repeat), and Undo restores; filters by field, section and type; retired assets left out.

Also run against the change: asset-merge (25), asset-type-layout (16), asset-custom-fields (29), asset-api-fields (27), asset-retirement (13), assets-all-companies (10), analyst-lifecycle (21).

Traps

  • Findings are a query - never cache them in a column. The moment they are stored, every write route (page, import, REST, agent, merge) has to remember to refresh them, and the first one that forgets brings back the stale list the issue was written about.
  • A layout hiding a section is not Not applicable (the profiles design: "hidden by this profile, never not applicable"). The service never reads asset_type_layout.
  • Any new table with an asset_id goes into AssetMergeService::REFERENCES - the merge test enforces it.

FreeITSM

Getting Started

Modules

Multi-tenancy (planned)

Responses to feedback

Blue sky thinking

Bugs resolved

Pull requests

Links

Clone this wiki locally