/build/static/layout/Breadcrumb_cap_w.png
03/21/2019 252 views

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

Comments


All Answers

0

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'


Answered 03/22/2019 by: chucksteel
Red Belt

  • Thanks, Chucksteel. If i want ALL tickets, do I just leave out this code?
    WHERE T.CREATED > DATE_SUB(NOW(), INTERVAL 1 MONTH)
    • 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."
      • 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
  • 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'
    • Am I doing something wrong?
    • 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.
      • 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?