Passer au contenu principal

Displaying ticket messages in a report - Base de connaissances / Developer & Reporting / DPQL Examples - Deskpro Support

Displaying ticket messages in a report

Liste des auteurs

Question:

I want to create a report that includes the first message on a ticket. I've tried using 'SELECT tickets_messages.message', but that doesn't work. How can I include message data in my report?

 

 

Answer:

The issue here is that you can only reference one table in your query's FROM clause. So if you are selecting fields from the 'tickets' table (e.g. your SELECT statement includes 'tickets.id', 'tickets.subject' etc.), you can't also select from 'tickets_messages'.

Instead, you have to use table cross-referencing to get message content using:

SELECT tickets.messages.message

 

 

One ticket can have many messages, so doing this will return multiple rows for each ticket that has more than message. To fix this, you can use 'GROUP BY tickets.id' so that only one row is displayed per ticket (containing the first message on the ticket).

 

Here's an example query that combines 'tickets' and 'tickets_messages'  content:

 

SELECT tickets.date_created, tickets.subject,  tickets.messages.message, tickets.departmentFROM ticketsWHERE tickets.date_created = %LAST_WEEK%SPLIT BY tickets.departmentGROUP BY tickets.id

 

Note that the message content often includes HTML formatting added by the user's email client, so it's not always easy to read. To remove this, you can export the results as CSV and use your spreadsheet program's "find and replace" function combined with wildcards/regular expressions to strip HTML tags & character entities like ' '.

Utile Peu Utile

65 personnes sur 125 ont trouvé cette page utile

Ajouter un commentaire

Veuillez vous connecter ou vous enregistrer pour soumettre un commentaire.

Mot de passe oublié ?