Welcome, Guest. Please login or register.

Author Topic: PostgreSQL query problem  (Read 120 times)

ascii

  • Jr. Member
  • **
  • Posts: 90
    • View Profile
PostgreSQL query problem
« on: January 07, 2020, 10:10:56 AM »
Hello together,

i just finished my migartion from mySQL to postgreSQL
everythink is workint so far.

Only downside is my paralell discovery. I is made for mysql and not working in postgresql.
I tried to alter it but no luck.
In mysql i used this
Code: [Select]
####  over the day  ####
#4,30 2-23 * * *        perl /var/nedi/nedi.pl -SWGflms -A 'RIGHT(INET_NTOA(devip), 1) IN (0, 6)' > /tmp/nedi-seedlist.ip.0u6.log 2>&1
#1,31 2-23 * * *        perl /var/nedi/nedi.pl -SWGflms -A 'RIGHT(INET_NTOA(devip), 1) IN (1, 5)' > /tmp/nedi-seedlist.ip.1u5.log 2>&1
#2,32 2-23 * * *        perl /var/nedi/nedi.pl -SWGflms -A 'RIGHT(INET_NTOA(devip), 1) IN (2, 7)' > /tmp/nedi-seedlist.ip.2u7.log 2>&1
#3,33 2-23 * * *        perl /var/nedi/nedi.pl -SWGflms -A 'RIGHT(INET_NTOA(devip), 1) IN (3, 8)' > /tmp/nedi-seedlist.ip.3u8.log 2>&1
#0,34 2-23 * * *        perl /var/nedi/nedi.pl -SWGflms -A 'RIGHT(INET_NTOA(devip), 1) IN (4, 9)' > /tmp/nedi-seedlist.ip.4u9.log 2>&1

the right to cutof the IPs is not working in postgresql
Code: [Select]
$ perl /var/nedi/nedi.pl -SWGflms -v -d d -A 'RIGHT(INET_NTOA(devip), 1) IN (0, 6)'
RDEV:2227 devices read from nedi.devices
DBG :SELECT device,inet_ntoa(devip),readcomm,snmpversion & 3 FROM devices LEFT JOIN configs USING (device) WHERE snmpversion > 0 AND RIGHT(INET_NTOA(devip), 1) IN (0, 6) key=
DBD::Pg::db selectall_arrayref failed: FEHLER:  Funktion right(inet, integer) existiert nicht
ZEILE 1: ... configs USING (device) WHERE snmpversion > 0 AND RIGHT(INET...

i tired is also with cast to convert but it's also not working
Code: [Select]
$ perl nedi.pl -SWGflms -v -d d -A "RIGHT(CAST(inet_ntoa(devip) AS text),1) IN (0, 6)"
RDEV:2227 devices read from nedi.devices
DBG :SELECT device,inet_ntoa(devip),readcomm,snmpversion & 3 FROM devices LEFT JOIN configs USING (device) WHERE snmpversion > 0 AND RIGHT(CAST(inet_ntoa(devip) AS text),1) IN (0, 6) key=
DBD::Pg::db selectall_arrayref failed: FEHLER:  Operator existiert nicht: text = integer
ZEILE 1: ...on > 0 AND RIGHT(CAST(inet_ntoa(devip) AS text),1) IN (0, 6)

maybe same SQL/Database expert is with us and could help me out


EDIT:
As a workaround for now i moved to
Code: [Select]
####  over the day  ####   PostgreSQL #######
1,31 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.10\%'" > /tmp/nedi-seedlist.ip.10.log 2>&1
2,32 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.20\%'" > /tmp/nedi-seedlist.ip.20.log 2>&1
3,33 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.3_\%'" > /tmp/nedi-seedlist.ip.3.log 2>&1
4,34 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.4_\%'" > /tmp/nedi-seedlist.ip.4.log 2>&1
5,35 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.5_\%'" > /tmp/nedi-seedlist.ip.5.log 2>&1
6,36 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.6_\%'" > /tmp/nedi-seedlist.ip.6.log 2>&1
7,37 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.7_\%'" > /tmp/nedi-seedlist.ip.7.log 2>&1
8,38 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.8_\%'" > /tmp/nedi-seedlist.ip.8.log 2>&1
9,39 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.9_\%'" > /tmp/nedi-seedlist.ip.9.log 2>&1

11,41 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.11\%'" > /tmp/nedi-seedlist.ip.11.log 2>&1
21,41 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.21\%'" > /tmp/nedi-seedlist.ip.21.log 2>&1
12,42 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.12\%'" > /tmp/nedi-seedlist.ip.12.log 2>&1
22,42 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.22\%'" > /tmp/nedi-seedlist.ip.22.log 2>&1
13,43 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.13\%'" > /tmp/nedi-seedlist.ip.13.log 2>&1
23,43 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.23\%'" > /tmp/nedi-seedlist.ip.23.log 2>&1
14,44 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.14\%'" > /tmp/nedi-seedlist.ip.14.log 2>&1
24,44 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.24\%'" > /tmp/nedi-seedlist.ip.24.log 2>&1
15,45 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.15\%'" > /tmp/nedi-seedlist.ip.15.log 2>&1
25,45 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.25\%'" > /tmp/nedi-seedlist.ip.25.log 2>&1
16,46 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.16\%'" > /tmp/nedi-seedlist.ip.16.log 2>&1
17,47 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.17\%'" > /tmp/nedi-seedlist.ip.17.log 2>&1
18,48 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.18\%'" > /tmp/nedi-seedlist.ip.18.log 2>&1
19,49 2-23 * * * perl /var/nedi/nedi.pl -P 2 -Smv -A "CAST(inet_ntoa(devip) AS text) ~~* '\%.\%.\%.19\%'" > /tmp/nedi-seedlist.ip.19.log 2>&1
« Last Edit: January 07, 2020, 11:41:14 AM by ascii »