netdisco-users Digest, Vol 229, Issue 4

[netdisco-users-request]

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 (Mackie, Paul)

--- Begin Message ---

Hi Muris,

I just want to say this sounds very interesting to me and thank you for deep 
diving into it. I have a cron job that restarts the container if the same error 
is seen in the logs (custom fields for Aruba AP serial numbers). Would be great 
if I can finally remove my dirty hack 😊

I also don’t understand how custom fields are needed for neighbour maps, but 
let’s see.

Best regards,

Paul Mackie



GEA INTERNAL

From: Muris <alcat...@gmail.com>
Sent: 24 October 2025 13:29
To: Oliver Gorwits <oli...@cpan.org>
Cc: netdisco-users@lists.sourceforge.net
Subject: Re: [Netdisco] Cisco IMEI tracker report for cellular services

Hi Oliver, I just wanted to let you know after looking at this closer I found 
the issue -

Root cause:
In /home/netdisco/perl5/lib/perl5/App/Netdisco/Web/Plugin/Device/Neighbors.pm 
calls ->with_custom_fields, which forces all custom_fields values to be cast as 
JSON arrays.
Because some devices return empty or malformed SNMP strings, the JSON cast 
fails, and the Neighbours endpoint crashes before rendering the map.

Fix applied (minimal and effective):
In /home/netdisco/perl5/lib/perl5/App/Netdisco/Web/Plugin/Device/Neighbors.pm,
I removed ->with_custom_fields from this line:

my $devices = schema(vars->{'tenant'})->resultset('Device')->search({}, {
  '+select' => [\'floor(log(throughput.total))'], '+as' => ['log'],
  join => 'throughput', distinct => 1,
})->with_times->with_custom_fields;

changed to:

})->with_times;

Result:
- The Neighbours tab now loads correctly again.
- No other Netdisco components (reports, discovery, backend) were affected.
- The map works faster, since it no longer expands unnecessary JSON fields.

What do you think about this, is custom_fields required in the neighbors.pm ?

Regards,
Muris

From: Muris <alcat...@gmail.com<mailto:alcat...@gmail.com>>
Date: Tuesday, 21 October 2025 at 12:46
To: Oliver Gorwits <oli...@cpan.org<mailto:oli...@cpan.org>>
Cc: Christian Ramseyer <ramse...@netnea.com<mailto:ramse...@netnea.com>>, 
<netdisco-users@lists.sourceforge.net<mailto:netdisco-users@lists.sourceforge.net>>
Subject: Re: [Netdisco] Cisco IMEI tracker report for cellular services

Hi Oliver,

I wanted to let you know that even though I got the Cisco IMEI / Cellular 
Services report working fine with the custom fields in deployment.yml, it seems 
to have broken the Neighbours tab in the web interface - it now shows a blank 
page. I didn’t realize it broke this as I hardly even use that tab, but others 
do so they reported it as an issue.
It also says in Error 500 with dancer , it doesn’t show that but page Is blank 
in neighbours tab, but I see it in the web developer tools in network inside a 
browser.

Here’s the log output from netdisco backend when the Neighbours page loads in 
short form:

DBIx::Class::ResultSet::next(): DBI Exception: DBD::Pg::st execute failed:
ERROR: invalid input syntax for type json
DETAIL: The input string ended unexpectedly.
CONTEXT: JSON data, line 1: [
STATEMENT: SELECT json_array_elements_text((me.custom_fields ->> 
'c3gIccId')::json),
json_array_elements_text((me.custom_fields ->> 'c3gGsmNetwork')::json),
json_array_elements_text((me.custom_fields ->> 'c3gImei')::json)
FROM device me ...
at /home/netdisco/perl5/lib/perl5/App/Netdisco/Web/Plugin/Device/Neighbors.pm 
line 244

It looks like the Neighbours/Netmap query is trying to expand all custom_fields 
as JSON arrays and fails if any of them contain plain text.

Would it be possible for the Neighbours view to exclude or ignore specific 
custom_fields (like these Cisco ones), or handle non-JSON values more safely so 
the page doesn’t break?

And how would I fix the broken neighbours page now?

Regards,
Muris

From: Muris <alcat...@gmail.com<mailto:alcat...@gmail.com>>
Date: Thursday, 2 October 2025 at 14:36
To: Oliver Gorwits <oli...@cpan.org<mailto:oli...@cpan.org>>
Cc: Christian Ramseyer <ramse...@netnea.com<mailto:ramse...@netnea.com>>, 
<netdisco-users@lists.sourceforge.net<mailto:netdisco-users@lists.sourceforge.net>>
Subject: Re: [Netdisco] Cisco IMEI tracker report for cellular services

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 <oli...@cpan.org<mailto:oli...@cpan.org>>
Date: Tuesday, 13 May 2025 at 05:29
To: Muris <alcat...@gmail.com<mailto:alcat...@gmail.com>>
Cc: Christian Ramseyer <ramse...@netnea.com<mailto:ramse...@netnea.com>>, 
<netdisco-users@lists.sourceforge.net<mailto:netdisco-users@lists.sourceforge.net>>
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 
<oli...@cpan.org<mailto: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<mailto: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> 
<mailto: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