Convert Novell trustee_database.xml to Excel

With this Powershell you can convert the Trustee XML of the Microfocus Novell Enterprise Server into a readable form in an Excel file. The depth of the explicit permissions is displayed in the first column. It should help to understand the structure better.

We would like to point out, if you are about to transfer your Microfocus / Novell filer to the Microsoft world, that some special features of the Windows world must be taken into account.

The Novell Microfocus file system has a decisive advantage over the Windows system. Only the rights on the authorization endpoint had to be taken care of. The file system independently ensured that the user could browse to this directory based on the least privilege principle. This is much more cumbersome under Microsoft. If you want to set clean rights, then explicit rights for the listing must be built all the way to the authorization end point. This creates a multitude of new necessary groups and can quickly become a problem.

Therefore, the token count and the token size value for the Kerberos login under Windows are strongly affected.

We therefore recommend

1. Before you migrate, look for obsolete data. These probably no longer need to be adopted. Perhaps they can be deleted or separated for emergencies.
2. to take into account the age of the data in the migration
   You no longer need explicit permissions on directories that only contain old data. Put a tool like that migRaven to analyze the permissions and age and then decide.
   https://www.migraven.com/loesungen/analyse-daten/
3. Now you can rethink the structure. It is worth questioning the old, grown structures. They can probably be built flat. You will thank your colleagues above all if you have to travel shorter distances to the data. That saves time.

If you need support during the migration, we are happy to assist you. It is our daily bread. We support migration projects around the world.

# Der Pfad zum übergeordneten Verzeichnis, in dem die XML-Dateien gespeichert sind
$strParentPath = Split-Path -Parent $MyInvocation.MyCommand.Definition
$directoryName = Split-Path -Leaf $strParentPath
$currentDate = Get-Date -Format "yyyy-MM-dd"
# Erstelle den Ausgabedateinamen mit Datum und Verzeichnisname
$excelFileName = "$currentDate-$directoryName-Trustees.xlsx"
$excelPath = Join-Path -Path $strParentPath -ChildPath $excelFileName

# Starten von Excel
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Add()

