[Solved] no customer assigned check

[Gabedot]

Hi!

Is there any possibility to check if a ticket is connected to a customer in the customer database with Generic Agent?
I would like to check it and secure this way, that every closed ticket has been connected to a customer.

Please help!

[jojo]

no

[crythias]

jojo is right, because there is no verification of username/customerid as an entry. The values are accepted freeform.

[Gabedot]

yeah well that's not 100% true.
Because in the ticket table in the database there are in fact two cases:

1. Customer is NOT connected to the ticket:
field "customer_user_id" shows the email address.

2. A customer is connected to the ticket:
field "customer_user_id" shows the user_name which is no email address

So in a MySQL query I can find them out, in a query where "customer_user_id" IS LIKE "%@%'
The only question about it is: HOW DO I EXPRESS THIS IN GENERIC AGENT?

Code: Select all

SELECT * FROM ticket
WHERE customer_user_id LIKE '%@%';

[jojo]

well this weon't work if you are using other values than email addresses for this fields.

[crythias]

I use company short codes for my customer_id. Other people might use departments.

[Gabedot]

Hi Jojo,
I don't get your point.

[jojo]

you statement is only true for the way you set up the customers.

And there is still no out of the box possibility for checking via generic agent

[Gabedot]

well well well...
jojo... that's not very helpful. telling people something is not possible.
You should try to think out of the box and guide people to a solution.

In my case I just tried a ticket filter with

Code: Select all

*@*

and that works perfectly. The only thing is that you have to define the usernames without an @.
So finally solved.

And one think besides. The whole OTRS System is NOT an out of the box solution. So your point is a bit pointless.

[jojo]

If you don't want an answer don't ask.

The filter will give the tickets which have a customer assigned, not the ones which don't have one.

[Gabedot]

Hi Jojo,

I have tested it already and it works perfectly.
The only think you have to make sure of is:
- The username in the customer database may not have an @ symbol

...

[crythias]

Gabedot, you asked if there is a way to check if a ticket is connected to a customer in the customer database.
Your "solution" only checks if the username has an @ symbol.

How does username @@@ verify that he's in your database?
How does viagra@spammer.tld verify that he's not in your database?

[Gabedot]

Hi crythias!

The explanation is a bit tricky.

1.Case "Somebody wirtes an email to the ticket system and is not registered in the customer database"
What happens in OTRS?
The ticket is being composed an in the database the sender's email address appears in the field "ticket.customer_user_id" (e.g. "viagra@spammer.tld")

2.Case "Somebody wirtes an email to the ticket system and IS registered in the customer database"
What happens in OTRS?
The ticket is being composed an in the database the field "ticket.customer_user_id" automatically contains the username of the customer specified in the customer database. (e.g. "viagra_user")

3.Case "Somebody wirtes an email to the ticket system and is not registered in the customer database, but the OTRS operator registeres the sender as a customer manually and asign it to the ticket."
What happens in OTRS?
In the database the username of the cusomter appears int the "ticket.customer_user_id field".