Create custom report for service ticket using keywords located in the summary or comments sections
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.
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,
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'