Hello everyone, long time reader first time poster here.
I'm trying to make a ticket rule that will append a line to a ticket and CC some specific people when the machine the ticket is entered for belongs to a certain label.
It has to do with our Windows Updates - basically when a computer in our "Windows Updates - Testing Group" label has a ticket submitted for it, I'd like to add "This is a windows update test workstation" and CC the admin team so we can take a look to see if the problem is related to the machine having a new patch applied to it.
It seems like this should be an easy thing to do, but I'm fairly SQL-ignorant... any help would be much appreciated!
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Rating comments in this legacy AppDeploy message board thread won't reorder them,
so that the conversation will remain readable.

Answers

0
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.
Answered 01/04/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.%')
Answered 01/04/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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
Answered 01/04/2011 by: GillySpy
Seventh Degree Black Belt

Please log in to comment
0
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?
Answered 01/04/2011 by: cwoody0
Senior Yellow Belt

Please log in to comment
0
Gerald, your modification is only going to show instances where the Comment already exists...
Answered 01/04/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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?
Answered 01/04/2011 by: cwoody0
Senior Yellow Belt

Please log in to comment
0
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.
Answered 01/04/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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>))
Answered 01/04/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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) ")
Answered 01/05/2011 by: cwoody0
Senior Yellow Belt

Please log in to comment
0
Why is "and (HD_TICKET.ID = 37409)" in your SELECT query?
Answered 01/05/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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.
Answered 01/05/2011 by: cwoody0
Senior Yellow Belt

Please log in to comment
0
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.%')
Answered 01/05/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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) ")
Answered 01/05/2011 by: cwoody0
Senior Yellow Belt

Please log in to comment
0
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.%')
Answered 01/05/2011 by: airwolf
Tenth Degree Black Belt

Please log in to comment
0
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>))
Answered 01/05/2011 by: cwoody0
Senior Yellow Belt

Please log in to comment
Answer this question or Comment on this question for clarity