none
Manipulating Data in Columns of CSV RRS feed

  • Question

  • So what I am doing is needing to translate some data from One AD domain to comare to Another AD domain.  But all of the back story really isn't important.  So in short what I am doing is running this following command against our current domain to create a CSV file of certain properties.  At the moment I am only grabbing the DistinguishedName and ObjectGuid of all Group objects.

    I then take the created CSV and remove the header rows from the file so it is just the raw data.

    Get-ADGroup -Filter * -Properties * | Select-Object -Property DistinguishedName,ObjectGuid | Sort-Object -Property DistinguishedName | Export-Csv -NoTypeInformation c:\temp\adgroups.csv -Force -Encoding ASCII
    (Get-Content c:\temp\adgroups.csv) | Select -Skip 1 | Set-Content C:\temp\adgroups.csv

    This result is working great but now I have another task to manipulate this data so it is readable by a third party application.  What I need to do is to only the column containing the GUIDS.  I need to remove the hyphens from GUIDs and then convert the GUID to HEX.

    For example the a GUID of ...

    77877b01-99ea-46e7-8ffc-04ec5acc7349  

    shoud be transformed into...

    017b8777ea99e7468ffc04ec5acc7349

    after removing the dashes and converting to hex.

    Thanks in advance.

    Thursday, July 9, 2015 6:18 PM

Answers

  • The function I wrote reverses the byte order of each of the hex strings in the original.

    So this means that:

    Hex String   Reversed
    77877b01   017b8777
    99ea   ea99
    46e7   e746
    8ffc   fc8f
    04ec5acc7349   4973cc5aec04

    If you don't want to reverse the last two groupings of the hex digits, then update the function to something like this:


    function Convert-Guid {
      param(
        [String] $guid
      )
      $result = ""
      $parts = $guid -split "-"
      for ( $i = 0; $i -lt $parts.Count; $i++ ) {
        if ( $i -lt 3 ) {
          $result += Reverse-HexStringBytes $parts[$i]
        }
        else {
          $result += $parts[$i]
        }
      }
      $result
    }
    

    -- Bill Stewart [Bill_Stewart]

    Friday, July 10, 2015 2:46 PM
    Moderator

All replies


  • function Reverse-HexStringBytes {
      param(
        [String] $chars
      )
      $result = ""
      for ( $n = $chars.Length - 2; $n -ge 0; $n -= 2 ) {
        $result += $chars.Substring($n, 2)
      }
      $result
    }
    
    function Convert-Guid {
      param(
        [String] $guid
      )
      $result = ""
      $parts = $guid -split "-"
      foreach ( $part in $parts ) {
        $result += Reverse-HexStringBytes $part
      }
      $result
    }
    

    With the above function definitions:


    $guid = "77877b01-99ea-46e7-8ffc-04ec5acc7349"
    Convert-Guid $guid  # output: 017b7877ea99e746fc8f4973cc5aec04
    


    -- Bill Stewart [Bill_Stewart]

    Thursday, July 9, 2015 6:51 PM
    Moderator
  • Your code works excellant.  My only issue is that your output does not meet the expected output as I had listed above.  I guess I need to look further into how this original output was generated.  The requirement for the applications is to have Powershell perform this conversion if it can so they can move away from using Apache Directory Studio to pull it from AD.

    So Both AD and ADS (apache directory studio) both display the origianl GUID as

    {77877b01-99ea-46e7-8ffc-04ec5acc7349}

    When I right click in ADS on this property and choose to copy this value as HEX is when I get

    017b8777ea99e7468ffc04ec5acc7349

    I need to replicate this function in Powershell.

    Friday, July 10, 2015 12:27 PM
  • The function I wrote reverses the byte order of each of the hex strings in the original.

    So this means that:

    Hex String   Reversed
    77877b01   017b8777
    99ea   ea99
    46e7   e746
    8ffc   fc8f
    04ec5acc7349   4973cc5aec04

    If you don't want to reverse the last two groupings of the hex digits, then update the function to something like this:


    function Convert-Guid {
      param(
        [String] $guid
      )
      $result = ""
      $parts = $guid -split "-"
      for ( $i = 0; $i -lt $parts.Count; $i++ ) {
        if ( $i -lt 3 ) {
          $result += Reverse-HexStringBytes $parts[$i]
        }
        else {
          $result += $parts[$i]
        }
      }
      $result
    }
    

    -- Bill Stewart [Bill_Stewart]

    Friday, July 10, 2015 2:46 PM
    Moderator
  • Thanks again.  I did also find another solution piecing together what seemed like hundreds of examples from all over the internet.  Here is the script I currently have that is doign the same thing as you have above.

    #Import Required Modules
    Import-Module ActiveDirectory
    
    #Function to convert GUIDs to Octect String
    #Uses Variable $Guid to pass into function
    function Convert-GuidToOctetString
    {
        param
        (
            [String]$Guid
        );
    
        [Guid]$g = New-Object Guid;
        if([Guid]::TryParse($Guid, [ref]$g))
        {
            return ([System.String]::Join('', ($g.ToByteArray() | ForEach-Object { $_.ToString('x2') })));
        }
        else
        {
            throw Exception("Input string is not a valid GUID")
        }
    
    }
    
    #Create initial file of data
    Get-ADGroup -Filter * -Properties * | Select-Object -Property DistinguishedName,ObjectGuid | Sort-Object -Property DistinguishedName | ConvertTo-Csv -NoTypeInformation | Out-File c:\temp\ADGroups.txt
    
    #Store csv in variable
    $csvstore = Import-Csv C:\temp\ADGroups.txt 
    
    #Convert ObjectGuid into Octect String
    $csvstore | ForEach-Object {$_.objectGuid = Convert-GuidToOctetString $_.ObjectGuid} 
    
    $csvstore | ConvertTo-Csv -NoTypeInformation | Out-File c:\temp\ADGroupsHex.txt

    Thanks again.
    Friday, July 10, 2015 4:05 PM