You are not logged in.
My followup email template includes:
##FOREACH LAST 1 timelineitems##
##timelineitems.date##, ##timelineitems.author##:
##timelineitems.description##
##ENDFOREACHtimelineitems##I have enabled both Allow anonymous followups (receiver) and Allow anonymous ticket creation (helpdesk.receiver), so any user can send us a ticket.
This works just fine.
However, using "timelineitems.author" in my email template does not show who wrote the reply (followup).
Author is shown only if a user exist in my GLPI under "Users".
But since we receive tickets from users outside of our organization, I can't import them in my GLPI, since literally anyone can send a ticket to us from any email.
So if an outside user sends us a ticket and replies, we will not be able to see who wrote those replies. This can be a problem especially when there are more users involved in a ticket.
Do you have any idea how can I solve this?
Showing only user's email would be enough, it doesn't have to show name and lastname.
For example, if a user sends us a ticket from "name@gmail.com", the field "timelineitems.author" could show only their's email address. That's enough to know who wrote the reply.
Last edited by oneill2john (2022-09-05 10:41:21)
Offline
Any idea on how to solve this?
Can any plugin do this?
Today I was testing this further and so far the only way to show "timelineitems.author" for every followup is if I create a user with email address (under Administration > Users). When a user exists in GLPI, every followup has a name of who has send a reply.
But since we will receive tickets from anybody (not just from our organization), creating users manually is not a solution.
Offline
Still struggling with this problem ...
I can't find a way how to display followup author in tickets when a user is not in GLPI database.
I've tried by directly manipulating in database by copying data from "glpi_tickets_users.alternative_email" to "glpi_users.name" and "glpi_useremails, but that didn't work (or I don't have the right query).
Offline
Uh, after 4 days of tinkering and playing with this, I have finally managed to solve this problem.
End result is - whenever a user sends a ticket, user's email is automatically added to GLPI Users (Administration > Users).
And if you are using tag ##timelineitems.author## in your email template, user's email will now show in email followups.
Here is my solution...
1. step
You have to create MySQL query, which means you should now something about MySQL and how to create queries. Good tool that helped my is "dbForge Studio for MySQL" which is visual sql builder tool.
But if you don't anything about SQL, you can use this one I created for myself.
In this example, database is called "glpi", and table prefix is "glpi_".
(if your name is different, replace it accordingly).
First SQL Query
INSERT IGNORE INTO glpi_users (name)
SELECT DISTINCT
glpi_tickets_users.alternative_email
FROM glpi_tickets_users,
glpi_users,
glpi_useremails
WHERE glpi_tickets_users.alternative_email NOT IN (SELECT DISTINCT
glpi_useremails.email
FROM glpi_useremails)
AND glpi_tickets_users.alternative_email <> ''Explanation: this query will look for email addresses from tickets and will import them to Users. If an email address already exists in Users, it will not be imported - this will prevent creating duplicate users.
Second SQL Query
INSERT IGNORE INTO glpi_useremails (users_id, email)
SELECT DISTINCT
glpi_users.id,
glpi_tickets_users.alternative_email
FROM glpi_users,
glpi_tickets_users
WHERE glpi_users.name = glpi_tickets_users.alternative_emailExplanation: Since user's email is saved in different table, this second query will import emails in that table.
You must have both queries, otherwise it will not work.
First one imports users, the second one adds email address to imported users.
2. step
Now when you have SQL queries, you must create .php file that fill execute those queries.
You will have two php files, for executing each of above SQL queries.
File1.php
<?php
// Connect to database
$conn = new mysqli("localhost","your_database_username","database_password","database_name");
// Check connection
if ($conn -> connect_errno) {
echo "Failed to connect to MySQL: " . $conn -> connect_error;
exit();
}
// Perform query
$sql = "INSERT IGNORE INTO glpi_users (name)
SELECT DISTINCT
glpi_tickets_users.alternative_email
FROM glpi_tickets_users,
glpi_users,
glpi_useremails
WHERE glpi_tickets_users.alternative_email NOT IN (SELECT DISTINCT
glpi_useremails.email
FROM glpi_useremails)
AND glpi_tickets_users.alternative_email <> ''";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
// Disconnect MySQL connection
$conn -> close();
?>File2.php
<?php
// Connect to database
$conn = new mysqli("localhost","your_database_username","database_password","database_name");
// Check connection
if ($conn -> connect_errno) {
echo "Failed to connect to MySQL: " . $conn -> connect_error;
exit();
}
// Perform query
$sql = "INSERT IGNORE INTO glpi_useremails (users_id, email)
SELECT DISTINCT
glpi_users.id,
glpi_tickets_users.alternative_email
FROM glpi_users,
glpi_tickets_users
WHERE glpi_users.name = glpi_tickets_users.alternative_email";
if (mysqli_query($conn, $sql)) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
// Disconnect MySQL connection
$conn -> close();
?>Take a note at atribute "$conn" - you must enter your data here (database username, password, database name)
3. step
Place those two .php files somewhere in your web GLPI installation.
If you are using Linux as web server, create two cron jobs that will execute those two php files at time interval of your choosing.
And that's it.
You will now have users automatically created from tickets and they will be shown in your email followups.
Please note, if you want use those imported users for other things (like adding them Assets), you must add those users to profiles (for example to "Self-service" profile).
Offline