Create custom report for service ticket using keywords located in the summary or comments sections
Hey all,
We are trying to get a report done where it shows a specific keyword or words either in the "Summary" or "Comments" section of a ticket in the service desk.
Anyone help me with that? I am not a SQL guy, but get the idea of it. The guy that was our KACE expert got another job and I am have put in charge of KACE now.
Thank you,
Dan
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
5 years ago
This query will return tickets that were created within the past month with the keywords listed for each column listed.
SELECT T.ID, T.TITLE, T.RESOLUTION, T.SUMMARY,
GROUP_CONCAT(C.COMMENT) as COMMENTS,
CREATED
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.ID = T.ID
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)
GROUP BY T.ID
HAVING COMMENTS REGEXP 'malware|virus|combofix|mbam|ccleaner'
or T.RESOLUTION REGEXP 'malware|virus|combofix|mbam|ccleaner'
or T.TITLE REGEXP 'malware|virus|combofix|mbam|ccleaner'
or T.SUMMARY REGEXP 'malware|virus|combofix|mbam|ccleaner'
Comments:
-
Thanks, Chucksteel. If i want ALL tickets, do I just leave out this code?
WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH) - dhug 5 years ago-
Also, to be more specific, we are having issues with Dell Active Pens (which are a stylus) and want to send Dell a report of all tickets we've created on them.
So looking to do a search in comments or summary that look for "pen" OR "stylus." - dhug 5 years ago-
To answer both questions:
Yes, removing the WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH) statement will show results for all tickets.
To search just the comments or summary for pen or stylus remove the last four lines and replace with:
HAVING COMMENTS REGEXP 'pen|stylus'
or T.SUMMARY REGEXP 'pen|stylus'
You can also adjust the columns returned on the report by adding or removing from this portion:
SELECT T.ID, T.TITLE, T.RESOLUTION, T.SUMMARY,
GROUP_CONCAT(C.COMMENT) as COMMENTS,
CREATED - chucksteel 5 years ago
-
This is the SQL code I am putting in and for some reason I am getting only one result from the report and it has nothing to do with "pen" or "stylus."
SELECT T.ID, T.TITLE, T.RESOLUTION, T.SUMMARY,
GROUP_CONCAT(C.COMMENT) as COMMENTS,
CREATED
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.ID = T.ID
GROUP BY T.ID
HAVING COMMENTS REGEXP 'pen|stylus'
or T.SUMMARY REGEXP 'pen|stylus' - dhug 5 years ago-
Am I doing something wrong? - dhug 5 years ago
-
The regular expression searches were matching the patterns inside of other words (e.g. why is this hapPENning?).
Changing them to:
HAVING COMMENTS REGEXP 'pen\w|stylus\w'
or T.SUMMARY REGEXP 'pen\w|stylus\w'
makes it more specific.
I'm not sure why you're only getting one result. - chucksteel 5 years ago-
SELECT T.ID, T.TITLE, T.RESOLUTION, T.SUMMARY,
GROUP_CONCAT(C.COMMENT) as COMMENTS,
CREATED
FROM ORG1.HD_TICKET T
JOIN HD_TICKET_CHANGE C on C.ID = T.ID
GROUP BY T.ID
HAVING COMMENTS REGEXP 'pen\w|stylus\w'
or T.SUMMARY REGEXP 'pen\w|stylus\w'
Tried this. Now getting no results. Guessing we have something different in our ticket system than you? - dhug 5 years ago
