Page 1 of 1
Issue with mail adresses when uploading from outlook
PostPosted:Tue Aug 26, 2014 11:10 am
by malte
Hi!
I have a problem when I upload mails from my outlook (Exchange) to OpenKM with the plugin. All local adresses are shown as follows
Name.png (5.97 KiB) Viewed 4885 times
When I save and upload a .msg file the names are fine...
While using OpenKM this aint a big problem, just annoying, but when I try to do a Repository Export it will fail because of whitspace in the mail address.
Can this be resolved using LDAP or is there any other solution for this?
Thank you very much!
Regards
Malte
Re: Issue with mail adresses when uploading from outlook
PostPosted:Wed Aug 27, 2014 6:58 pm
by jllort
Could you send us some msg file to test. Basically to be sure I've understood. If you upload *.msg all goes fine, but if same mail - msg - is uploaded from outlook plugin then fails getting mails.
Re: Issue with mail adresses when uploading from outlook
PostPosted:Thu Aug 28, 2014 11:07 am
by malte
The mail upload from outlook does not fail, but it shows the mail as "Exchange Legacy DN" as I was told this is called.
If I paste this legacy DN into Outlook, the name is recognised after a while. OpenKM cannot resolve these Exchance Legacy DNs, because it is neither connected to Exchange, nor to the Outlook global adress list (GAL).
To solve this issue, I wrote a small php script, which will change all these Legacy DN strings into proper mail adresses. Therefore I got an export of the global adress list from our IT as .csv containing all the Exchange Legacy DNs and their corresponding mail adresses.
With these data I can alter the database and change Exchange Legacy DN to normal mail adresses.
If this is useful for anybody I can pubish the script here.
Generally iI think it would be better to make the Outlook Script do this instead of messing with the DB?
Re: Issue with mail adresses when uploading from outlook
PostPosted:Sun Aug 31, 2014 6:44 am
by pavila
Please, attach the script because other uses may have the same problem.
Regards.
Re: Issue with mail adresses when uploading from outlook
PostPosted:Tue Sep 09, 2014 10:50 am
by malte
Here you go. Its written in PHP. I set up a cronjob that will run the script once a minute.
I think with large databases you may better change the select statement to sth. like "SELECT ... WHERE NML_FROM LIKE '%/o' " for better performance.
Code: Select all<?php
# Change ExchangeDN to Mailadresses
# Malte Küppers
#
# CSV Legacy File Format: Exchange Legacy DN ; Mailadresse
#
#
# variables
$row = 0;
$legacy_file = "/root/Hilfsdateien/LegacyExchangeDN.txt" # Set to your filepath
if (($handle = fopen($legacy_file, "r")) !== FALSE) {
while (($data = fgetcsv($handle, 10000, ";")) !== FALSE) {
$exchdn[$row]=strtoupper($data[0]); #Exchange Legacy DN
$mail[$row]=$data[1]; #Mailadresse
$row++;
}
fclose($handle);
#echo $row ." lines read";
}
#Open Database Connection
$db = mysqli_connect("127.0.0.1", "<USERNAME>", "<PASSWORD>", "okmdb");
if(!$db)
{
exit("Verbindungsfehler: ".mysqli_connect_error());
}
# Mail FROM ändern
$ergebnis = mysqli_query($db, "SELECT DISTINCT NML_FROM FROM OKM_NODE_MAIL");
while($row = mysqli_fetch_object($ergebnis)){
#only adresses that needs to be changed (Starting with /o= )
if(strpos($row->NML_FROM,"/o")!==false) {
$key = array_search(strtoupper($row->NML_FROM), $exchdn);
if(is_numeric($key)){
echo $row->NML_FROM ." found at Pos #".$key ."<br>";
$qry = "UPDATE OKM_NODE_MAIL SET NML_FROM = '".$mail[$key]."' WHERE NML_FROM ='".$row->NML_FROM."';";
#echo $qry ."<br><br>";
$update = mysqli_query($db,$qry);
}
}
}
# Mail TO ändern
$ergebnis = mysqli_query($db, "SELECT DISTINCT NML_TO FROM OKM_NODE_MAIL_TO");
while($row = mysqli_fetch_object($ergebnis)){
#Nur Adressen die nicht geändert wurden
if(strpos($row->NML_TO,"/o")!==false) {
$key = array_search(strtoupper($row->NML_TO), $exchdn);
if(is_numeric($key)){
echo $row->NML_TO ." gefunden an Pos #".$key ."<br>";
$qry = "UPDATE OKM_NODE_MAIL_TO SET NML_TO = '".$mail[$key]."' WHERE NML_TO ='".$row->NML_TO."';";
#echo $qry ."<br><br>";
$update = mysqli_query($db,$qry);
}
}
}
# Mail CC ändern
$ergebnis = mysqli_query($db, "SELECT DISTINCT NML_CC FROM OKM_NODE_MAIL_CC");
while($row = mysqli_fetch_object($ergebnis)){
#Nur Adressen die nicht geändert wurden
if(strpos($row->NML_CC,"/o")!==false){
$key = array_search(strtoupper($row->NML_CC), $exchdn);
if(is_numeric($key)){
echo $row->NML_CC ." gefunden an Pos #".$key ."<br>";
$qry = "UPDATE OKM_NODE_MAIL_CC SET NML_CC = '".$mail[$key]."' WHERE NML_CC ='".$row->NML_CC."';";
#echo $qry ."<br><br>";
$update = mysqli_query($db,$qry);
}
}
}
# Mail BCC ändern
$ergebnis = mysqli_query($db, "SELECT DISTINCT NML_BCC FROM OKM_NODE_MAIL_BCC");
while($row = mysqli_fetch_object($ergebnis)){
#Nur Adressen die nicht geändert wurden
if(strpos($row->NML_BCC,"/o")!==false){
$key = array_search(strtoupper($row->NML_BCC), $exchdn);
if(is_numeric($key)){
echo $row->NML_BCC ." gefunden an Pos #".$key ."<br>";
$qry = "UPDATE OKM_NODE_MAIL_BCC SET NML_BCC = '".$mail[$key]."' WHERE NML_BCC ='".$row->NML_BCC."';";
#echo $qry ."<br><br>";
$update = mysqli_query($db,$qry);
}
}
}
?>
Re: Issue with mail adresses when uploading from outlook
PostPosted:Fri Sep 12, 2014 6:35 pm
by jllort
I've added the script here
http://wiki.openkm.com/index.php/Conver ... _Legacy_DN. Do you got simply sample of the .csv file to add there too.
Re: Issue with mail adresses when uploading from outlook
PostPosted:Thu Sep 18, 2014 1:25 pm
by malte
The file corresponding .csv looks like this:
Code: Select all/o=EMail/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=somefancyname;f.ancy@sample.com
/o=EMail/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=anotherfancyname;an.fancy@othersample.com
/o=EMail/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=m.mueller;m.mueller@mydomain.com
I used sample data for privacy reasons.
Re: Issue with mail adresses when uploading from outlook
PostPosted:Fri Sep 19, 2014 5:38 pm
by jllort
It's perfect, thanks for it. I've updated wiki.
Name.png (5.97 KiB) Viewed 4885 times
When I save and upload a .msg file the names are fine...