I'm having a problem with the Notifications in iTop (2.0.1). I used the code from this thread https://sourceforge.net/p/itop/discussion/922360/thread/25aaba5a/ to be able to send a Notification when a Ticket is updated by an admin / agent.
But now every time the Ticket is updated, two emails are sent. One to the user and one to the agent assigned to the Ticket.
What I want it to do:
If it's updated by the user - send mail to agent
If it's updated by an admin / agent - send mail to user
Is there a way to distinguish, who last updated the Ticket and then send a Notification based on that?
I'm currently trying to get the userid that last updated the request by going through the public_log of the request. What is returned when I read the public_log looks like this:
========== 2013-07-11 09:38:57 : Test User (102) ============ QWER ========== 2013-07-10 09:32:24 : Test User (102) ============ BSDQFSD
I just need to have an OQL Query, that gets the Number in the first parenthesis after the User's name.
Any help appreciated. Will post if I find something myself.
So... I came up with this:
SELECT Person AS p
WHERE (p.friendlyname = IF(:this->caller_id_friendlyname = SUBSTRING_INDEX(SUBSTR(:this->public_log,34,40),' ',2),:this->agent_id_friendlyname,:this->caller_id_friendlyname))
It should take a string (using SUBSTR) from the public log, starting from index 34 (Where the username starts) for 40 characters (I don't know how long the name will be, so I just set something high enough).
Then it takes this string and cuts it off after the 2nd space, which should return the full username.
Then it compares if this username is the ticket caller's name, and if so, returns the agent's name, if not, returns the user's name.
And then selects the Person to send the email according to the friendlyname attribute.
In theory, I thought that would work. But when I test it I get this Error:
Unexpected token PAR_OPEN, found '(' in: SELECT Person AS p WHERE (p.friendlyname = IF(:this->caller_id_friendlyname = SUBSTRING_INDEX(SUBSTR(:this->public_log,34,40),' ',2),:this->agent_id_friendlyname,:this->caller_id_friendlyname))
It seems iTop doesn't like the '(' after SUBSTRING_INDEX. Can I not use SUBSTRING_INDEX or do I have to use something other than '(' ?
Btw. this would be so much easier, if there was something like a 'lastupdatedby' field in the UserRequest class
So I found that I really cant use SUBSTRING_INDEX. I cut down my query to look like this:
SELECT Person AS p
WHERE (p.friendlyname = IF(:this->caller_id_friendlyname = SUBSTR(:this->public_log,34,9),:this->agent_id_friendlyname,:this->caller_id_friendlyname))
In this example case, it actually works, because the caller's name "Test User" is 9 characters long. But that doesn't help me.
I added a field "friendlyname_length" to "view_contact" in the iTop database by adding this line:
length(concat(_fn_person_person.first_name,' ',_contact.name)) AS friendlyname_length
This gives me the full length of a persons name, which I could use in my OQL query as the length attribute of the SUBSTR() function.
But I can't query this from the Database using OQL in iTOP. I need to change the Datamodel, right? How do I do that.
I want my final query to look something like this:
SELECT Person AS p
WHERE (p.friendlyname = IF(:this->caller_id_friendlyname = SUBSTR(:this->public_log,34,:this->caller_id_friendlyname_length),:this->agent_id_friendlyname,:this->caller_id_friendlyname))
Next Update, cause I hit a wall again...
I'm still trying to get the name of the last person to edit the public_log to determine who to send the notification to, when the ticket is updated. But I went for a little bit different approach this time. I tried working with the Data Model Toolkit, but I'm pretty sure I wasnt't supposed to do it like this.
What I did:
I copy / pasted the whole "itop-config-mgmt" folder from "env-production" to "extensions"
and added this field definition to the "Person" Class in datamodel.itop-config-mgmt.xml
<field id="name_length" xsi:type="AttributeInteger">
then went to the toolkit and let it update my itop + database, which gave me a new column in my "itopperson" table called "name_length" and a field in the itop class person, called name_length.
Then I filled this column with data (the length of the persons full name) using this sql command:
UPDATE itopperson,itopcontact SET itopperson.name_length=char_length(concat(itopperson.first_name, itopcontact.name))+1 WHERE itopperson.id = itopcontact.id;
I still couldn't read this value from a ticket update, so I created an external field in the UserRequest class by:
adding a new field to the "User Request" class in the datamodel.itop-request-mgmt-itil.xml file
<field id="caller_id_friendlyname_length" xsi:type="AttributeExternalField">
so i can call the caller's name length as :this->caller_id_friendlyname_length
3. Updated the itop code with the toolkit and tested my OQL query again
In my Test Scenario, the caller's name is "Test User" the agent's name is "Timo Reinhardt" and the value for public_log I test with is (taken from $this->public_log$ of a ticket notification):
========== 2013-07-16 15:27:14 : Test User (102) ============ eqwe ========== 2013-07-16 15:26:28 : Timo Reinhardt (3) ============ zwerr ========== 2013-07-16 15:22:16 : Timo Reinhardt (3) ============ rewq ========== 2013-07-16 15:20:54 : Test User (102) ============ qwer ========== 2013-07-16 15:20:13 : Test User (102) ============ qwer ========== 2013-07-08 13:10:56 : Test User (102) ============ asdaf
When I test this in Run Queries, it works perfectly. The last user who edited the ticket is Test User, which equals :this->caller_id_friendlyname, so the agent's Person Object is returned (see upper part of the screenshot in attachment).
But when I put this OQL in the "To:" field of a Notification action (middle part of screenshot).
The only result I ever get is Test User... (lower part of screenshot)
If anyone knows where this might come from, please post it. Thank You!
I'm not sure that trying to extract the information from the case log is the best way to proceed. Let me propose something a bit different:
Let's write a small extension (see attachment) that modifies the "ticket" class. this extension adds a new boolean field "admin_updated". This field is set to 1 when the ticket is being updated by an admin, 0 otherwise. This is achieved by overloading the "ComputeValues()" method of the Ticket class.
Then for each operation where you want to distinguish the notifications between admin/non admin updates, create two actions attached to the same trigger. In the OQL query that determines the list of contacts to notify, either add the criteria "AND :this->admin_updated=1" (for notifying admin only) or "AND :this->admin_updated=0" (for notifying non-admin users).
This way one of the two lists of contacts will always be empty (an empty list of contacts does not cause any notification to be sent).
Hope this helps,
that works perfectly, thank you so much. I'm new to iTop so I don't know my way around it yet.
The toolkit only "updates" the modules/extensions which are already installed.
Every time you want to add a new extension you have to run the setup first, then you can use the toolkit for performing updates, each time you modify an installed module/extension.
I see, thanks again.
I just found another Problem with this. When you add this extension, you cannot create a change, because "admin_update"'s value is not allowed to be "NULL".
So if anyone else is using this extension and needs to create changes in iTop, just add
to the field definition in the extensions XML-File