Special characters (umlaute üöä) in CDR report

Is there any way to fix the CDR reports in FreePBX to show special characters such as the German Umlaute äüö?

We have a lot of names with those characters and everywhere else they seem to work. They are reported properly to the phones as CDRnames, the console shows them " – Executing [[email protected]:44] Set(“SIP/sipconnect-00000071”, “CALLERID(name)=C:Krützfeldt, Christian”) in new stack". All other parts of the FreePBX GUI are fine with them.

Just the reports under https://myserver/admin/config.php?display=cdr don’t like the characters and show garbage. Same is true if I export the reports as CSV File.
It probably has something to do with the character encoding but I don’t know how to fix it.

This is something that the Asterisk development team (not the FreePBX development team) has on their schedule to investigate over the next couple of months.

It’s probably related to incorrect mysql encoding types or invalid settings in the ODBC driver if I had to guess. I found a few tickets in FreePBX related to this:
https://issues.freepbx.org/browse/FREEPBX-15268?jql=text%20~%20"utf8mb4"

But at a minimum I would double check that mysql encoding types are set to the correct encoding.

Is the MySQL character encoding something I should play around with? I don’t want to break anything FreePBX created and might expect to be a certain way.
It is not an urgent issue so if it is a known issue and the devolopment team is looking into it - I’m happy to wait for a fix.

@Balticfinance I’m not sure if it will break anything or not, but it would be nice to know if the MySQL server and database tables have the correct encoding type set at least. Looking up that information would be read only and should not break anything.

OK, had a look together with a colleague who knows more about mariadb then I do.

The database is latin, the cdr table columns are utf8 - strange but ok.

We updated one of the cdr records from the terminal to have some proper äöü in it and the FreePBX GUI shows the correct record with the characters intact.
Next we tried to update the database to also be in utf8 instead of latin and then restarted the database and asterisk. But that didn’t help new records are still saved wrong.

Summary, the database seems to support umlaute in it’s current configuration and the web GUI will show them should they be present. The asterisk CLI also shows the correct umlaute. So my guess is the problem is with the code that saves the CDR entries.

Do you use CID-superfecta? If yes, I had a similar problem and had to apply some “würgarounds”.

First I did this:

rpm -e mysql-connector-odbc --nodeps
yum -y install mariadb-connector-odbc
fwconsole restart

It is possible that in a newer version of freePBX the Umlaute problem with mysql-connector is already fixed…I dont know…
Then, I checked exisiting entries in the asterisk-superfecta-cache table. I deleted or edited entries, which contained e.g. a ? instead of a umlaut, using phpmyadmin.

