The Right Way…?

So normally you would do this with PowerShell right? It’s easy.

1
2
$User = Get-ADUser someone -Properties 'mS-DS-ConsistencyGuid'
[convert]::ToBase64String( ([guid]$User.'mS-DS-ConsistencyGuid').ToByteArray() )

This would output the expected string for Office365. If you aren’t using mS-DS-ConsistencyGuid you would probably use ObjectGuid here instead.

The Hard Way!

So let’s say you have your users in MySQL for some reason. Maybe you are managing 10 domains and you need to be able to do cross domain lookups quickly… caughs

Well, here is how you can convert the mS-DS-ConsistencyGuid (stored as a string) to an ImmutableID.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT
    `msDSConsistencyGuid`,
    TO_BASE64(
        CONCAT(
            REVERSE(UNHEX(SUBSTRING_INDEX(`msDSConsistencyGuid`,'-',1))),
            REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`msDSConsistencyGuid`,'-',2),'-',-1))),
            REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`msDSConsistencyGuid`,'-',3),'-',-1))),
            UNHEX(REPLACE(SUBSTRING_INDEX(`msDSConsistencyGuid`,'-',-2),'-',''))
        )
    ) AS `ImmutableId`
FROM `users` WHERE `msDSConsistencyGuid` IS NOT NULL LIMIT 1

What is happening here? Well a Microsoft GUID is stored in mixed-edian format, which means that the first three compents are incoded little-edian and the last two are bit-edian order.

Here is a GUID: 6701fa1a-fb0f-45a8-9356-d302b2affbb1

Converted to a byte array that looks like:

Component	String Value	Byte Value	Edianess
1	6701fa1a	1A FA 01 67	Little
2	fb0f	0F FB	Little
3	45a8	A8 45	Little
4	9356	93 56	Big
5	d302b2affbb1	D3 02 B2 AF FB B1	Big

Now MySQL when you UNHEX something it treats it as Big edian… if we just do something like this:

1
SELECT TO_BASE64(UNHEX(REPLACE('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',''))) AS `NOTImmutableId`

NOTImmutableId
ZwH6GvsPRaiTVtMCsq/7sQ==

We get something that looks like an ImmutableID, but it is not.

What I ended up doing is using SUBSTRING_INDEX to pull out each of the first three sections and the last two sections.

1
2
3
4
5
SELECT
    SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',1) AS `Component1`,
    SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',2),'-',-1) AS `Component2`,
    SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',3),'-',-1) AS `Component3`,
    SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',-2) AS `Component4and5`

Component1	Component2	Component3	Component4and5
6701fa1a	fb0f	45a8	9356-d302b2affbb1

On the last section I strip out the dash.

1
2
3
4
5
SELECT
    SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',1) AS `Component1`,
    SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',2),'-',-1) AS `Component2`,
    SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',3),'-',-1) AS `Component3`,
    REPLACE(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',-2),'-','') AS `Component4and5`

Component1	Component2	Component3	Component4and5
6701fa1a	fb0f	45a8	9356d302b2affbb1

UNHEX each section to get the binary values.

1
2
3
4
5
SELECT
    UNHEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',1)) AS `Component1`,
    UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',2),'-',-1)) AS `Component2`,
    UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',3),'-',-1)) AS `Component3`,
    UNHEX(REPLACE(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',-2),'-','')) AS `Component4and5`

Component1	Component2	Component3	Component4and5
0x6701fa1a	0xfb0f	0x45a8	0x9356d302b2affbb1

REVERSE the first three components to convert to little-edian.

1
2
3
4
5
SELECT
    REVERSE(UNHEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',1))) AS `Component1`,
    REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',2),'-',-1))) AS `Component2`,
    REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',3),'-',-1))) AS `Component3`,
    UNHEX(REPLACE(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',-2),'-','')) AS `Component4and5`

Component1	Component2	Component3	Component4and5
0x1afa0167	0x0ffb	0xa845	0x9356d302b2affbb1

CONCAT the whole thing and convert TO_BASE64 and we are done!

1
2
3
4
5
6
SELECT TO_BASE64(CONCAT(
    REVERSE(UNHEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',1))),
    REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',2),'-',-1))),
    REVERSE(UNHEX(SUBSTRING_INDEX(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',3),'-',-1))),
    UNHEX(REPLACE(SUBSTRING_INDEX('6701fa1a-fb0f-45a8-9356-d302b2affbb1','-',-2),'-',''))
)) AS `ImmutableID`

ImmutableID
GvoBZw/7qEWTVtMCsq/7sQ==

Now what?

You could create a generated column to generate your ImmutableId values and always have the latest value ready to go. Or just write a tiny bit of PowerShell and forget you ever saw this…

Converting from ImmutableID to GUID string

Just in case you need to go the other way…

1
2
3
4
5
6
7
SELECT CONCAT(
    LOWER(HEX(REVERSE(UNHEX(SUBSTRING(HEX(FROM_BASE64( 'GvoBZw/7qEWTVtMCsq/7sQ==' )),1,8))))),'-',
    LOWER(HEX(REVERSE(UNHEX(SUBSTRING(HEX(FROM_BASE64( 'GvoBZw/7qEWTVtMCsq/7sQ==' )),9,4))))),'-',
    LOWER(HEX(REVERSE(UNHEX(SUBSTRING(HEX(FROM_BASE64( 'GvoBZw/7qEWTVtMCsq/7sQ==' )),13,4))))),'-',
    LOWER(SUBSTRING(HEX(FROM_BASE64( 'GvoBZw/7qEWTVtMCsq/7sQ==' )),17,4)),'-',
    LOWER(SUBSTRING(HEX(FROM_BASE64( 'GvoBZw/7qEWTVtMCsq/7sQ==' )),21))
) AS `mSDSConsistencyGuid`

mSDSConsistencyGuid
6701fa1a-fb0f-45a8-9356-d302b2affbb1

The Right Way…?#

The Hard Way!#

Now what?#

Converting from ImmutableID to GUID string#

The Right Way…?

The Hard Way!

Now what?

Converting from ImmutableID to GUID string