Menu
▾
▴
Re: [Netdisco] Cisco IMEI tracker report for cellular services
|
From: Oliver G. <ol...@cp...> - 2025-12-13 16:34:08
|
Hi Paul and Muris, I've released 2.096001 which has a fix, I believe, for
this issue. Do let me know if you have any further trouble.
kind regards
oliver.
On Tue, 28 Oct 2025 at 10:33, Mackie, Paul <Pau...@ge...> wrote:
> 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 <alc...@gm...>
> *Sent:* 24 October 2025 13:29
> *To:* Oliver Gorwits <ol...@cp...>
> *Cc:* net...@li...
> *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 <alc...@gm...>
> *Date: *Tuesday, 21 October 2025 at 12:46
> *To: *Oliver Gorwits <ol...@cp...>
> *Cc: *Christian Ramseyer <ram...@ne...>, <
> net...@li...>
> *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 <alc...@gm...>
> *Date: *Thursday, 2 October 2025 at 14:36
> *To: *Oliver Gorwits <ol...@cp...>
> *Cc: *Christian Ramseyer <ram...@ne...>, <
> net...@li...>
> *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 <ol...@cp...>
> *Date: *Tuesday, 13 May 2025 at 05:29
> *To: *Muris <alc...@gm...>
> *Cc: *Christian Ramseyer <ram...@ne...>, <
> net...@li...>
> *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 <ol...@cp...> 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 <alc...@gm...> 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" <ram...@ne... <mailto:
> ram...@ne...>> 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
> >
>
>
>
>
|
