Manipulating Data in Columns of CSV

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.

July 9th, 2015 2:21pm

Free Windows Admin Tool Kit Click here and download it now
July 9th, 2015 2:53pm

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.

July 10th, 2015 8:27am

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:

Free Windows Admin Tool Kit Click here and download it now
July 10th, 2015 10:46am

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.
July 10th, 2015 12:06pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics