ManageEngine SupportCenter Plus: Changing Request Starting ID

How to change the starting request ID number in ManageEngine SupportCenter Plus. For ServiceDesk Plus MSP, see ManageEngine ServiceDesk Plus MSP.

In the ManageEngine installation directory, I’ll assume C:\ManageEngine\SupportCenter

For MySQL

  1. C:\ManageEngine\SupportCenter\mysql\bin\mysql.exe -u root -P 33356 supportcenter
  2. mysql> insert into workorder (WORKORDERID,REQUESTERID,DEPARTMENTID) values (4999,2,1);
    Where request ID is 4999, Requesterid is 2 and Department ID is 1
  3. Restart SupportCenter service

For MSSQL

  1. insert into workorder (WORKORDERID,REQUESTERID,CREATEDTIME,DUEBYTIME,DEPARTMENTID) values (4999,2,0,0,1);
  2. Restart SupportCenter service

For Postgres

  1. C:\ManageEngine\SupportCenter\pgsql\bin\psql.exe -U postgres -p 65432 -d supportcenter
  2. postgres# insert into workorder (WORKORDERID,REQUESTERID,CREATEDTIME,DUEBYTIME,DEPARTMENTID) values (4999,2,0,0,1);
  3. Restart SupportCenter service

Pennsylvania COVID-19 Data Scraping with Python

Over the last few weeks I’ve been using Python to scrape the Pennsylvania Department of Health’s Coronavirus page. Over time the page has evolved and even split into sub-pages which contain table date of cases, deaths and other statistics.

I’ve decided to put my Python script on GitHub for public consumption. Initially when I had created the script, it was used to send me alerts when the reported numbers changed as there was no set time during the day that the website was updated, so I wanted to set up a cron job to check the website and alert me of new updates.

Below you’ll find the main script. Note that the code below may be out of date, so please check my GitHub repository for the latest. The code changes almost daily as it seems Pennsylvania Dept. of Health changes the structure, or adds new data, to the webpage which throws off my code.

Python Script

import pandas as pd
from bs4 import BeautifulSoup
import requests
import os
import re

url = r'https://www.health.pa.gov/topics/disease/coronavirus/Pages/Cases.aspx'
html_content = requests.get(url).text
lastupdatedfile = "lastupdated.txt"
soup = BeautifulSoup(html_content, "lxml")
stats = soup.find("span",attrs={"class": "ms-rteStyle-Quote"})
#TODO: byte / string issue here on updatecheck
updatecheck = stats.text[stats.text.find("at "):][3:]

if os.path.isfile(lastupdatedfile):
	lastupdate = open(lastupdatedfile).read()
	if lastupdate == updatecheck:
		print("Skipping check, no new update.")
		os._exit(0)
	else:
		print("***UPDATE***\nOld: {}".format(lastupdate))
		print("New: {}".format(updatecheck))

tables = pd.read_html(html_content, header=0)
df = tables[3]

totalCounties = 67

print("Pennsylvania Data ({})".format(updatecheck))

deathsTotal = int(df["Deaths"].sum())
casesTotal  = int(df["Number of Cases"].sum())
mortalityPercent = round((deathsTotal / casesTotal) * 100,2)
reportingTotal = int(df["County"].count())
reportingCases = df["Number of Cases"]
reportingCasesPct = round((reportingCases.count() / totalCounties) * 100,2)
reportingDeathsObj = df.apply(lambda x: True if x['Deaths'] > 0 else False, axis=1)
reportingDeaths = len(reportingDeathsObj[reportingDeathsObj == True].index)
reportingDeathsPct = round((reportingDeaths / reportingTotal) * 100,2)

print("Total Cases: {}".format(casesTotal))
print("Total Deaths: {}".format(deathsTotal))
print("Mortality Rate(%): {}".format(mortalityPercent))
print("Counties Reporting Cases: {}".format(reportingCases.count()))
print("Counties Reporting Cases(%): {}".format(reportingCasesPct))
print("Counties Reporting Deaths: {}".format(reportingDeaths))
print("Counties Reporting Deaths(% of counties reporting cases): {}".format(reportingDeathsPct))


f=open(lastupdatedfile,"w")
f.write(updatecheck)
f.close()
# Add some notification stuff here...

Output Example

Pennsylvania Data (12:00 p.m. on 3/28/2020)
Total Cases: 2751
Total Deaths: 34
Mortality Rate(%): 1.24
Counties Reporting Cases: 56
Counties Reporting Cases(%): 83.58
Counties Reporting Deaths: 13
Counties Reporting Deaths(% of counties reporting cases): 23.21

ManageEngine ServiceDeskPlus MSP: Change Starting Request ID (Postgres)

The procedure to change the request ID for ServiceDeskPlus MSP product (v10.5 and later) is outlined below.

Navigate to the location of ServiceDeskPlus MSP installation and change into the pgsql\bin directory in a command prompt.

C:\ManageEngine\ServiceDeskPlus-MSP\pgsql\bin> psql -U sdpadmin -p 65432 -d servicedesk -h 127.0.0.1 

Enter the password sdp@123

Queries for the respective modules:

REQUEST

insert into workorder(workorderid,requesterid,createdtime,respondedtime,duebytime,completedtime,timespentonreq,isparent,is_catalog_template,fr_duetime,haschange,hascausedbychange,hasproblem,surveystatus,hasdraft,resolvedtime) values (3000,1,0,0,0,0,0,true,false,0,false,false,false,0,false,0); 

PROBLEM

insert into problem (problemid,title,reportedtime,updatedtime,duebytime,closedtime,isread,notespresent) values(3000,’title’,0,0,0,0,false,false); 

CHANGE

insert into changedetails (changeid,title,createdtime,scheduledstarttime,scheduledendtime,completedtime,notespresent) values(3000,’title’,0,0,0,0,false); 

Use \q to exit postgresql commandline and restart the ServiceDeskPlus MSP service. On Windows this is servicedeskmsp.

Try creating a new ticket to check the ID series.

ServiceDesk Plus MSP 10.5 Postgre SQL password

The latest release of ManageEngine ServiceDesk Plus MSP changes the default user and password of the Postgres SQL database.

Previously, the command to connect to the database was:

psql -U postgres -p 65432 -d servicedesk -h 127.0.0.1

The new change shows that there is a change in username and password as follows:

psql -U sdpadmin -p 65432 -d servicedesk -h 127.0.0.1

Username: sdpadmin

Password: sdp@123