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
}

About: Carol

I've been doing IT for 30 years, and IdM for 15. I live in Australia and build IdM solutions based on Microsoft Identity Manager. I also play the violin, but that doesn't help much with the IdM solutions.


8 thoughts on “Using powershell to parse a csexport file”

  1. this is exactly what Im looking for but I could really use some help with parsing username and emailaddress. I’ve tryied so many ways by modifying your code but no luck. Can you please help?

  2. All you need to do is modify the line at the top “$csvcolumns = …”. Make sure the column names you list are exactly the same as the attribute names in your connector space. In the case I developed this for my connector space attribute is called “emailAddress”. It is probably not the same in yours.

  3. Since I last posted I did what what you suggested but the problem seems to be separating out the columns.
    $csvcolumns = @(“emailAddress”,”userName”)
    this just creates one column and puts both names in it like this:
    emailaddress;username
    here is what the xml file looks like partially:
    Removed

  4. I’m finding that this line (around line 53) needs to change from

    If ($csObject.connector -eq “0”) {

    to
    If ($csObject.connector -eq $null) {

    (fwiw, on my screen it is a funny font. That’s a zero between the quotes and not an O)

  5. Hi Carol

    Using your script, i have a bunch of pending export changes that include phone numbers being removed. Ive added the ‘telephoneNumber’ attribute to the script…but i can get these attributes into the csv file…it just displays dn and connector type only. i can see the numbers in the csexport output.

    Any ideas or assistance would be great.

    cheers
    stu

Leave a Reply

Your email address will not be published. Required fields are marked *


*