So, i'm new to osTicket after several other open source solutions, i figured we'd migrate over to the latest osTicket running in a docker stack...
After setting up, testing, etc it became apparent that there was no way to "backdate" ticket dates according to email dates if you want to import several thousand old tickets from an IMAP/POP mailbox (just so we have a reference of them in our system associated with each customer)...
After playing around, I've come up with a quick solution that you can add to your DB as a stored procedure (and run once-off or on a schedule if required) allowing you to set all your ticket & thread dates to that of their email header.
Prerequisites:
- You'll need to have MariaDB 10.X running (fully compatible with latest osTicket 1.15.x) as MySQL does not PCRE Regex...
- Your table prefix will need to begin with
ost_
(this is the application default)
- Backup your DB with MySQL dump etc before running this...
DELIMITER $$
DROP PROCEDURE IF EXISTS parseDatesFromImapEmailHeaders$$
CREATE PROCEDURE parseDatesFromImapEmailHeaders()
BEGIN
ALTER TABLE ost_ticket ADD COLUMN IF NOT EXISTS imap_dates_parsed INT DEFAULT 0;
DROP TEMPORARY TABLE IF EXISTS imapTicketDates;
-- Determine original dates (if possible - otherwise fallback to default ticket dates in DB) for tickets received via email (from email headers)...
-- We're creating a temp 'staging' table to insert this data for the duration of the batch ....
CREATE TEMPORARY TABLE imapTicketDates
SELECT
ost_ticket.ticket_id,
ost_thread_entry.id thread_id,
ost_thread_entry_email.thread_entry_id,
IFNULL(
STR_TO_DATE(
REPLACE(
REGEXP_SUBSTR(
ost_thread_entry_email.headers,
'Date:(.*)\\K(Sun|Mon|Tue|Wed|Thu|Fri|Sat)(.*)([0-2][0-9]:[0-5][0-9]:[0-5][0-9])'
), '\r\n', ''
),
"%a, %e %b %Y %k:%i:%s"
),
IFNULL(
STR_TO_DATE(
REPLACE(
REGEXP_SUBSTR(
ost_thread_entry_email.headers,
'Delivery-date:(\\s*\\K(Sun|Mon|Tue|Wed|Thu|Fri|Sat),\\s*(\\d+\\s+\\w+\\s+\\d+)\\s+[0-2][0-9]:[0-5][0-9]:[0-5][0-9]\\s)'
), '\r\n', ''
),
"%a, %e %b %Y %k:%i:%s"
),
IFNULL(
STR_TO_DATE(
REPLACE(
REGEXP_SUBSTR(
ost_thread_entry_email.headers,
'Received: by (.*);(\s*\K(Sun|Mon|Tue|Wed|Thu|Fri|Sat),\s*(\d+\s+\w+\s+\d+)\s+[0-2][0-9]:[0-5][0-9]:[0-5][0-9]\s)'
), '\r\n', ''
),
"%a, %e %b %Y %k:%i:%s"
),
IFNULL(
STR_TO_DATE(
REPLACE(
REGEXP_SUBSTR(
ost_thread_entry_email.headers,
'Date:\\s\\K(.*)(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(.*)([0-2][0-9]:[0-5][0-9]:[0-5][0-9])'
), '\r\n', ''
),
"%e %b %Y %k:%i:%s"
),
ost_ticket.created
)
)
)
) AS new_thread_date
FROM
ost_ticket
INNER JOIN
ost_thread_entry ON ost_thread_entry.thread_id = ost_ticket.ticket_id
INNER JOIN
ost_thread_entry_email ON ost_thread_entry_email.thread_entry_id = ost_thread_entry.id
WHERE
imap_dates_parsed = 0;
-- SELECT * from imapTicketDates;
-- Update ticket dates based on data in staging table...
UPDATE
ost_ticket
INNER JOIN imapTicketDates USING (ticket_id)
SET
ost_ticket.created = imapTicketDates.new_thread_date,
ost_ticket.updated = imapTicketDates.new_thread_date,
ost_ticket.lastupdate = imapTicketDates.new_thread_date,
ost_ticket.imap_dates_parsed = 1
WHERE
ost_ticket.ticket_id = imapTicketDates.ticket_id;
-- Update ticket individual thread / chat dates...
UPDATE
ost_thread_entry
INNER JOIN imapTicketDates ON (ost_thread_entry.id = imapTicketDates.thread_id)
SET
ost_thread_entry.created = imapTicketDates.new_thread_date;
-- Update 'ost thread' (last updated etc) metadata table...
UPDATE
ost_thread
INNER JOIN imapTicketDates ON (ost_thread.id = imapTicketDates.ticket_id)
SET
ost_thread.created = imapTicketDates.new_thread_date,
ost_thread.lastmessage = imapTicketDates.new_thread_date;
-- Update 'thread event' the log for when items are triggered, created, updated etc...
UPDATE
ost_thread_event
INNER JOIN imapTicketDates ON (ost_thread_event.thread_id = imapTicketDates.ticket_id)
SET
ost_thread_event.timestamp = imapTicketDates.new_thread_date;
DROP TEMPORARY TABLE imapTicketDates;
END$$
DELIMITER ;
You may run at any time using the SQL function: CALL parseDatesFromImapEmailHeaders();