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!
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!
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.
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).
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
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).
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: