Tools
Tickets from older WHD version not accessible through Client’s UI
In WHD 12.8.3, existing tickets are inaccessible on the Client’s UI.
First published date
7/22/2024 8:39 PM
Last published date
2/8/2025 3:21 AM
Overview
Troubleshooting steps to resolve issue related to older tickets in WHD 12.8.3. not being accessible on the Client's UI. Includes scripts for MS-SQL, MySQL, and PostgreSQL.
Product section
Web Help Desk
Cause
In WHD 12.8.3, SolarWinds added a column “due_date” in the JOB_TICKET table. This change results in “due_date” being null for existing tickets, making them inaccessible on the Client’s UI and causing an error message to be displayed.
Error message:
Internal Server Error Something went wrong, please try again.
Resolution
Workaround 1: Once the affected ticket has been opened and saved from the Tech UI, it can be accessed from the Client UI.
Workaround 2: To quickly fix this issue from the backend, you can use the attached SQL query. that should resolve all affected Tickets from seeing this issue.
Once either of the workarounds are applied the older ticket will be accessible through the Client’s UI.
Workaround 2: To quickly fix this issue from the backend, you can use the attached SQL query. that should resolve all affected Tickets from seeing this issue.
MS-SQL Script:
WITH final_due_time AS (
SELECT
jt.job_ticket_id AS ticket_id,
jt.report_date,
pt.due_time,
pt.due_time_unit,
CASE pt.due_time_unit
WHEN 1 THEN DATEADD(HOUR, pt.due_time, jt.report_date)
WHEN 2 THEN DATEADD(DAY, pt.due_time, jt.report_date)
WHEN 3 THEN DATEADD(WEEK, pt.due_time, jt.report_date)
WHEN 4 THEN DATEADD(MONTH, pt.due_time, jt.report_date)
WHEN 5 THEN DATEADD(MINUTE, pt.due_time, jt.report_date)
WHEN 6 THEN DATEADD(YEAR, pt.due_time, jt.report_date)
ELSE COALESCE(jt.last_updated, jt.report_date)
END AS due_date_cal
FROM job_ticket jt
JOIN priority_type pt ON jt.priority_type_id = pt.priority_type_id
WHERE jt.due_date IS NULL
)
UPDATE jt
SET jt.due_date = fd.due_date_cal
FROM job_ticket jt
JOIN final_due_time fd ON jt.job_ticket_id = fd.ticket_id;
MySQL Script:
UPDATE job_ticket jt
JOIN (
SELECT
jt.job_ticket_id AS ticket_id,
jt.report_date,
pt.due_time,
pt.due_time_unit,
CASE pt.due_time_unit
WHEN 1 THEN jt.report_date + INTERVAL pt.due_time HOUR
WHEN 2 THEN jt.report_date + INTERVAL pt.due_time DAY
WHEN 3 THEN jt.report_date + INTERVAL pt.due_time WEEK
WHEN 4 THEN jt.report_date + INTERVAL pt.due_time MONTH
WHEN 5 THEN jt.report_date + INTERVAL pt.due_time MINUTE
WHEN 6 THEN jt.report_date + INTERVAL pt.due_time YEAR
ELSE COALESCE(jt.last_updated, jt.report_date)
END AS due_date_cal
FROM job_ticket jt
JOIN priority_type pt ON jt.priority_type_id = pt.priority_type_id
WHERE jt.due_date IS NULL
) AS fd ON jt.job_ticket_id = fd.ticket_id
SET jt.due_date = fd.due_date_cal;
PostgreSQL Script:
WITH final_due_time AS (
SELECT jt.job_ticket_id as ticket_id,
jt.report_date,
pt.due_time,
pt.due_time_unit,
CASE pt.due_time_unit
WHEN 1 THEN jt.report_date + (pt.due_time * INTERVAL '1 hour')
WHEN 2 THEN jt.report_date + (pt.due_time * INTERVAL '1 day')
WHEN 3 THEN jt.report_date + (pt.due_time * INTERVAL '1 week')
WHEN 4 THEN jt.report_date + (pt.due_time * INTERVAL '1 month')
WHEN 5 THEN jt.report_date + (pt.due_time * INTERVAL '1 minute')
WHEN 6 THEN jt.report_date + (pt.due_time * INTERVAL '1 year')
ELSE coalesce(jt.last_updated,jt.report_date) -- Default case if unit is not recognized
END AS due_date_cal
FROM
job_ticket jt
JOIN priority_type pt ON jt.priority_type_id = pt.priority_type_id
WHERE jt.due_date is null
)
Update job_ticket
SET due_Date = fd.due_date_cal
from final_due_time fd
where job_ticket_id = fd.ticket_id
WITH final_due_time AS (
SELECT
jt.job_ticket_id AS ticket_id,
jt.report_date,
pt.due_time,
pt.due_time_unit,
CASE pt.due_time_unit
WHEN 1 THEN DATEADD(HOUR, pt.due_time, jt.report_date)
WHEN 2 THEN DATEADD(DAY, pt.due_time, jt.report_date)
WHEN 3 THEN DATEADD(WEEK, pt.due_time, jt.report_date)
WHEN 4 THEN DATEADD(MONTH, pt.due_time, jt.report_date)
WHEN 5 THEN DATEADD(MINUTE, pt.due_time, jt.report_date)
WHEN 6 THEN DATEADD(YEAR, pt.due_time, jt.report_date)
ELSE COALESCE(jt.last_updated, jt.report_date)
END AS due_date_cal
FROM job_ticket jt
JOIN priority_type pt ON jt.priority_type_id = pt.priority_type_id
WHERE jt.due_date IS NULL
)
UPDATE jt
SET jt.due_date = fd.due_date_cal
FROM job_ticket jt
JOIN final_due_time fd ON jt.job_ticket_id = fd.ticket_id;
MySQL Script:
UPDATE job_ticket jt
JOIN (
SELECT
jt.job_ticket_id AS ticket_id,
jt.report_date,
pt.due_time,
pt.due_time_unit,
CASE pt.due_time_unit
WHEN 1 THEN jt.report_date + INTERVAL pt.due_time HOUR
WHEN 2 THEN jt.report_date + INTERVAL pt.due_time DAY
WHEN 3 THEN jt.report_date + INTERVAL pt.due_time WEEK
WHEN 4 THEN jt.report_date + INTERVAL pt.due_time MONTH
WHEN 5 THEN jt.report_date + INTERVAL pt.due_time MINUTE
WHEN 6 THEN jt.report_date + INTERVAL pt.due_time YEAR
ELSE COALESCE(jt.last_updated, jt.report_date)
END AS due_date_cal
FROM job_ticket jt
JOIN priority_type pt ON jt.priority_type_id = pt.priority_type_id
WHERE jt.due_date IS NULL
) AS fd ON jt.job_ticket_id = fd.ticket_id
SET jt.due_date = fd.due_date_cal;
PostgreSQL Script:
WITH final_due_time AS (
SELECT jt.job_ticket_id as ticket_id,
jt.report_date,
pt.due_time,
pt.due_time_unit,
CASE pt.due_time_unit
WHEN 1 THEN jt.report_date + (pt.due_time * INTERVAL '1 hour')
WHEN 2 THEN jt.report_date + (pt.due_time * INTERVAL '1 day')
WHEN 3 THEN jt.report_date + (pt.due_time * INTERVAL '1 week')
WHEN 4 THEN jt.report_date + (pt.due_time * INTERVAL '1 month')
WHEN 5 THEN jt.report_date + (pt.due_time * INTERVAL '1 minute')
WHEN 6 THEN jt.report_date + (pt.due_time * INTERVAL '1 year')
ELSE coalesce(jt.last_updated,jt.report_date) -- Default case if unit is not recognized
END AS due_date_cal
FROM
job_ticket jt
JOIN priority_type pt ON jt.priority_type_id = pt.priority_type_id
WHERE jt.due_date is null
)
Update job_ticket
SET due_Date = fd.due_date_cal
from final_due_time fd
where job_ticket_id = fd.ticket_id
Once either of the workarounds are applied the older ticket will be accessible through the Client’s UI.