--- Begin Message ---
Hi Oliver, ive expanded a bit on this - ive added in SIM Number reporting using
“c3gIccId” as part of the third column.
custom_fields:
device:
- name: 'c3gImei'
label: 'IMEI Number'
snmp_object: 'CISCO-WAN-3G-MIB::c3gImei'
- name: 'c3gGsmNetwork'
label: 'GSM Network'
snmp_object: 'CISCO-WAN-3G-MIB::c3gGsmNetwork'
- name: 'c3gIccId'
label: 'SIM Number'
snmp_object: 'CISCO-WAN-3G-MIB::c3gIccId'
reports:
- tag: cisco_cellular_routers
label: 'Cisco Cellular IMEI & SIM Tracker'
category: Device
columns:
- { devname: 'Name' }
- { ip: 'Device IP', _searchable: true }
- { carrier: 'Carrier Network' }
- { imei: 'IMEI' }
- { simnum: 'SIM Number' }
query: |
SELECT COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname, ip,
CASE
WHEN custom_fields->>'c3gGsmNetwork' IN ('[""]', '["000 00"]')
THEN 'Not Detected'
WHEN custom_fields->>'c3gGsmNetwork' LIKE '[%"%'
THEN REPLACE(REPLACE(TRIM(BOTH '["]' FROM
custom_fields->>'c3gGsmNetwork'), '","', ' | '), '"', '')
ELSE NULL
END AS carrier,
CASE
WHEN custom_fields->>'c3gImei' IN ('[""]', '["000 00"]')
THEN 'Not Detected'
WHEN custom_fields->>'c3gImei' LIKE '[%"%'
THEN REPLACE(REPLACE(TRIM(BOTH '["]' FROM custom_fields->>'c3gImei'),
'","', ' | '), '"', '')
ELSE NULL
END AS imei,
CASE
WHEN custom_fields->>'c3gIccId' IN ('[""]', '["000 00"]')
THEN 'Not Detected'
WHEN custom_fields->>'c3gIccId' LIKE '[%"%'
THEN REPLACE(REPLACE(TRIM(BOTH '["]' FROM
custom_fields->>'c3gIccId'), '","', ' | '), '"', '')
ELSE NULL
END AS simnum
FROM device
WHERE
(custom_fields->>'c3gGsmNetwork' LIKE '[%"%')
OR
(custom_fields->>'c3gImei' LIKE '[%"%')
OR
(custom_fields->>'c3gIccId' LIKE '[%"%')
ORDER BY ip ASC;
From: Oliver Gorwits <[email protected]>
Date: Tuesday, 13 May 2025 at 05:29
To: Muris <[email protected]>
Cc: Christian Ramseyer <[email protected]>,
<[email protected]>
Subject: Re: [Netdisco] Cisco IMEI tracker report for cellular services
I correct myself: I think the error isn't malformed json, just a json data
structure that Netdisco isn't expecting (to retrieve).
regards
oliver.
On Mon, 12 May 2025 at 20:44, Oliver Gorwits <[email protected]> wrote:
great investigative work, thank you!
it puzzles me that the crash is after the data is stored into the jsonb field
... it should be sanity checked by postgresql on the way in. and the field type
of custom_fields is already jsonb so I'm not sure why the cast is needed.
anyway, Netdisco can apply better sanity checking as well, I will look into
that.
regards
oliver.
On Mon, 12 May 2025 at 05:32, Muris <[email protected]> wrote:
I found what the issue is after more investigation - the crash was caused by
some devices responding to SNMP queries with malformed or incomplete values for
the following fields;
custom_fields:
device:
- name: 'c3gImei'
label: 'IMEI Number'
snmp_object: 'CISCO-WAN-3G-MIB::c3gImei'
- name: 'c3gGsmNetwork'
label: 'GSM Network'
snmp_object: 'CISCO-WAN-3G-MIB::c3gGsmNetwork'
These fields are globally polled across all devices. For devices that aren’t
cellular-capable, SNMP responses can be empty strings or placeholders, which
are stored in the custom_fields JSONB column. When these are cast to ::jsonb in
a report, PostgreSQL throws an error if the value is invalid JSON.
Original Report:
reports:
- tag: cisco_cellular_routers
label: 'Cisco Cellular IMEI Tracker'
category: Device
columns:
- { devname: 'Name' }
- { ip: 'Device IP', _searchable: true }
- { carrier: 'Carrier Network' }
- { imei: 'IMEI' }
query: |
SELECT COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname, ip,
CASE
WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') THEN
ARRAY(SELECT
jsonb_array_elements_text((custom_fields->>'c3gGsmNetwork')::jsonb))::text[]
ELSE NULL
END AS carrier,
CASE
WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') THEN
ARRAY(SELECT
jsonb_array_elements_text((custom_fields->>'c3gImei')::jsonb))::text[]
ELSE NULL
END AS imei
FROM device
WHERE
(jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') AND
custom_fields->>'c3gGsmNetwork' IS NOT NULL AND custom_fields->>'c3gGsmNetwork'
<> '[]')
OR
(jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') AND
custom_fields->>'c3gImei' IS NOT NULL AND custom_fields->>'c3gImei' <> '[]')
ORDER BY ip ASC;
This works fine if the data is perfect JSON — but crashes as soon as a
malformed value is stored in custom_fields.
I then modified the report to avoid JSON casting entirely and instead format
the values as plain strings. This eliminates the risk of crashing and allows
for consistent parsing and display. I also added fallback display text for
common placeholders like [""] or ["000 00"], and if the field is effectively
blank, it now shows "Not Detected but SIM present". This approach is safer,
cleaner, and more resilient - even when devices return unexpected or malformed
SNMP responses.
reports:
- tag: cisco_cellular_routers2
label: 'Cisco Cellular IMEI Tracker (Safe)'
category: Device
columns:
- { devname: 'Name' }
- { ip: 'Device IP', _searchable: true }
- { carrier: 'Carrier Network' }
- { imei: 'IMEI' }
query: |
SELECT COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname, ip,
CASE
WHEN custom_fields->>'c3gGsmNetwork' IN ('[""]', '["000 00"]')
THEN 'Not Detected but SIM present'
WHEN custom_fields->>'c3gGsmNetwork' LIKE '[%"%'
THEN REPLACE(REPLACE(TRIM(BOTH '["]' FROM
custom_fields->>'c3gGsmNetwork'), '","', ' | '), '"', '')
ELSE NULL
END AS carrier,
CASE
WHEN custom_fields->>'c3gImei' IN ('[""]', '["000 00"]')
THEN 'Not Detected but SIM present'
WHEN custom_fields->>'c3gImei' LIKE '[%"%'
THEN REPLACE(REPLACE(TRIM(BOTH '["]' FROM custom_fields->>'c3gImei'),
'","', ' | '), '"', '')
ELSE NULL
END AS imei
FROM device
WHERE
(custom_fields->>'c3gGsmNetwork' LIKE '[%"%')
OR
(custom_fields->>'c3gImei' LIKE '[%"%')
ORDER BY ip ASC;
Long-Term Suggestions
1. Graceful handling of unsupported SNMP field responses
Netdisco could skip or sanitize custom_fields values if the SNMP response is
clearly empty, malformed, or invalid (e.g., "", noSuchInstance).
2. Add JSON-safe casting helpers in the report engine
Internal helper functions that verify and validate custom_fields before
applying ::jsonb casts could help avoid user side crashes.
3. Optional “safe display” mode for reports
A config option or flag for reports that instructs Netdisco to treat custom
field data as plain text and format it for readability — stripping brackets,
quotes, etc., without requiring the user to manually write TRIM/REPLACE logic.
On 7/5/2025, 9:14 pm, "Christian Ramseyer" <[email protected]
<mailto:[email protected]>> wrote:
The SQL itself seems to work correctly for me (empty result, none
expected since I don't collect these fields, but no error).
Do you get an error even when you paste the SQL into netdisco-do psql?
Or only when running it from the web interface? If the latter, please
post the full report definition as it appears in the config.
Cheers
Christian
On 06.05.2025 07:01, Muris wrote:
> SELECT me.devname, me.ip, me.carrier, me.imei FROM (SELECT
> COALESCE(NULLIF(dns,''), NULLIF(name,''), '') AS devname, ip,
>
> CASE
>
> WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') THEN
>
> ARRAY(SELECT jsonb_array_elements_text((custom_fields-
> >>'c3gGsmNetwork')::jsonb))::text[]
>
> ELSE NULL
>
> END AS carrier,
>
> CASE
>
> WHEN jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') THEN
>
> ARRAY(SELECT jsonb_array_elements_text((custom_fields-
> >>'c3gImei')::jsonb))::text[]
>
> ELSE NULL
>
> END AS imei
>
> FROM device
>
> WHERE
>
> (jsonb_path_exists(custom_fields::jsonb, '$.c3gGsmNetwork') AND
> custom_fields->>'c3gGsmNetwork' IS NOT NULL AND custom_fields-
> >>'c3gGsmNetwork' <> '[]')
>
> OR
>
> (jsonb_path_exists(custom_fields::jsonb, '$.c3gImei') AND custom_fields-
> >>'c3gImei' IS NOT NULL AND custom_fields->>'c3gImei' <> '[]')
>
> ORDER BY ip ASC
>
> ) me
>
--- End Message ---
