Issue with mail adresses when uploading from outlook

[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 4290 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

[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.

[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?

[pavila]

Please, attach the script because other uses may have the same problem.

Regards.

[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);
                                }
                }
}

?>

[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.

[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.

[jllort]

It's perfect, thanks for it. I've updated wiki.