NetXMS Support Forum

Old web api path summaryTable/adHoc vs restapi summary-table/ad-hoc

curl -s -X POST --cookie "session_handle=__HASH__" --data @netxms.json "http://localhost:8080/netxms-websvc/summaryTable/adHoc"
netxms.json, same as rest-api:
{
    "baseObject":"ContainerName",
    "columns": [
        {
        "columnName":"Free form name that will be used in return table for this column",
        "dciName":"Name of DCI, that will be used for filtering"
        },
        {
        "columnName":"Name2",
        "dciName":"DCIName2"
        }
    ]
}

If you are using 3.9.280 now, you can safe upgarde to latest 3.9 that is 3.9.420 first
Atleast i found 3.9.420 in repo for ubuntu focal https://packages.netxms.org/ubuntu/pool/focal/main/n/netxms/

You can also migrate the DB to a virtual server, just use a Postgresql guide on how to migrate data.
Then you have both Bare-Metal db + VM DB you can connect NetXMS server to.
You can now edit netxms Bare-Metal installation to point to VM DB (Ie Snapshot as backup), then upgrade bare-metal install to latest 4.4 stable.
Rollback plan will be to reinstall  3.9.420 and point to bare-metal db that you didnt touch

Applies to: UPS CS121/CS141 plugin module from Coromatic/Generex/++

Notes:

Instance discovery on attached sensors.

Applies to: AKCP sensorProbe2  https://www.akcp.com/akcp-products/sensorprobe-series/sensorprobe2/

Notes: Instance discover on attached sensors and grab name from device

We have a pfSense node with SNMP activated that gets recreated as a object every hr because its a GW/FW with public IP as VIP and nodes behind that public IP is NAT attached.
Node behind will have public IP as primary IP, that will be a duplicate of GW/FW.

I assume its the "NetworkDiscovery.MergeDuplicateNodes" that causes this to happen and it would be greate if this could have been disabled on selected Object only also

Some DevOp in a company i was earlier made a Groovy script for this that could be executed in Ansible.
It was created for earlier version, but  do not have it aviable anymore.

Script used netxms-client library and session.setObjectMaintenanceMode(object.objectId, true, comment) to enter maintenance.

In Ansible "script" was used, something like this:

Quote- name: Enter NetXMS maintenance mode
  become: false
  script:
    ./scripts/netxms-cli enter-maintenance {{ netxms_hostname | default(inventory_hostname) | quote }} --comment "Upgrading linux system"
  vars:
    ansible_command_timeout: 40
  throttle: 5
  delegate_to: localhost
  ignore_errors: yes

We are also using this OID.

Here is some more info: https://oidref.com/1.3.6.1.2.1.15.3.1.2

SYNTAX INTEGER {
idle(1),
connect(2),
active(3),
opensent(4),
openconfirm(5),
established(6)
}

Thanks!
This change has passed me by..

Hi

After upgrade to NetXMS 4.0.x a API call we have used stopped working.
Did anything change in way summaryTable/adHoc works?

http://_HOST_/netxms-websvc/summaryTable/adHoc with this payload gives Internal error 46.
In NetXMS 3.x it returned the summarytable.


{
      "baseObject":"Infrastructure Services",
      "multiInstance":False,
      "columns": [
          {
          "columnName":"IP",
          "dciName":"Dummy(PrimaryIP)"
          },
          {
          "columnName":"Type",
          "dciName":".1.0.8802.1.1.2.1.5.1.1.2.7.0"
          },
          {
          "columnName":"OS",
          "dciName":"Dummy(System.OS)"
          }
      ]
  }

If you our out after binding new nodes that have not been put into other containers you can use this on a container you name ex "_NewNode"
It will only bind if the node is not part of any other container.
And if you use auto unbind it will unbind when its put into other container.

for (p : $node->parents) {
  if ( (p->type == 5 or p->type == 14)
  && ( p->name != "_NewNode") ) {
    return false;
  }
}

return true;

I increased max_locks_per_transaction from 64 to 512 in postgresql.conf after finding this errors in postgresql.log as you pointed me too Filip.
After this change, nxdbmgr completed without problems - Thanks!

Logs showed this:

Quote2021-04-13 13:59:31.878 CEST [834154] usr_netxms@db_netxms ERROR:  out of shared memory
2021-04-13 13:59:31.878 CEST [834154] usr_netxms@db_netxms HINT:  You might need to increase max_locks_per_transaction.
2021-04-13 13:59:31.878 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM idata_sc_30

Quote2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM idata_sc_30
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM idata_sc_90
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM idata_sc_180
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM idata_sc_other
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.986 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1618315671)
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM tdata_sc_default
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.987 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM tdata_sc_7
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM tdata_sc_30
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM tdata_sc_90
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM tdata_sc_180
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms ERROR:  current transaction is aborted, commands ignored until end of transaction block
2021-04-13 14:07:51.988 CEST [834154] usr_netxms@db_netxms STATEMENT:  SELECT distinct(item_id) FROM tdata_sc_other

I have manually deleted the problems, but still get the 25P02 error that can be solved with "savepoint": https://www.postgresql.org/docs/current/sql-savepoint.html

SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_30                                                                                                                                                                                                                                                                                                                                                                              75>>> SELECT distinct(item_id) FROM idata_sc_90
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_90                                                                                                                                                                                                                                                                                                                                                                              83>>> SELECT distinct(item_id) FROM idata_sc_180
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_180                                                                                                                                                                                                                                                                                                                                                                             91>>> SELECT distinct(item_id) FROM idata_sc_other
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_other                                                                                                                                                                                                                                                                                                                                                                        [PASSED]
* Table DCI history records                                            [   0>>> SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1618041778)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1618041778)                                                                                                                                                                                                                                                                                                                                  8>>> SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1618041778)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1618041778)                                                                                                                                                                                                                                                                                                                                       16>>> SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1618041778)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1618041778)                                                                                                                                                                                                                                                                                                                                      25>>> SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1618041778)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1618041778)                                                                                                                                                                                                                                                                                                                                      33>>> SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1618041778)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1618041778)                                                                                                                                                                                                                                                                                                                                     41>>> SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1618041778)
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1618041778)                                                                                                                                                                                                                                                                                                                                   50>>> SELECT distinct(item_id) FROM tdata_sc_default
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_default                                                                                                                                                                                                                                                                                                                                                                         58>>> SELECT distinct(item_id) FROM tdata_sc_7
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_7                                                                                                                                                                                                                                                                                                                                                                               66>>> SELECT distinct(item_id) FROM tdata_sc_30
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_30                                                                                                                                                                                                                                                                                                                                                                              75>>> SELECT distinct(item_id) FROM tdata_sc_90
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_90                                                                                                                                                                                                                                                                                                                                                                              83>>> SELECT distinct(item_id) FROM tdata_sc_180
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_180                                                                                                                                                                                                                                                                                                                                                                             91>>> SELECT distinct(item_id) FROM tdata_sc_other
SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_other                                                                                                                                                                                                                                                                                                                                                                        [PASSED]
Database doesn't contain any errors
>>> UPDATE config SET var_value='UNLOCKED' WHERE var_name='DBLockStatus'
>>> UPDATE config SET var_value='' WHERE var_name='DBLockInfo'
>>> UPDATE config SET var_value='0' WHERE var_name='DBLockPID'
Database check completed
>>> SELECT var_value FROM metadata WHERE var_name='PendingOnlineUpgrades'


Thanks.

Also got this when doing "Clear collected data" on 5 DCIs

06.04.2021 15:14:59 netxms01 Normal SYS_NODE_NORMAL Node status changed to NORMAL
06.04.2021 15:14:59 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: Internal error (pResult is NULL in UnsafeDrvSelect))
06.04.2021 15:14:59 netxms01 Normal SYS_DB_CONN_RESTORED Connection with backend database engine restored
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:58 netxms01 Critical SYS_NODE_CRITICAL Node status changed to CRITICAL
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: INSERT INTO alarms (alarm_id,parent_alarm_id,creation_time,last_change_time,source_object_id,zone_uin,source_event_code,message,original_severity,current_severity,alarm_key,alarm_state,ack_by,resolved_by,hd_state,hd_ref,repeat_count,term_by,timeout,timeout_event,source_event_id,ack_timeout,dci_id,alarm_category_ids,rule_guid,event_tags,rca_script_name,impact) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT port FROM snmp_ports WHERE zone=? OR zone=-1 ORDER BY zone DESC, id ASC; Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:58 netxms01 Critical SYS_DB_QUERY_FAILED Database query failed (Query: SELECT avg(idata_value::double precision) FROM idata_sc_7 WHERE item_id=? AND idata_timestamp BETWEEN to_timestamp(?) AND to_timestamp(?) AND idata_value~E'^\\d+(\\.\\d+)*$'; Error: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.)
06.04.2021 15:14:55 netxms01 Critical SYS_DB_CONN_LOST Lost connection with backend database engine

Have been running a server with timescaledb on Ubuntu 20.04 for 2months now.
Its been abit unstable compared to have running default PostGresql over years on other setups.

Could i have a currupt database?

nxdbmgr will give this "in loop" when running "nxdbmgr check -d -f" as it fails to commit the fix:

Found collected data for non-existing DCI [6152]. Delete invalid records? (Yes/No/All/Skip) Y
* DCI history records                                                  [  66% ]

SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_30                                                                                                                                                                                                                                                       75SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_90                                                                                                                                                                                                                                                       83SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_180                                                                                                                                                                                                                                                      91SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM idata_sc_other                                                                                                                                                                                                                                                 [FIXED ]
* Table DCI history records                                            [   0SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_default WHERE tdata_timestamp > to_timestamp(1617706540)                                                                                                                                                                                                           8SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_7 WHERE tdata_timestamp > to_timestamp(1617706540)                                                                                                                                                                                                                16SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_30 WHERE tdata_timestamp > to_timestamp(1617706540)                                                                                                                                                                                                               25SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_90 WHERE tdata_timestamp > to_timestamp(1617706540)                                                                                                                                                                                                               33SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_180 WHERE tdata_timestamp > to_timestamp(1617706540)                                                                                                                                                                                                              41SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT count(*) FROM tdata_sc_other WHERE tdata_timestamp > to_timestamp(1617706540)                                                                                                                                                                                                            50SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_default                                                                                                                                                                                                                                                  58SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_7                                                                                                                                                                                                                                                        66SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_30                                                                                                                                                                                                                                                       75SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_90                                                                                                                                                                                                                                                       83SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_180                                                                                                                                                                                                                                                      91SQL query failed (25P02 ERROR:  current transaction is aborted, commands ignored until end of transaction block):
SELECT distinct(item_id) FROM tdata_sc_other                                                                                                                                                                                                                                                 [PASSED]
106 errors was found, 106 errors was corrected
All errors in database was fixed
Commit changes? (Yes/No) Y


ddockx:
If installed with .deb/apt service is called "netxmsd"
Correct command will then be:

Quotesystemctl start netxmsd