— dkoch 2014/02/09 12:15 This script synchronizes the client descriptions from OPSI to the AD.

1. You will have to store a mysql.exe binary in the same directory as the script.
2. Replace DATABASE-HOST DATABASEUSER PASSWORD and DATABASE to fit for your setup
3. Make sure your MySQL server listens ( bind-address in my.cnf )

# This script synchronizes the client descriptions from OPSI to the AD
 
# Connect to the MYSQL of OPSI and crete a csv with $hostId and $description
echo "Getting information from OPSI-Backend..."
chcp 850
echo "SET NAMES 'cp850' ; select hostId, description from HOST" | `
./mysql.exe --default-character-set=utf8 -hDATABASE-HOST -uDATABASEUSER -pPASSWORD -DDATABASE > .output.sql
 
# Convert SQL output to valid CSV output
(Get-Content .output.sql )  | Foreach-Object {
    $_ -replace '\t', ';'
	} | Set-Content -encoding utf8 .output.csv
 
echo "Pushing to AD..."
# Load CSV and transform values for $hostId and $description
Import-Csv ./.output.csv -encoding utf8 -Delimiter ";"  |`
    ForEach-Object {
        $hostId += $_.hostId.ToUpper().Replace(".WKK-INTRA.NET","")
        $description += $_.description
 
		# If $description is NOT empty or null 
		if (!([string]::IsNullOrEmpty($description))) {
			# If $hostId is found in AD-Backend
			if ($hostou=(dsquery computer domainroot -name "$hostId")) {
				# If $hostId has NOT same $description yet
				if (!(dsquery computer domainroot -name "$hostId" -desc "$description")) {
				$olddescription=(dsget computer "$hostou" -desc | select -first 1 -skip 1)
				$olddescription= $olddescription.Trim()
 
				echo "Updating $hostId with description: `"$description`" was `"$olddescription`"" >> log
				dsmod computer $hostou -desc "$description"
 
				}
			}
		}
 
		$hostOu = @()
		$hostid=@()
		$description = @()
		$objComputer= @()
		}