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   [ + ] Show Comments

Comments

Please log in to comment

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
Answer this question or Comment on this question for clarity