Afterwards I added some code to superfecta.class.php (admin/modules/superfecta)
in line 191 (or line 209…depends on your version of superfecta)
below lines: $found = false;
if (!empty($callerid)) {
$found = true;

  		$callerid = strtr($callerid, array(
    'ъ'=>'-', 'Ь'=>'-', 'Ъ'=>'-', 'ь'=>'-',
    'Ă'=>'A', 'Ą'=>'A', 'À'=>'A', 'Ã'=>'A', 'Á'=>'A', 'Æ'=>'A', 'Â'=>'A', 'Å'=>'A', 'Ä'=>'Ae',
    'Þ'=>'B',
    'Ć'=>'C', 'ץ'=>'C', 'Ç'=>'C',
    'È'=>'E', 'Ę'=>'E', 'É'=>'E', 'Ë'=>'E', 'Ê'=>'E',
    'Ğ'=>'G',
    'İ'=>'I', 'Ï'=>'I', 'Î'=>'I', 'Í'=>'I', 'Ì'=>'I',
    'Ł'=>'L',
    'Ñ'=>'N', 'Ń'=>'N',
    'Ø'=>'O', 'Ó'=>'O', 'Ò'=>'O', 'Ô'=>'O', 'Õ'=>'O', 'Ö'=>'Oe',
    'Ş'=>'S', 'Ś'=>'S', 'Ș'=>'S', 'Š'=>'S',
    'Ț'=>'T',
    'Ù'=>'U', 'Û'=>'U', 'Ú'=>'U', 'Ü'=>'Ue',
    'Ý'=>'Y',
    'Ź'=>'Z', 'Ž'=>'Z', 'Ż'=>'Z',
    'â'=>'a', 'ǎ'=>'a', 'ą'=>'a', 'á'=>'a', 'ă'=>'a', 'ã'=>'a', 'Ǎ'=>'a', 'а'=>'a', 'А'=>'a', 'å'=>'a', 'à'=>'a', 'א'=>'a', 'Ǻ'=>'a', 'Ā'=>'a', 'ǻ'=>'a', 'ā'=>'a', 'ä'=>'ae', 'æ'=>'ae', 'Ǽ'=>'ae', 'ǽ'=>'ae',
    'б'=>'b', 'ב'=>'b', 'Б'=>'b', 'þ'=>'b',
    'ĉ'=>'c', 'Ĉ'=>'c', 'Ċ'=>'c', 'ć'=>'c', 'ç'=>'c', 'ц'=>'c', 'צ'=>'c', 'ċ'=>'c', 'Ц'=>'c', 'Č'=>'c', 'č'=>'c', 'Ч'=>'ch', 'ч'=>'ch', 'ד'=>'d', 'ď'=>'d', 'Đ'=>'d', 'Ď'=>'d', 'đ'=>'d', 'д'=>'d', 'Д'=>'D', 'ð'=>'d',
    'є'=>'e', 'ע'=>'e', 'е'=>'e', 'Е'=>'e', 'Ə'=>'e', 'ę'=>'e', 'ĕ'=>'e', 'ē'=>'e', 'Ē'=>'e', 'Ė'=>'e', 'ė'=>'e', 'ě'=>'e', 'Ě'=>'e', 'Є'=>'e', 'Ĕ'=>'e', 'ê'=>'e', 'ə'=>'e', 'è'=>'e', 'ë'=>'e', 'é'=>'e',
    'ф'=>'f', 'ƒ'=>'f', 'Ф'=>'f',
    'ġ'=>'g', 'Ģ'=>'g', 'Ġ'=>'g', 'Ĝ'=>'g', 'Г'=>'g', 'г'=>'g', 'ĝ'=>'g', 'ğ'=>'g', 'ג'=>'g', 'Ґ'=>'g', 'ґ'=>'g', 'ģ'=>'g', 'ח'=>'h', 'ħ'=>'h', 'Х'=>'h', 'Ħ'=>'h', 'Ĥ'=>'h', 'ĥ'=>'h', 'х'=>'h', 'ה'=>'h',
    'î'=>'i', 'ï'=>'i', 'í'=>'i', 'ì'=>'i', 'į'=>'i', 'ĭ'=>'i', 'ı'=>'i', 'Ĭ'=>'i', 'И'=>'i', 'ĩ'=>'i', 'ǐ'=>'i', 'Ĩ'=>'i', 'Ǐ'=>'i', 'и'=>'i', 'Į'=>'i', 'י'=>'i', 'Ї'=>'i', 'Ī'=>'i', 'І'=>'i', 'ї'=>'i', 'і'=>'i', 'ī'=>'i', 'ij'=>'ij', 'IJ'=>'ij',
    'й'=>'j', 'Й'=>'j', 'Ĵ'=>'j', 'ĵ'=>'j', 'я'=>'ja', 'Я'=>'ja', 'Э'=>'je', 'э'=>'je', 'ё'=>'jo', 'Ё'=>'jo', 'ю'=>'ju', 'Ю'=>'ju',
    'ĸ'=>'k', 'כ'=>'k', 'Ķ'=>'k', 'К'=>'k', 'к'=>'k', 'ķ'=>'k', 'ך'=>'k',
    'Ŀ'=>'l', 'ŀ'=>'l', 'Л'=>'l', 'ł'=>'l', 'ļ'=>'l', 'ĺ'=>'l', 'Ĺ'=>'l', 'Ļ'=>'l', 'л'=>'l', 'Ľ'=>'l', 'ľ'=>'l', 'ל'=>'l', 'מ'=>'m', 'М'=>'m', 'ם'=>'m', 'м'=>'m',
    'ñ'=>'n', 'н'=>'n', 'Ņ'=>'n', 'ן'=>'n', 'ŋ'=>'n', 'נ'=>'n', 'Н'=>'n', 'ń'=>'n', 'Ŋ'=>'n', 'ņ'=>'n', 'ʼn'=>'n', 'Ň'=>'n', 'ň'=>'n',
    'о'=>'o', 'О'=>'o', 'ő'=>'o', 'õ'=>'o', 'ô'=>'o', 'Ő'=>'o', 'ŏ'=>'o', 'Ŏ'=>'o', 'Ō'=>'o', 'ō'=>'o', 'ø'=>'o', 'ǿ'=>'o', 'ǒ'=>'o', 'ò'=>'o', 'Ǿ'=>'o', 'Ǒ'=>'o', 'ơ'=>'o', 'ó'=>'o', 'Ơ'=>'o', 'œ'=>'oe', 'Œ'=>'oe', 'ö'=>'oe', 'פ'=>'p', 'ף'=>'p', 'п'=>'p', 'П'=>'p', 'ק'=>'q',
    'ŕ'=>'r', 'ř'=>'r', 'Ř'=>'r', 'ŗ'=>'r', 'Ŗ'=>'r', 'ר'=>'r', 'Ŕ'=>'r', 'Р'=>'r', 'р'=>'r',
    'ș'=>'s', 'с'=>'s', 'Ŝ'=>'s', 'š'=>'s', 'ś'=>'s', 'ס'=>'s', 'ş'=>'s', 'С'=>'s', 'ŝ'=>'s', 'Щ'=>'sch', 'щ'=>'sch', 'ш'=>'sh', 'Ш'=>'sh', 'ß'=>'ss',
    'т'=>'t', 'ט'=>'t', 'ŧ'=>'t', 'ת'=>'t', 'ť'=>'t', 'ţ'=>'t', 'Ţ'=>'t', 'Т'=>'t', 'ț'=>'t', 'Ŧ'=>'t', 'Ť'=>'t', '™'=>'tm',
    'ū'=>'u', 'у'=>'u', 'Ũ'=>'u', 'ũ'=>'u', 'Ư'=>'u', 'ư'=>'u', 'Ū'=>'u', 'Ǔ'=>'u', 'ų'=>'u', 'Ų'=>'u', 'ŭ'=>'u', 'Ŭ'=>'u', 'Ů'=>'u', 'ů'=>'u', 'ű'=>'u', 'Ű'=>'u', 'Ǖ'=>'u', 'ǔ'=>'u', 'Ǜ'=>'u', 'ù'=>'u', 'ú'=>'u', 'û'=>'u', 'У'=>'u', 'ǚ'=>'u', 'ǜ'=>'u', 'Ǚ'=>'u', 'Ǘ'=>'u', 'ǖ'=>'u', 'ǘ'=>'u', 'ü'=>'ue',
    'в'=>'v', 'ו'=>'v', 'В'=>'v', 'ש'=>'w', 'ŵ'=>'w', 'Ŵ'=>'w',
    'ы'=>'y', 'ŷ'=>'y', 'ý'=>'y', 'ÿ'=>'y', 'Ÿ'=>'y', 'Ŷ'=>'y',
    'Ы'=>'y', 'ž'=>'z', 'З'=>'z', 'з'=>'z', 'ź'=>'z', 'ז'=>'z', 'ż'=>'z', 'ſ'=>'z', 'Ж'=>'zh', 'ж'=>'zh'
));

and I changed one of the following lines to:

$callerid = iconv(‘ISO-8859-15//TRANSLIT’, ‘UTF-8’, $callerid);

I am just a user no pro. Since I applied all those changes German Umlaute are displayed correctly or are replaced by ae, oe, ue etc everywhere in freePBX and on my phones…

Charles, no we don’t use the CID Superfecta. In fact it isn’t even installed. It is available locally but currently not installed.
Also our phones, and the CLI display Umlaute just fine. It really is only the CDR report that doesn’t like them.

Does your system use the mysql connector or the mariadb connector? In my case, switching from mysql to mariadb connector solved the Umlaute problem in cdr reports.
Be careful…you can break things!
Why don’t you use CID Superfecta? It‘s awesome,.,and it has a lookup template for dasTelefonbuch.de!!

1 Like

We use our internal CIDLookup that checks our CRM system and other internal data sources.
Most of our customers are not calling from German landlines so dasTelefonbuch won’t help that much.
So I haven’t really seen the need for CID Superfecta. But I’ll give it a try.

I’ll have a look for the connector I didn’t touch, we downloaded FreePBX, and clicked on Next until it was installed on the system. So we use whatever the default is.
Can you tell me where I can which one is currently in use?

If you installed freePBX just by using the Web-GUI, better don’t try to fix the cdr report issue.
It is just for advanced users…and Linux freaks :wink:
You have to ssh into the machine and use commands in the terminal window.

EDIT: By the way, you can use CID superfecta with internal sources too, e.g. a LDAP-server.

This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.