OPSI2AD

— 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< >DATABASE< (near mysql.exe) and >YOUR-DOMAIN-SUFFIX< (near ForEach) to fit for your setup
3. Make sure your MySQL server listens ( bind-address in my.cnf )
4. Execute this script as Domain-Administrator on an DC

This Script requires Powershell 3

# This script synchronizes the client descriptions from OPSI to the AD
 
$timestamp=Get-Date -format u
# 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 -h>DATABASE-HOST< -u>DATABASEUSER< -p>PASSWORD< -D>DATABASE< > .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(".>YOUR-DOMAIN-SUFFIX<","")
        $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 "[$timestamp] Updating $hostId with description: `"$description`" was `"$olddescription`"" >> opsi2ad.log
				dsmod computer $hostou -desc "$description"
 
				}
			}
		}
 
		$hostOu = @()
		$hostid=@()
		$description = @()
		$objComputer= @()
		}