/bundles/itninjaweb/img/Breadcrumb_cap_w.png

Hello everyone,

I am trying to add the assest and the machines fields into my email. However, i cannot find the value in order to do so.

Here is my query :

        SELECT
          -- ticket fields
          HD_TICKET.ID, -- $id
          HD_TICKET.ID AS TICKNUM, -- $ticknum
          HD_TICKET.TITLE, -- $title
          DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
          DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
        
          -- change fields
          C.COMMENT, -- $comment
          C.DESCRIPTION, -- $description
          GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
           H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
           ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
        
        
          -- about the updater
          UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
          UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
          UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
          IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
        
          -- about the owner
          OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
          OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
          OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
          IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
        
          -- about the submitter
          SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
          SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
          SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
          -- about priority
          P.NAME AS PRIORITY, -- $priority
        
          -- about status
          S.NAME AS STATUS,   -- $status
        
          -- about impact
          I.NAME AS IMPACT,   -- $impact
        
          -- about category
          CAT.NAME AS CATEGORY, -- $category
        
          -- other fields
          -- -- example of static distribution list
          'user@domain.com' AS NEWTICKETEMAIL -- $newticketemail
        
        
        FROM HD_TICKET
         /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                                AND C.ID=<CHANGE_ID>
         /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
         /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
         /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
         /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
         /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
         /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
         /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
         /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
        WHERE
         C.DESCRIPTION LIKE 'TICKET CREATED%'
        
          /* this is necessary when using group by functions */
        GROUP BY HD_TICKET.ID
        HAVING 1=1

Here is the email :

$submitter_fname has opened ticket$ticknum.

----------------------------------------------------------------------------
Ticket: http://kbox.qsl.com/userui/ticket?ID=$ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact

----------------------------------------------------------------------------
Title: $title
----------------------------------------------------------------------------

Ticket Comment:

$comment

----------------------------------------------------------------------------

What i would like to do is have the fields $assets and $machine.

What should i do to do it ?

Thanks!

0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer this question or Comment on this question for clarity

Answers

0

That's a well documented SQL query!

You'll need to add some joins based on the machine selected in the ticket. I can't access the database right now to check the column names, but one of them should be something like HD_TICKET.ASSET_ID. That asset id can be used to make your join to the assets database. Do you have a tool like MySQL Workbench? If not then I would highly recommend that you install it and check out the table structures. 

Answered 07/20/2012 by: chucksteel
Red Belt

  • Haha i found it on Kace's website and added the fluff that i needed.

    If i had access to the DB i would be able to figure out the column names. Unfortunately, i don't. Is there another way i could find them?

    The K1000 workbench creates a lot of pointless garbage and it makes it hard to figure out what is what.
  • I use a program called MySQL Workbench to access the database. It connects using the reporting username and password that you can setup in the Settings.
Please log in to comment

Share