ZENworks PostgreSQl database - Tips & Utilities

  • 7024768
  • 03-Aug-2020
  • 17-Feb-2021

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

To change the ZSD use the Service Desk Configuration Tile in the Administrators Console (9443)
   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';