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
(Oliver Gorwits)
2. Re: Cisco IMEI tracker report for cellular services
(Oliver Gorwits)
--- Begin Message ---
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 <alcat...@gmail.com> 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" <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 ---
--- Begin Message ---
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 <oli...@cpan.org> 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 <alcat...@gmail.com> 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" <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
