/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Create custom report for service ticket using keywords located in the summary or comments sections

03/21/2019 365 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?

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