ZENworks PostgreSQl database - Tips & Utilities

Document ID:7024768
Creation Date:03-Aug-2020
Modified Date:17-Feb-2021
- Micro Focus Products:
  Service Desk
  ZENworks Configuration Management

Environment

Service Desk
ZENworks Configuration Management

Situation

Postgres Database can be an internal database in the ZENworks Systems.

Postgres syntax is related in this document.

You can use 3rd party tools to navigate through the database - see Additional Information below.

Example Environment

ZCM Appliance
ZENworks DB Name: zenworks
Audit DB Name: zenworksaudit

DB Connection Username: zenadmin
Password: (hint: Use command zman dgc)
Port: 54327

ZSD Appliance

Appliance Server name: NIM-ZSD

DBName: servicedesk
Port: 5432

Resolution

Remember postgresql will have case sensitivity on by default.
Connecting to the database - verify case sensitivity.

Example:
ZSD configuring Integrations with a ZCM db - Sybase db is ZENworksDB
After migrating to PostgreSQL check case - it maybe zenworksdb

It is possible to run postgres command directly in the internal database.

Example: ZENworks Service Desk Server Name is NIM-ZSD
Log in to appliance as root/pw and run queries

NIM-ZSD:~# su - postgres

postgres@NIM-ZSD:~>

Check postgres Version

postgres@NIM-ZSD:~> psql -V

Connect to the DB

postgres@NIM-ZSD:~> psql \servicedesk

Run db queries (don’t forget the ending ; )
servicedesk=# : select * from team;

Run a script
servicedesk=# \i <filename>.sql;

Restore a dump of the database (see Additional Information for details)
servicedesk=# psql -U postgres -d servicedesk < /tmp/zsdDb.backup

Quit Service Desk db command line

servicedesk=# \quit

Exit from Service Desk Database console
postgres@NIM-ZSD:~>exit

Log out of elevated user

NIM-ZSD:~# logout

NIM-ZSD:~#

Backup the internal PostgreSQL Database (will be prompted for the db password)
NIM-ZSD:~# pg_dump --dbname=postgresql://sdadmin@localhost:5432/servicdesk --file=/tmp/zsdDb.backup --encoding=UTF8 --blobs --format=tar
password:

(see Additional Information below for details on the db password)

Appliance command for the database

ZCM Check service: systemctl [status | stop | start | restart] zenpostgresql.service | more

ZCM Check version: cat /vastorage/var/opt/novell/pgsql/data/PG_VERSION

ZSD Check service: systemctl [status | stop | start | restart] postgresql.service | more

ZSD Check version: cat /vastorage/pgsql/data/PG_VERSION (or see above logged in as postgres)

Additional Information

ZENworks 2020 Database Documentation

ZENworks Service Desk v8.1.2 Database Configuration Setup

To change the ZSD use the Service Desk Configuration Tile in the Administrators Console (9443)

Postgres Documentation,

pg_dump command

Utilities

PGAdmin4

By default PGAdmin 4 doesn't pick up the guid hex so encode is needed in the syntax.

Syntax example to display guid zuid with encode(zuid::bytea,'hex')

MSSQL: select zuid, platform, hostname, password from zdevice

PGAdmin4: select encode(zuid::bytea, 'hex'),platform, hostname,password from zdevice

PGAdmin4 examples to call out guid id with '\x<guid>'

Example:

MSSQL syntax:
select * from zdevice where zuid = 0x99d89b65dc2e0f43a8bbbfff4684710a;

PGAdmin4 syntax:
select * from zdevice where zuid = '\x99d89b65dc2e0f43a8bbbfff4684710a';

DBeaver
By default DBeaver doesn't pick up the guid hex.
Modifications to the default properties helps display GUID & easier syntax.
DBeaver Properties (global) for Editors > Data Editor > Editors - Binary data format=hex

DBeaver examples to call out guid id with '\x<guid>

MSSQL syntax: select * from zdevice where zuid = 0x99d89b65dc2e0f43a8bbbfff4684710a;

DBeaver syntax before change format hex:

select * from zbundle where zuid = decode('C9C0914A6E3C8DC13B1CA496F3FB7055','hex');

     DBeaver syntax after change format to hex:
     select * from zdevice where zuid = '\x99d89b65dc2e0f43a8bbbfff4684710a';