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 ---
Hi Oliver,
I updated netdisco to the latest version 2.85.1 and this IMEI tracker report
has stopped working, any ideas?
This is in the backend
[14016] 2025-05-06 04:54:22 error request to GET
/ajax/content/report/cisco_cellular_routers crashed:
DBIx::Class::Storage::DBI::_dbh_execute(): 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: [for Statement "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"] at /home/netdisco/perl5/lib/perl5/App/Netdisco/Web/GenericReport.pm line
66
From: Oliver Gorwits <oli...@cpan.org>
Date: Wednesday, 18 September 2024 at 18:44
To: Muris <alcat...@gmail.com>
Cc: Christian Ramseyer <ramse...@netnea.com>,
<netdisco-users@lists.sourceforge.net>
Subject: Re: [Netdisco] Cisco IMEI tracker report for cellular services
Hi Muris, that's great news, glad you found a working query!
On Wed, 18 Sept 2024 at 01:35, Muris <alcat...@gmail.com> wrote:
Thanks for the tip I looked at alternatives and I managed to get it working
using a different method avoiding the ? operator it uses json_path_exists
function to check instead.
You could have this as alternate option for others that it doesn’t work for also
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;
On 18/9/2024, 06:09, "Christian Ramseyer" <ramse...@netnea.com
<mailto:ramse...@netnea.com>> wrote:
On 17.09.2024 19:14, Oliver Gorwits wrote:
>
> I have no idea how to fix this, sorry. I guess it could be worked around
> by refactoring the query not to use the PostgreSQL jsonb operator "?".
>
> WHERE____
>
> (custom_fields \? 'c3gGsmNetwork' AND
> json_array_length((custom_fields ->> 'c3gGsmNetwork')::json) > 0)____
>
> OR____
>
> (custom_fields \? 'c3gImei' AND json_array_length((custom_fields
> ->> 'c3gImei')::json) > 0)____
>
Which should be easy here, you can just leave out the part before the
AND and still get the same result (untested)
Cheers
Christian
_______________________________________________
Netdisco mailing list
netdisco-users@lists.sourceforge.net
https://sourceforge.net/p/netdisco/mailman/netdisco-users/
--- End Message ---
_______________________________________________
Netdisco mailing list - Digest Mode
netdisco-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/netdisco-users
