Convert Exchange Legacy DN

From OpenKM Documentation
Jump to: navigation, search

Small php script, which will change all these Legacy DN strings into proper mail adresses. Therefore there's 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 it can be altered the database and change Exchange Legacy DN to normal mail adresses.

CSV sample

/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


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

?>


More information at [forum]