Using powershell to parse a csexport file

From time to time it’s necessary to access detailed data about objects in the connector space of a FIM Sync MA. One way to do this is with the csexport command line tool (found in the Bin folder) but the XML it produces isn’t particularly pretty and it doesn’t open nicely in Excel.

Luckily powershell has some great XML parsing capability, so here’s a little script I wrote which takes an XML file created by csexport, and produces a CSV file more suitable for opening in Excel. Note that the script only supports single-valued attributes – you can modify it yourself if you need multi-values.

Note: Jorge has done an improved version of this script that handles multivalue – see http://jorgequestforknowledge.wordpress.com/2013/02/08/parsing-a-csexport-generated-xml-file-into-a-scoped-csv-file/

#
# parse-csexport.ps1
#

# Change the following list to get different attributes. The first four are available for all connector spaces.
$csvcolumns = @("dn","connector-type","connector-state","mv-guid","emailAddress","userName","sn","givenName","title","personalTitle")

$csvfile = "csexport.csv"
$csexportfile = "csexport.xml"

[xml]$csexport = get-content $csexportfile

if (Test-Path $csvfile) {Remove-Item -Path $csvfile}
foreach ($csvcol in $csvcolumns) {
  $csvheader = $csvheader + ";" + $csvcol
}
Add-Content $csvfile $csvheader

foreach ($csobj in $csexport."cs-objects"."cs-object") {
  $csobjhash = @{}
  $csobjhash.Add("dn",$csobj."cs-dn")
  # Disconnectors
  if ($csobj.connector -eq "0") {
    $csobjhash.Add("connector-type","disconnector")
    $csobjhash.Add("connector-state",$csobj."connector-state")
    $csobjhash.Add("mv-guid","") 
    foreach ($attr in $csobj."unapplied-export-hologram".entry.attr) {
      if ($attr.multivalued -eq "false") {
        $csobjhash.Add($attr.name,$attr.value)
      }
    }
  }
  # Connectors
  else {
    $csobjhash.Add("connector-type","connector")
    $csobjhash.Add("connector-state",$csobj."connector-state")
    $csobjhash.Add("mv-guid",$csobj."mv-link"."#text") 
    foreach ($attr in $csobj."synchronized-hologram".entry.attr) {
      if ($attr.multivalued -eq "false") {
        $csobjhash.Add($attr.name,$attr.value)
      }
    }
  }
  $csvline = ""
  foreach ($csvcol in $csvcolumns) {
    if ($csobjhash.Contains($csvcol)) {
      if ($csvline -eq "") {$csvline = $csobjhash.Item($csvcol) }
      else {$csvline = $csvline + ";" + $csobjhash.Item($csvcol) }
    }
    else {$csvline = $csvline + ";"}
  }
  $csvline
  Add-Content $csvfile $csvline
}