How to run database queries in Web Help Desk.

10/28/2019 6:50 PM

8/24/2022 7:05 PM

The following information provides the steps on how to run database queries in Web Help Desk for MySQL, MSSQL and PostgreSQL.
 

Web Help Desk

Note: Please make sure to back up the database before running a query.
 

• For MSSQL - Use Microsoft SQL Studio
• For MySQL - Use Workbench

 

• For PostgreSQL (Windows) – Use pgAdmin31. Browse to C:\Program Files\WebHelpDesk\pgsql9\bin

2. Run pgAdmin3
3. On pgAdmin III window, go to File > Add Server
4. Add the following:

• Name: WHDDB
• Host: localhost
• Port: 20293
• Maintenance DB: postgres
• Username: whd
• Password: whd

5. Click Ok. Then close pgAdmin3, then run it again.
6. Expand "Databases" then double click 'whd'
7. Click the SQL icon at the top (the icon that looks like a magnifying glass)

Run Script:

REM - Scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose.
REM - The risk arising out of the use or performance of the scripts and documentation stays with you. 
REM - In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation. 

SELECT * FROM (TABLE NAME);

 

• For PostgreSQL (Linux) - Run the commands below:

# Scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose.
# The risk arising out of the use or performance of the scripts and documentation stays with you. 
# In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation. 

sudo su - postgres

-bash-4.1$

/usr/local/webhelpdesk/pgsql9/bin/psql -h 127.0.0.1 -p 20293 -U whd -d whd

whd=>

SELECT * FROM (TABLE NAME);
or
SELECT table_name FROM information_schema.tables;

Commonly used tables in Web Help Desk :
ASSET
CLIENT
LOCATION
DEPARTMENT
LOCATION
TECH
JOB_TICKET
PROBLEM TYPE

Commonly used columns in Web Help Desk :
CLIENT table:
- CLIENT_ID = shows the client ID of the client account
- FIRST_NAME = shows the first name of the client account
- EMAIL = shows the email address of the client account
- LAST_NAME = shows the last name of the client account
- USER_NAME = shows the user name of the client account
- PASSWORD = shows the encrypted password of the client account
- DELETED = shows if the client account is deleted in WHD. value of 0 is not deleted, value of 1 is deleted.
- INACTIVE = shows if the client account is active in WHD. value of 0 is not active, value of 1 is inactive.
- INCORRECT_AUTH_ATTEMPTS - shows the count of invalid login attempt of the client

TECH table:
- CLIENT_ID = shows the client ID of the client account
- FIRST_NAME = shows the first name of the client account
- EMAIL = shows the email address of the client account
- LAST_NAME = shows the last name of the client account
- USER_NAME = shows the user name of the client account
- PASSWORD = shows the encrypted password of the client account
- DELETED = shows if the client account is deleted in WHD. value of 0 is not deleted, value of 1 is deleted.
- INACTIVE = shows if the client account is active in WHD. value of 0 is not active, value of 1 is inactive.
- INCORRECT_AUTH_ATTEMPTS - shows the count of invalid login attempt of the client
- USE_LDAP_AUTHENTICAITON = shows if the tech account is authenticating locally or via active directory. value of 0 is local authentication, value of 1 is using active directory authentication.
- LINKED_CLIENT_ID = shows the client ID of the linked client account for the technician

 

Disclaimer: 

Scripts are not supported under any SolarWinds support program or service. Scripts are provided AS IS without warranty of any kind. SolarWinds further disclaims all warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The risk arising out of the use or performance of the scripts and documentation stays with you. In no event shall SolarWinds or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the scripts or documentation.