Reporting - Comments with name of Commentor
I'm trying to build a report that will pull several fields, one of which is the comment field from a queue. I have the comments for the tickets I need listed in my report, but it's hard to follow who made what comment and what timestamp it was made. Therefore, I want to have each comment in a single ticket list who made it and the associated timestamp. I also need to have these comments sorted in reverse order than it currently pulls in the report. When ticket comments are listed in the report it lists them from oldest to newest, but instead I need them to display from newest to oldest (like is shown in a ticket).
Any assistance would be greatly appreciated!
Answers (6)
I found a KACE article that has the comments sorted from newest to oldest and it also displays who made the comment and the date, but it doesn't have the full timestamp!
http://www.kace.com/support/resources/kb/article/Helpdesk-Report-Showing-Ticket-History?action=artikel&id=954
How can I get it to display the full timestamp? The timestamp of a comment looks like:
PersonName - 02/11/2013 15:42:53
Another thing I noticed is when I run the report in HTML or TXT the comments are all there, but when I run the report in CSV it has only the first line (the latest comment, which says for example, "Change by <person> on 2012-09-10) of the comments, and doesn't have the rest of the latest comment or any older comments.
How can I get all of the comments to show in CSV? This is the format that I need it in.
Change the date() function to timestamp() in the query like this
Instead of
CAST(DATE(HD_TICKET_CHANGE.TIMESTAMP) AS CHAR),' ---\n',
use
TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),' ---\n',
Comments:
-
Thank you, that really helped, now I have the dates with the full timestamp as I needed. Now, the last part I need is to have the report when ran in a CSV to list all comments, not just the first line of comments. - jwaltersnsm 11 years ago
The full solution would be this, you cant use the new line inside of a CSV so just use dashes (or change to somethign else of your liking).
SELECT
HD_TICKET.ID,
O.FULL_NAME AS OWNER_NAME,
S.FULL_NAME AS SUBMITTER_NAME, GROUP_CONCAT( CONCAT('\n-- Change by ',U.USER_NAME,' on ', CAST(TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP) AS CHAR),' ---', IF(DESCRIPTION='' OR ISNULL(DESCRIPTION),'', CONCAT('--',TRIM(TRAILING '-->' FROM REPLACE(DESCRIPTION,'\n','--')),'')), IF(HD_TICKET_CHANGE.COMMENT='','',CONCAT(HD_TICKET_CHANGE.COMMENT,' - ')) ) ORDER BY CAST( HD_TICKET_CHANGE.ID AS CHAR) DESC SEPARATOR ' - ' ) AS HD_TICKET_CHANGE_COMMENT FROM HD_TICKET JOIN HD_CATEGORY ON (HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID) JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID) LEFT JOIN USER O ON (O.ID = HD_TICKET.OWNER_ID) LEFT JOIN USER U ON USER_ID=U.ID LEFT JOIN USER S ON S.ID=HD_TICKET.SUBMITTER_ID WHERE HD_TICKET_CHANGE.OWNERS_ONLY=0 GROUP BY HD_TICKET_ID ORDER BY HD_TICKET.ID DESC
Comments:
-
Ok, so I took the query you posted and pasted it right into a report to see what I get. It's only partially working. The comments for tickets are mangled, and it only lists maybe the first couple lines. Also, occasionally there's some tickets that appear to have all of the comments but it's on multiple lines, like over 300 lines because they have a lot in the comments. How might I get these comments regardless of their size to go onto one line? Each comment on a ticket separated by a dash is fine. - jwaltersnsm 11 years ago
-
Im afraid Ill need more info like maybe a screenshot. Are we still talking about the CSV file? Im not seeing this behavior in my CSV but its also a test environment without huge tickets. At some point I imagine you will run up against a field size limitation.
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP005199291.aspx - jdornan 11 years ago-
There's a screenshot posted if you've not seen it already. Yes, it's still about the CSV file. - jwaltersnsm 11 years ago
Here's a screenshot of the CSV. The really long line is one of the comments for a ticket, but it's cut off at the end.
Here's a closeup screenshot of the same one above, but only showing a portion of the really long line. Note that the columns are as follows: first column is Excel cell numbers, 2nd one is the numbered column, 3rd column is ticket number, 4th column is ticket owner, 5th column is ticket submitter, and 6th column is comments in a ticket.
The ticket number 12761 is the one that has screwed up comments, notice how it says, "--Change", then that's it, or so it appears, but the comments actually continue on in cell numbers 578 through 1388. (FYI the ticket 12761 is involving an Exchange sync error, nothing to do with Excel or this issue with the CSV.) This is very weird why the comments continue on for this one ticket, yet you can see above tickets of number 12770 through 12762 have their comments on one cell, albeit cut off and not showing all of it either.
Per this link http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx there's a limit of 32,767 characters allowed for one cell in Excel 2007 (version I have), and I seriously doubt I have that many characters in any of my tickets' comments. If I had 4000 characters on a single-spaced page font size 11 that would be a little over eight pages of text, and that's with no spaces between letters. I think I should be able to stick all of my comments from a ticket, typically one to two pages worth of text, in a single cell. The occasional comments for a ticket are larger, and a couple might exceed eight pages (extreme issues).
Comments:
-
Id have to see the raw contents of the ticket, I imagine you have some special character in there that excel is reading as a break or new line causing the issue that you'll need to strip out with format changes - jdornan 11 years ago
-
I'd be happy to show you the raw contents of the ticket, how can I get with you to do this? - jwaltersnsm 11 years ago
Read the link again.
Only 1,024 display in a cell; all 32,767 display in the formula bar.
Yes, I did read that, and understood that the cell will visually display only 1,024, but I checked in the formula bar and it's cut off as well. Here's an example:
Here's a closeup of the end of the line that's cut off at 255 characters including spaces, I verified this by using word count in MS Word (see below).
Here's the full line of text that's displayed along with the word count:
