KACE Product Support

You could use a ticket on save rule in order to do this. You'll probably want the information stored in HD_TICKET.HD_CATEGORY_ID (performing a join in the sql query to the table HD_CATEGORY to get the name instead of just a number) and doing the same for whatever field you've got your sub category saved in. Then append this information to a ticket via an update query. You'll also need to join the tables HD_TICKET_CHANGE and HD_TICKET on the ticket ID fields.

It shouldnt be terribly difficult but it will deffinately be a custom query and not one that would be easilly written with the built in editor. My suggestion is to download FlySpeed SQL Query (freebie) and connect to your database and have a squiz around with it.

I now have a connection to the MySQL db but am not an expert on joins and such... It's a little confusing as to what I am actually looking for. I have reviewed the tables in HD_CATEGORY and HD_TICKET and I have not a good enough grasp to understand how to pull what I am looking for.

Any further ideas would be very welcome.

--james

Did you use the rules wizard to create your rule that sends the email to the helpdesk? If so, then it should include a select statement that has a name of CATEGORY_NAME which translates to the $category_name variable for the email.

If your select statement doesn't already have it, then the table.column is HD_CATEGORY.NAME. The wizard doesn't use a join, but opts to include the HD_CATEGORY in the from portion of the select statement, and then includes HD_CATEGORY.ID = HD_CATEGORY_ID in the where clause.

The join, however would be:
LEFT JOIN HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
(that's untested but it should work).

Hi.

Thanks for the suggestion. I am still getting that error from MySQL though. If I remove the left join, no error occurs. Of course then I don't receive the category email - either case.

Rats!

--james

jbowes,

What is the error message that you're getting? Is it about the not liking the <CHANGE_ID> part? If so this is correct. You will receive this message and should be getting it. There is a number which the KACE box substitutes into there as the ticket is being saved.

I would normally try and keep away from the variables which the kbox uses, if you're setting this as an 'On Save' ticket rule then I dont really see the need to try and sift through all the tickets looking for a particular change ID, so long as your searching for the Like 'Ticket Created%' which I think is easier to do.

If you know a particular change ID on a ticket you can substitute <CHANGE_ID> for 'thenumber'.

Let us know how you go mate,
Cheers,
Col

Hi.

Okay I have the flyspeed SQL tool but am not able to connect to the MySQL database. I am quite familiar with MySQL but I have always had local admin access. What credentials do I use to connect to the database?

Thanks for your help.

Hi.

No I didn't use a wizard - I actually followed an example from this forum for sending an email when there is a new ticket. Ideally, I'd like to add the CATEGORY information to the new ticket email. I think it is possible but I will have to play around...

Thanks for your suggestion which I will gladly try.

--james

Well I tried that but I have an error within the query which states that:

mysql error: [1066: Not unique table/alias: 'HD_CATEGORY'] in EXECUTE

I have bold faced the lines I added. Thanks for the help!

select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
HD_CATEGORY.NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
'helpdesk@company.com' as NEWTICKETEMAIL,
UNIX_TIMESTAMP(HD_TICKET_CHANGE.TIMESTAMP),
COMMENT
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID=<CHANGE_ID>
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
left join HD_CATEGORY on HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'

If you include the HD_CATEGORY table in the FROM statement, then I don't believe that you need the join as well. The HD_CATEGORY.ID = HD_CATEGORY_ID line in the where clause takes care of that. You should probably amend the select of the HD_CATEGORY.NAME to include an AS to avoid causing any duplicate column names, so:
HD_CATEGORY.NAME as CATEGORY_NAME

Well this is indeed nuts! I am using the mysql workbench query tool to test things out but I seem to get an error with the syntax of the query just the way it was - before adding anything to it.

This tool doesn't like the following line:

JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_CHANGE.ID = <CHANGE_ID>

The error is "Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' at line 27" which the CHANGE_ID line.

Not a MySQL expert but that doesn't look good to me...

--james

The <CHANGE_ID> is a variable that Kace supplies when the rule is running inside of Kace. When using another tool you need to supply another change id value from an existing ticket.

Steelc is right on the ball, the <CHANGE_ID> is supplied by the Kbox and will not work in any normal sql query.

Just having a look over this again - If I'm correct in what you're wanting to do then you're wanting to add the category and status to the email so that the support staff dont need to log onto the KBOX. This sounds a little counter productive now that I look at it again, but to each their own I spose.

If you create a ticket rule the same as you have above and change line 11 to:

HD_CATEGORY.NAME as TickCategory,

You should also be able do this for multiple different selections, ie, HD_STATUS.NAME as TickStatus, HD_TICKET.CustomField1 as Cust1

Then you'll be able to use the variable $TickCategory in the email response to your IT staff this should sort things out for you (we've used it in the past and had mixed success). This way you should be able to avoid having an outrageous update query. See attached picture for a test configuration I've used in the past. Let us all know how you go.

Cheers
Col

On an "on ticket save" rule the <CHANGE_ID> is the ID of the change record so you can zero in on the latest comment. Also in an "on ticket save" is an implied addition to your query -- it adds and HD_TICKET.ID =123 where 123 is the ticket that was saved.

This makes it a bit difficult to test, but you can get the value of <CHANGE_ID> by doing this:
If you are only interested in it firing upon ticket creation then you could remove the and HD_TICKET_CHANGE.ID=<CHANGE_ID> and just go with the HD_TICKET_CHANGE.DESCRIPTION LIKE 'Ticket Created%' .

Steelc is right that your left join on HD_CATEGORY is redundant. So remove that as well.

So your query now James should be:
select HD_TICKET.ID, /* ... other columns you want */
HD_CATEGORY.NAME
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
left join USER U1 on U1.ID = HD_TICKET.OWNER_ID
left join USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
left join USER U3 on U3.ID = HD_TICKET_CHANGE.USER_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID and
HD_STATUS.ID = HD_STATUS_ID and
HD_IMPACT.ID = HD_IMPACT_ID and
HD_CATEGORY.ID = HD_CATEGORY_ID and
HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%'
/* and HD_TICKET.ID = 123 gets added behind the scenes on a OTS rule */