foreach ($file in Get-ChildItem $strParentPath -Filter "*.xml") {
    # Lesen der Datei mit expliziter UTF-8-Codierung
    $fileContent = Get-Content -Path $file.FullName -Encoding UTF8 -Raw
    $doc = [xml]$fileContent

    $sheetName = "$directoryName-$($file.BaseName)"
    # Begrenze die Länge des Blattnamens auf 31 Zeichen
    if ($sheetName.Length -gt 31) {
        $sheetName = $sheetName.Substring(0, 31)
    }
    $sheet = $workbook.Worksheets.Add()
    $sheet.Name = $sheetName

    # Kopfzeile hinzufügen
    $sheet.Cells.Item(1, 1).Value2 = "Tiefe"
    $sheet.Cells.Item(1, 2).Value2 = "Pfad"
    $sheet.Cells.Item(1, 3).Value2 = "Trustee Name"
    $sheet.Cells.Item(1, 4).Value2 = "Trustee ID"
    $sheet.Cells.Item(1, 5).Value2 = "Rechte"
    $row = 2

    foreach ($fileElement in $doc.metadata.trusteeInfo.file) {
        $path = $fileElement.path.'#cdata-section'
        $strPath = [string]$path
        $strPath = $strPath -replace "/", "\"
        $arrFields = $strPath.Split('\', [System.StringSplitOptions]::RemoveEmptyEntries)
        $strCount = $arrFields.Count - 1

        foreach ($trustee in $fileElement.trustee) {
            $name = $trustee.name.'#cdata-section'
            $id = $trustee.id
            $rights = $trustee.rights.value

            # Daten in das Arbeitsblatt eintragen
            $sheet.Cells.Item($row, 1).Value2 = "$strCount"
            $sheet.Cells.Item($row, 2).Value2 = $path
            $sheet.Cells.Item($row, 3).Value2 = $name
            $sheet.Cells.Item($row, 4).Value2 = $id
            $sheet.Cells.Item($row, 5).Value2 = $rights

            $row++
        }
        # Falls kein Trustee vorhanden ist, nur Tiefe und Pfad eintragen
        if ($fileElement.trustee -eq $null -or $fileElement.trustee.Count -eq 0) {
            $sheet.Cells.Item($row, 1).Value2 = "$strCount"
            $sheet.Cells.Item($row, 2).Value2 = $path
            $row++
        }
    }
}

# Speichern und Schließen
$workbook.SaveAs($excelPath)
$excel.Quit()

# Aufräumen
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel

Write-Host "Excel-Datei wurde erstellt: $excelPath"

#Variante 2
# in dieser Version werden alle Ergebnisse aus allen Trustee Dateien in einer Excel in einem Blatt zusammengefasst.
# Das gibt noch mal eine bessere Option für die Auswertung der Daten.

# Der Pfad zum übergeordneten Verzeichnis, in dem die XML-Dateien gespeichert sind
$strParentPath = Split-Path -Parent $MyInvocation.MyCommand.Definition
$directoryName = Split-Path -Leaf $strParentPath
$currentDate = Get-Date -Format "yyyy-MM-dd"
# Erstelle den Ausgabedateinamen mit Datum und Verzeichnisname
$excelFileName = "$currentDate-$directoryName-Trustees.xlsx"
$excelPath = Join-Path -Path $strParentPath -ChildPath $excelFileName

# Starten von Excel
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false
$workbook = $excel.Workbooks.Add()
$sheet = $workbook.Worksheets.Add()
$sheet.Name = "Alle Trustee-Informationen"

# Kopfzeile hinzufügen
$sheet.Cells.Item(1, 1).Value2 = "Tiefe"
$sheet.Cells.Item(1, 2).Value2 = "Pfad"
$sheet.Cells.Item(1, 3).Value2 = "Trustee Name"
$sheet.Cells.Item(1, 4).Value2 = "Trustee ID"
$sheet.Cells.Item(1, 5).Value2 = "Rechte"
$sheet.Cells.Item(1, 6).Value2 = "Server"
$sheet.Cells.Item(1, 7).Value2 = "Volume"
$row = 2

foreach ($file in Get-ChildItem $strParentPath -Filter "*.xml" -Recurse) {
    $fileContent = Get-Content -Path $file.FullName -Encoding UTF8 -Raw
    $doc = [xml]$fileContent
    $serverName = Split-Path -Leaf (Split-Path -Parent $file.FullName)

    foreach ($fileElement in $doc.metadata.trusteeInfo.file) {
        $path = $fileElement.path.'#cdata-section'
        $strPath = [string]$path
        $strPath = $strPath -replace "/", "\"
        $arrFields = $strPath.Split('\', [System.StringSplitOptions]::RemoveEmptyEntries)
        $strCount = $arrFields.Count - 1

        foreach ($trustee in $fileElement.trustee) {
            $name = $trustee.name.'#cdata-section'
            $id = $trustee.id
            $rights = $trustee.rights.value

            # Daten in das Arbeitsblatt eintragen
            $sheet.Cells.Item($row, 1).Value2 = "$strCount"
            $sheet.Cells.Item($row, 2).Value2 = $path
            $sheet.Cells.Item($row, 3).Value2 = $name
            $sheet.Cells.Item($row, 4).Value2 = $id
            $sheet.Cells.Item($row, 5).Value2 = $rights
            $sheet.Cells.Item($row, 6).Value2 = $serverName
            $sheet.Cells.Item($row, 7).Value2 = $file.Name

            $row++
        }
        # Falls kein Trustee vorhanden ist, nur Tiefe und Pfad eintragen
        if ($fileElement.trustee -eq $null -or $fileElement.trustee.Count -eq 0) {
            $sheet.Cells.Item($row, 1).Value2 = "$strCount"
            $sheet.Cells.Item($row, 2).Value2 = $path
            $sheet.Cells.Item($row, 6).Value2 = $serverName
            $sheet.Cells.Item($row, 7).Value2 = $file.Name
            $row++
        }
    }
}

# Speichern und Schließen
$workbook.SaveAs($excelPath)
$excel.Quit()

# Aufräumen
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel

Write-Host "Excel-Datei wurde erstellt: $excelPath"