{"id":1710,"date":"2011-09-25T04:31:15","date_gmt":"2011-09-25T04:31:15","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=1710"},"modified":"2013-02-21T08:01:18","modified_gmt":"2013-02-21T08:01:18","slug":"using-powershell-to-parse-a-csexport-file","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/using-powershell-to-parse-a-csexport-file","title":{"rendered":"Using powershell to parse a csexport file"},"content":{"rendered":"<p>From time to time it&#8217;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 <strong>csexport<\/strong> command line tool (found in the Bin folder) but the XML it produces isn&#8217;t particularly pretty and it doesn&#8217;t open nicely in Excel.<\/p>\n<p>Luckily powershell has some great XML parsing capability, so here&#8217;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 &#8211; you can modify it yourself if you need multi-values.<\/p>\n<blockquote><p>Note: Jorge has done an improved version of this script that handles multivalue &#8211; see <a href=\"http:\/\/jorgequestforknowledge.wordpress.com\/2013\/02\/08\/parsing-a-csexport-generated-xml-file-into-a-scoped-csv-file\/\">http:\/\/jorgequestforknowledge.wordpress.com\/2013\/02\/08\/parsing-a-csexport-generated-xml-file-into-a-scoped-csv-file\/<\/a><\/p><\/blockquote>\n<p><!--more--><br \/>\n<code><\/code><\/p>\n<pre>#\r\n# parse-csexport.ps1\r\n#\r\n\r\n# Change the following list to get different attributes. The first four are available for all connector spaces.\r\n$csvcolumns = @(\"dn\",\"connector-type\",\"connector-state\",\"mv-guid\",\"emailAddress\",\"userName\",\"sn\",\"givenName\",\"title\",\"personalTitle\")\r\n\r\n$csvfile = \"csexport.csv\"\r\n$csexportfile = \"csexport.xml\"\r\n\r\n[xml]$csexport = get-content $csexportfile\r\n\r\nif (Test-Path $csvfile) {Remove-Item -Path $csvfile}\r\nforeach ($csvcol in $csvcolumns) {\r\n  $csvheader = $csvheader + \";\" + $csvcol\r\n}\r\nAdd-Content $csvfile $csvheader\r\n\r\nforeach ($csobj in $csexport.\"cs-objects\".\"cs-object\") {\r\n  $csobjhash = @{}\r\n  $csobjhash.Add(\"dn\",$csobj.\"cs-dn\")\r\n  # Disconnectors\r\n  if ($csobj.connector -eq \"0\") {\r\n    $csobjhash.Add(\"connector-type\",\"disconnector\")\r\n    $csobjhash.Add(\"connector-state\",$csobj.\"connector-state\")\r\n    $csobjhash.Add(\"mv-guid\",\"\") \r\n    foreach ($attr in $csobj.\"unapplied-export-hologram\".entry.attr) {\r\n      if ($attr.multivalued -eq \"false\") {\r\n        $csobjhash.Add($attr.name,$attr.value)\r\n      }\r\n    }\r\n  }\r\n  # Connectors\r\n  else {\r\n    $csobjhash.Add(\"connector-type\",\"connector\")\r\n    $csobjhash.Add(\"connector-state\",$csobj.\"connector-state\")\r\n    $csobjhash.Add(\"mv-guid\",$csobj.\"mv-link\".\"#text\") \r\n    foreach ($attr in $csobj.\"synchronized-hologram\".entry.attr) {\r\n      if ($attr.multivalued -eq \"false\") {\r\n        $csobjhash.Add($attr.name,$attr.value)\r\n      }\r\n    }\r\n  }\r\n  $csvline = \"\"\r\n  foreach ($csvcol in $csvcolumns) {\r\n    if ($csobjhash.Contains($csvcol)) {\r\n      if ($csvline -eq \"\") {$csvline = $csobjhash.Item($csvcol) }\r\n      else {$csvline = $csvline + \";\" + $csobjhash.Item($csvcol) }\r\n    }\r\n    else {$csvline = $csvline + \";\"}\r\n  }\r\n  $csvline\r\n  Add-Content $csvfile $csvline\r\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>From time to time it&#8217;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&#8217;t particularly pretty and it doesn&#8217;t open nicely in Excel. Luckily powershell&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[42,58,34,9,28,23],"tags":[],"class_list":["post-1710","post","type-post","status-publish","format-standard","hentry","category-fim-2010","category-fim-sync-service","category-ilm2007","category-logs","category-miis2003","category-powershell"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-rA","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1710","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=1710"}],"version-history":[{"count":6,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1710\/revisions"}],"predecessor-version":[{"id":2538,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1710\/revisions\/2538"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=1710"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=1710"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=1710"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}