Automate asset inventory updates from cloud, MDM, and CI sources
For developers who need asset inventory to stay audit-ready without manual spreadsheets. This shows a working pattern that pulls assets from common sources on a schedule, normalizes them into PostgreSQL, and fails loudly when data goes stale.
TL;DR — Build asset inventory as a scheduled data pipeline, not a spreadsheet: collect from authoritative APIs, upsert into one table with source timestamps, and alert when any source stops updating. The most common fix when inventory is "inaccurate" is to stop treating one source as complete and instead merge cloud, endpoint/MDM, and CI/CD data with a deterministic unique key. Reading time: ~5 min
Goal
When you finish, you will have a scheduled job that pulls asset data from your authoritative systems, writes it into PostgreSQL with repeatable upserts, and exposes a simple stale-data check so you can prove the inventory is current and detect when collection has broken.
Prerequisites
- PostgreSQL 15+ reachable from your runner — check with
psql --version - Python 3.11+ and
pip— check withpython3 --version jq1.6+ — check withjq --versioncurl8+ — check withcurl --version- Read-only API access to your asset sources, at minimum one of:
- cloud inventory API credentials (for example AWS
ec2:DescribeInstances, Azure Resource Graph reader, or GCP Asset Viewer) - MDM/endpoint management API token
- CI/CD or source-control API token for runners/build agents
- cloud inventory API credentials (for example AWS
- A Linux host, container, or CI runner that can execute a scheduled job (
cronor your CI scheduler) - These environment values at hand:
DATABASE_URL, source API base URLs, and source API tokens
Steps
Step 1: Create the inventory table and freshness view
Run this once against your PostgreSQL database.
psql "$DATABASE_URL" <<'SQL'
CREATE TABLE IF NOT EXISTS asset_inventory (
asset_key text PRIMARY KEY,
asset_type text NOT NULL,
hostname text,
owner text,
environment text,
source text NOT NULL,
source_id text NOT NULL,
first_seen_at timestamptz NOT NULL DEFAULT now(),
last_seen_at timestamptz NOT NULL,
raw jsonb NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_asset_inventory_source ON asset_inventory(source);
CREATE INDEX IF NOT EXISTS idx_asset_inventory_last_seen_at ON asset_inventory(last_seen_at);
CREATE OR REPLACE VIEW asset_inventory_freshness AS
SELECT
source,
max(last_seen_at) AS newest_seen_at,
now() - max(last_seen_at) AS age
FROM asset_inventory
GROUP BY source;
SQL
You should see CREATE TABLE, CREATE INDEX, and CREATE VIEW with no ERROR: lines.
Step 2: Create the collector script with deterministic upserts
Create collect_assets.py exactly as below. It accepts newline-delimited JSON on stdin and upserts by asset_key.
#!/usr/bin/env python3
import json, os, sys
import psycopg
sql = """
INSERT INTO asset_inventory (
asset_key, asset_type, hostname, owner, environment,
source, source_id, last_seen_at, raw
) VALUES (
%(asset_key)s, %(asset_type)s, %(hostname)s, %(owner)s, %(environment)s,
%(source)s, %(source_id)s, now(), %(raw)s
)
ON CONFLICT (asset_key) DO UPDATE SET
asset_type = EXCLUDED.asset_type,
hostname = EXCLUDED.hostname,
owner = EXCLUDED.owner,
environment = EXCLUDED.environment,
source = EXCLUDED.source,
source_id = EXCLUDED.source_id,
last_seen_at = now(),
raw = EXCLUDED.raw;
"""
count = 0
with psycopg.connect(os.environ["DATABASE_URL"]) as conn:
with conn.cursor() as cur:
for line in sys.stdin:
if not line.strip():
continue
obj = json.loads(line)
obj["raw"] = json.dumps(obj["raw"])
cur.execute(sql, obj)
count += 1
conn.commit()
print(f"upserted={count}")
Install the dependency:
python3 -m pip install --upgrade psycopg[binary]
chmod +x collect_assets.py
You should see a successful Successfully installed psycopg-... line and no Python traceback.
Step 3: Collect from one authoritative source first
Start with a source you already trust. The example below uses AWS EC2 because the output shape is stable and easy to normalize. If you do not use AWS, keep the same normalization contract and swap the curl/CLI command for your provider API.
aws ec2 describe-instances --output json | jq -rc '
.Reservations[].Instances[] |
{
asset_key: ("aws-ec2:" + .InstanceId),
asset_type: "vm",
hostname: (.PrivateDnsName // .PublicDnsName // .InstanceId),
owner: ((.Tags // []) | map(select(.Key=="Owner") | .Value) | first // "unknown"),
environment: ((.Tags // []) | map(select(.Key=="Environment") | .Value) | first // "unknown"),
source: "aws-ec2",
source_id: .InstanceId,
raw: .
}
' | ./collect_assets.py
You should see output shaped like:
upserted=42
Step 4: Add a second source so gaps are visible
A single source is never a complete inventory. Add endpoint/MDM or CI runners the same way. The example below uses a generic REST API returning items[]; adapt only the URL and field names.
curl -fsS \
-H "Authorization: Bearer $MDM_TOKEN" \
"$MDM_API_BASE/devices" | jq -rc '
.items[] |
{
asset_key: ("mdm-device:" + (.serial_number // .id)),
asset_type: "endpoint",
hostname: (.hostname // .device_name // .serial_number),
owner: (.user_email // "unknown"),
environment: "user-device",
source: "mdm",
source_id: (.id|tostring),
raw: .
}
' | ./collect_assets.py
You should see another upserted=<n> line. If curl returns non-2xx, it exits non-zero because of -f.
Step 5: Schedule the collection and fail on stale data
Create a wrapper script so the scheduler has one entry point.
cat > /usr/local/bin/run-asset-inventory.sh <<'SH'
#!/usr/bin/env bash
set -euo pipefail
export DATABASE_URL="${DATABASE_URL:?missing}"
export MDM_API_BASE="${MDM_API_BASE:?missing}"
export MDM_TOKEN="${MDM_TOKEN:?missing}"
aws ec2 describe-instances --output json | jq -rc '.Reservations[].Instances[] | {asset_key:("aws-ec2:" + .InstanceId),asset_type:"vm",hostname:(.PrivateDnsName // .PublicDnsName // .InstanceId),owner:((.Tags // []) | map(select(.Key=="Owner") | .Value) | first // "unknown"),environment:((.Tags // []) | map(select(.Key=="Environment") | .Value) | first // "unknown"),source:"aws-ec2",source_id:.InstanceId,raw:.}' | /path/to/collect_assets.py
curl -fsS -H "Authorization: Bearer $MDM_TOKEN" "$MDM_API_BASE/devices" | jq -rc '.items[] | {asset_key:("mdm-device:" + (.serial_number // .id)),asset_type:"endpoint",hostname:(.hostname // .device_name // .serial_number),owner:(.user_email // "unknown"),environment:"user-device",source:"mdm",source_id:(.id|tostring),raw:.}' | /path/to/collect_assets.py
psql "$DATABASE_URL" -Atc "SELECT source || E'\t' || age FROM asset_inventory_freshness WHERE now() - newest_seen_at > interval '26 hours';" | tee /tmp/asset_inventory_stale.txt
if [[ -s /tmp/asset_inventory_stale.txt ]]; then
echo "stale inventory sources detected" >&2
exit 2
fi
SH
chmod +x /usr/local/bin/run-asset-inventory.sh
Add the schedule with cron:
(crontab -l 2>/dev/null; echo '15 * * * * /usr/bin/env DATABASE_URL="postgresql://user:pass@db.example.com:5432/inventory" MDM_API_BASE="https://mdm.example.com/api/v1" MDM_TOKEN="REDACTED" /usr/local/bin/run-asset-inventory.sh >> /var/log/asset-inventory.log 2>&1') | crontab -
crontab -l
You should see the new cron line printed by crontab -l.
⚠️ If you put secrets directly in
crontab, they are visible to any account that can read that user's crontab or process environment. Prefer a root-readable env file such as/etc/asset-inventory.envandsourceit in the wrapper script.
Step 6: Add a control check your auditors can actually use
Run this query to list stale sources and asset counts by source.
psql "$DATABASE_URL" <<'SQL'
SELECT source, count(*) AS assets, max(last_seen_at) AS newest_seen_at
FROM asset_inventory
GROUP BY source
ORDER BY source;
SELECT source, age
FROM asset_inventory_freshness
WHERE now() - newest_seen_at > interval '26 hours';
SQL
You should see one row per source in the first result set, and zero rows in the second when collection is healthy.
Verify it works
Run the pipeline once, then verify both data and freshness.
/usr/local/bin/run-asset-inventory.sh; echo "exit_code=$?"
psql "$DATABASE_URL" -x -c "SELECT source, count(*) AS assets, max(last_seen_at) AS newest_seen_at FROM asset_inventory GROUP BY source ORDER BY source;"
psql "$DATABASE_URL" -Atc "SELECT count(*) FROM asset_inventory WHERE last_seen_at > now() - interval '2 hours';"
Expected success shape:
upserted=42
upserted=318
exit_code=0
-[ RECORD 1 ]--+------------------------------
source | aws-ec2
assets | 42
newest_seen_at | 2026-10-30 14:15:02.114947+00
-[ RECORD 2 ]--+------------------------------
source | mdm
assets | 318
newest_seen_at | 2026-10-30 14:15:03.021881+00
360
If a source is stale, the wrapper exits 2 and /tmp/asset_inventory_stale.txt contains lines like:
mdm 1 day 03:12:44.1023
Common pitfalls
Source-specific IDs collide across systems
Mistake: using asset_key values like 12345 from multiple APIs.
Symptom: rows overwrite each other; counts look too low and source flips between runs.
Fix: prefix every key with the source and type, for example aws-ec2:i-0123456789abcdef0 and mdm-device:C02ABC12345.
Treating one source as the full inventory
Mistake: only ingesting cloud VMs and assuming laptops, runners, containers, and SaaS-managed devices do not matter.
Symptom: audit asks for all assets; your table only contains servers.
Fix: add at least one endpoint/MDM source and one build/runtime source, each with its own source value and freshness check.
curl succeeds with HTML login instead of JSON
Mistake: missing or expired API token, but the endpoint redirects to SSO or returns a login page.
Symptom: jq fails with parse error: Invalid numeric literal or Cannot iterate over null.
Fix: inspect headers, then fix auth.
curl -I "$MDM_API_BASE/devices"
Typical bad output shape:
HTTP/1.1 302 Found
location: https://login.example.com/oauth2/authorize
content-type: text/html; charset=utf-8
Use curl -fsS -H "Authorization: Bearer $MDM_TOKEN" ... and verify the API returns content-type: application/json.
Scheduler runs with a different environment than your shell
Mistake: the script works interactively but cron cannot find aws, jq, or environment variables.
Symptom: /var/log/asset-inventory.log shows /bin/sh: aws: not found or missing from ${VAR:?missing}.
Fix: use absolute paths in the wrapper and export variables inside the script or source /etc/asset-inventory.env.
Deleting unseen assets immediately
Mistake: turning every missing item in one run into a delete.
Symptom: transient API failures or rate limits make assets disappear from the inventory and reappear later.
Fix: do not hard-delete on first miss; keep last_seen_at and mark assets stale only after a threshold such as 7 days.
API pagination silently truncates your inventory
Mistake: ingesting only the first page from a REST API.
Symptom: counts cap at a suspicious round number like 100 or 1000.
Fix: follow the provider's pagination token or next link until exhausted before piping into collect_assets.py.
This article was written by an AI system and published pending human review. Verify anything you intend to act on.
Have a project in mind?
Get an instant AI price estimate for it, or talk directly to our team.
One email a month on what we learn building with AI