Send netdisco-users mailing list submissions to
netdisco-users@lists.sourceforge.net
To subscribe or unsubscribe via the World Wide Web, visit
https://lists.sourceforge.net/lists/listinfo/netdisco-users
or, via email, send a message with subject or body 'help' to
netdisco-users-requ...@lists.sourceforge.net
You can reach the person managing the list at
netdisco-users-ow...@lists.sourceforge.net
When replying, please edit your Subject line so it is more specific
than "Re: Contents of netdisco-users digest..."
Today's Topics:
1. Re: Cisco IMEI tracker report for cellular services (Muris)
--- Begin Message ---
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" <ramse...@netnea.com
<mailto:ramse...@netnea.com>> 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 ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
