KACE Product Support

Your select query will look something like this:


SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%Label Name%'


Change Label Name accordingly.

Check the box that says "Results are tickets, add a comment to each one." This is where you'll enter "This is a Windows Update test workstation."

Your update query would then look like this:


update HD_TICKET as T

set T.CC = 'user1@domain.com, user2@domain.com, user3@domain.com'
where
(T.ID in (<TICKET_IDS>))


Enter a comma delimited list of email addresses for the CC field.

Set the ticket rule to run On Ticket Save and save it.

Whoops! Almost forgot... this needs to be added to the Select query's WHERE clause, otherwise the ticket will have the comment added and CCs modified everytime ANY ticket is saved. This addition to the WHERE clause will only consider it a "new" ticket if it doesn't already have "This is a Windows Update test workstation." in the comments.


SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%Label Name%' AND T.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a Windows Update test workstation.%')


It was never explicitly stated (or I missed it) but this rule should have a frequency of "on ticket save" so that it is evaluated every time it is updated.

Here is a rewrite for Andy's query that adds two things:

• it will peform faster (noticeable on large helpdesks)
• it will protect you if you accidentally hit the "run now" button.
• will only change the ticket based on actions of the most recent save when a relevant change occurs
  


SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C ON HD_TICKET_ID=T.ID and C.ID=<CHANGE_ID>
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%Label Name%'
AND COMMENT like '%This is a Windows Update test workstation.%'


The update could also be like this to avoid removing what is already there. You could also make it more complex to avoid duplicates.

update HD_TICKET as T
set T.CC_LIST = CONCAT(CC_LIST,'user1@domain.com, user2@domain.com, user3@domain.com')
where
(T.ID in (<TICKET_IDS>))


Note: I have not tested this as written

Airwolf, Gillyspy, thanks for the swift reply!

I've got it set up like this:

Select Query:
[font="Courier New"]SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN HD_TICKET_CHANGE C ON HD_TICKET_ID=T.ID and C.ID=<CHANGE_ID>
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%'
AND COMMENT like '%This is a test computer for workstation updates.%'

Results are tickets, add a comment to each one:
[font="Courier New"]This is a test computer for workstation updates.
(Owners Only is checked)

Update Query:
[font="Courier New"]update HD_TICKET as T
set T.CC_LIST = CONCAT(CC_LIST,'me@domain.org')
where
(T.ID in (<TICKET_IDS>))

Tested making a ticket about a machine that's in the specified label, and I'm not having any success. Any suggestions?

Gerald, your modification is only going to show instances where the Comment already exists...

OK so here's what I've got then, copying Airwolf's stuff basically directly:

Select Query:
[font="Courier New"]SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND T.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%')

Comment:
[font="Courier New"]This is a test computer for workstation updates.

Update Query:
[font="Courier New"]update HD_TICKET as T

set T.CC = 'me@domain.org'
where
(T.ID in (<TICKET_IDS>))

However still no success. What am I doing wrong?

Your label is TICKET TEST and a ticket has a machine selected that is in that label? You have to save the ticket before the changes will take effect.

Gerald's example for the Update query is better, because it will add the admins to the list of CCs instead of replacing the list.

Update Query:
update HD_TICKET as T
set T.CC_LIST = CONCAT(CC_LIST,'me@domain.org')
where
(T.ID in (<TICKET_IDS>))

Well right, obviously the On Ticket Save action only happens when the ticket is saved.
However, I make a change to a ticket w/ a machine in that TICKET TEST label, and nothing happens. the ticket isn't updated and no one is added to the CC list.

Here's what's in the log for the rule:

[font="Courier New"]49:25> Starting: Wed, 05 Jan 2011 11:49:25 -0800
49:25> Executing Select Query...
49:25> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'where clause'] in EXECUTE("SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND T.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%') and (HD_TICKET.ID = 37409) ")

Why is "and (HD_TICKET.ID = 37409)" in your SELECT query?

Hm, it's not -- here's the select query as entered in the rule:

[font="Courier New"]SELECT T.ID AS 'ID' FROM HD_TICKET T
JOIN MACHINE M ON (T.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND T.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%')

That HD_TICKET.ID = 37409 doesn't get appended until a ticket is saved and the rule is run against it - then that shows up in the log, as though the kbox is appending that as it runs the rule against the saved ticket. That's the number of the ticket that was saved.

That's how the update query should act when <TICKET_IDS> is used (it returns an array of all tickets found by the Select Query)... It could have something to do with the "Results are tickets, add a comment" checkbox. Try this instead:

SELECT ID FROM HD_TICKET
JOIN MACHINE M ON (HD_TICKET.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND HD_TICKET.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%')

OK, so with your suggested change, here's what I get in the log:

[font="Courier New"]38:32> Starting: Wed, 05 Jan 2011 12:38:32 -0800
38:32> Executing Select Query...
38:32> mysql error: [1052: Column 'ID' in field list is ambiguous] in EXECUTE("SELECT ID FROM HD_TICKET
JOIN MACHINE M ON (HD_TICKET.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND HD_TICKET.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%') and (HD_TICKET.ID = 37409) ")

Bah! Sorry, I forgot to specify the table for ID.

SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN MACHINE M ON (HD_TICKET.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND HD_TICKET.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%')

And so....

[font="Courier New"]42:23> Starting: Wed, 05 Jan 2011 12:42:23 -0800
42:23> Executing Select Query...
42:23> selected 1 rows
42:23> Adding ticket comments...
42:23> updated 1 tickets
42:23> Executing Update Query...
42:23> updated 1 rows
42:23> Ending: Wed, 05 Jan 2011 12:42:23 -0800

THANK YOU THANK YOU THANK YOU Airwolf! Excellent SQL wrangling - May angels sing your name from the heavens!

So, posted for future generations, here is the rule in its entirety...

Select Query:
[font="Courier New"]SELECT HD_TICKET.ID AS ID FROM HD_TICKET
JOIN MACHINE M ON (HD_TICKET.MACHINE_ID = M.ID)
JOIN MACHINE_LABEL_JT MLJT ON (MLJT.MACHINE_ID = M.ID)
JOIN LABEL L ON (MLJT.LABEL_ID = L.ID)
WHERE L.NAME like '%TICKET TEST%' AND HD_TICKET.ID NOT IN (SELECT HD_TICKET_ID FROM HD_TICKET_CHANGE WHERE COMMENT like '%This is a test computer for workstation updates.%')

Comment:
[font="Courier New"]This is a test computer for workstation updates.

Update Query:
[font="Courier New"]update HD_TICKET as T
set T.CC_LIST = CONCAT(CC_LIST,'me@domain.org')
where
(T.ID in (<TICKET_IDS>))