Retention: Which Retention Policy Tag is applied to message - Get-StoreQuery way
In this post I'll explain how can you query store and check which Retention Policy Tag is applied to message. This is extremly useful if you don't have access to users mailbox / Outlook etc... Next posts ( when I get some time ) will address this using EWS.
Problem statement: Check which retention policy tag is applied to messages in "MyStuff" folder for mailbox "adias".
First you should load ManagedStoreDiagnosticFunctions.ps1 into memory. That script contains Get-StoreQuery function that will be use through this post.
[PS] C:\>cd $exscripts
[PS] C:\Program Files\Microsoft\Exchange Server\V15\scripts>. .\ManagedStoreDiagnosticFunctions.ps1
[PS] C:\Program Files\Microsoft\Exchange Server\V15\scripts>
Since first parameter of Get-StoreQuery is name of the database we are querying, we need to find out where mailbox is located:
[PS] C:\>Get-Mailbox adias | ft database, name -AutoSize
Database Name
-------- ----
DB01 Dias Ana
Next thing we need to find out is MailboxNumber within database since this will help us get to "MyStuff" folder:
[PS] C:\>Get-StoreQuery -Database DB01 -Query "select MailboxNumber from [DB01].Mailbox where DisplayName='Dias Ana'" | ft -AutoSize
MailboxNumber
-------------
134
Now that we know MailboxNumber within database we need to find folder where messages of interest are located:
[PS] C:\>Get-StoreQuery -Database DB01 -Query "select FolderId, DisplayName from Folder where MailboxPartitionNumber=134"
FolderId DisplayName
-------- -----------
0xE06990A3341D4C44B472B05AAFD8E81C00000000010800000100 Top of Information Store
0xE06990A3341D4C44B472B05AAFD8E81C00000000010900000100 Sent Items
0xE06990A3341D4C44B472B05AAFD8E81C00000000010A00000100 Deleted Items
0xE06990A3341D4C44B472B05AAFD8E81C00000000010B00000100 Outbox
0xE06990A3341D4C44B472B05AAFD8E81C00000000010C00000100 Inbox
0xE06990A3341D4C44B472B05AAFD8E81C00000000010D00000100 Calendar
0xE06990A3341D4C44B472B05AAFD8E81C00000000010E00000100 Contacts
0xE06990A3341D4C44B472B05AAFD8E81C00000000010F00000100 Drafts
0xE06990A3341D4C44B472B05AAFD8E81C00000000011000000100 Journal
0xE06990A3341D4C44B472B05AAFD8E81C00000000011100000100 Notes
0xE06990A3341D4C44B472B05AAFD8E81C00000000011200000100 Tasks
0xE06990A3341D4C44B472B05AAFD8E81C00000000011300000100 Recoverable Items
0xE06990A3341D4C44B472B05AAFD8E81C00000000011400000100 Deletions
0xE06990A3341D4C44B472B05AAFD8E81C00002987902E00000100 MyStuff
Finally get to the message:
[PS] C:\>$message = Get-StoreQuery -Database DB01 -Query "select * from Message where MailboxPartitionNumber=134 and FolderId=0xE06990A3341D4C44B472B05AAFD8E81C00002987902E00000100"
Message contains "PropertyBlob" property which is stored in Hex format and contains all message properties:
[PS] C:\>$message.PropertyBlob
0x50726F5000033600080823000000000050504100B80100004848420050020000505043005F02000048484400F6020000080857000100000008085
8000000000048486400000300004848650004030000484870007F0300005050190C8E03000048481A0C2604000048481E0C3504000048481F0C3904
00004848020EB40400004848030EB504000048481D0EB604000050504D0EC504000050504E0EE30400005050580E01050000484835101F050000505
00B304605000050501430580500005050183066050000505019307805000050501B308A05000038381C309805000018181D300000000018181E3041
00000038381F30A10500000808403A010000001818DE3FAF6F00001818F13F090400004848F83FAA0500005050F93FB90500004848FA3F510600005
050FB3F5B060000181819403D02000018181A403D02000018181C401100000048482340F2060000484825406D07000048483040E707000048483140
F607000048483840050800004848394014080000181859403D02000018185A407D0600004848015D1E0800004848025D3D0800004848E55F5C08000
01818C6650200000008081880010000000808298000000000519600000000DCA740C8C042101AB4B908002B2FE18201000000000000002F6F3D494E
542F6F753D45786368616E67652041646D696E6973747261746976652047726F7570202846594449424F484632335350444C54292F636E3D5265636
97069656E74732F636E3D64393036373263323134636334306434396437616136656665353361393134302D41646D696E6973747261746F72004D0D
41646D696E6973747261746F72519500000000DCA740C8C042101AB4B908002B2FE18201000000000000002F4F3D494E542F4F553D45584348414E4
7452041444D494E4953545241544956452047524F5550202846594449424F484632335350444C54292F434E3D524543495049454E54532F434E3D36
303936453838354132303534444433394343443034444135423539353730372D4D532E204449415320414E41004D084469617320416E614D0245584
D792F4F3D494E542F4F553D45584348414E47452041444D494E4953545241544956452047524F5550202846594449424F484632335350444C54292F
434E3D524543495049454E54532F434E3D44393036373243323134434334304434394437414136454645353341393134302D41444D494E495354524
1544F524D0D526574656E74696F6E20746167519600000000DCA740C8C042101AB4B908002B2FE18201000000000000002F6F3D494E542F6F753D45
786368616E67652041646D696E6973747261746976652047726F7570202846594449424F484632335350444C54292F636E3D526563697069656E747
32F636E3D64393036373263323134636334306434396437616136656665353361393134302D41646D696E6973747261746F72004D0D41646D696E69
73747261746F724D0245584D792F4F3D494E542F4F553D45584348414E47452041444D494E4953545241544956452047524F5550202846594449424
F484632335350444C54292F434E3D524543495049454E54532F434E3D44393036373243323134434334304434394437414136454645353341393134
302D41444D494E4953545241544F5248484D0D526574656E74696F6E20746167511C01050000000000051500000096D60630CB490B714D4AA0AEF40
10000511C01050000000000051500000096D60630CB490B714D4AA0AEF4010000511C01050000000000051500000096D60630CB490B714D4AA0AEF4
0100004D253C313435363439323933313834372E32303738374069742D656E67696E6565722E6F72673E511071AB2CD588B69D47AB665CEEBF0C71C
0510C03000000578DDE05FC4E2B9E511023F7FB3A698F3D4A8FDF7254278DD560511023F7FB3A698F3D4A8FDF7254278DD560510C01000000802D67
C89870D10138DB823D8FACA3D1013880ED408DACA3D1014D0D41646D696E6973747261746F72519600000000DCA740C8C042101AB4B908002B2FE18
201000000000000002F6F3D494E542F6F753D45786368616E67652041646D696E6973747261746976652047726F7570202846594449424F48463233
5350444C54292F636E3D526563697069656E74732F636E3D64393036373263323134636334306434396437616136656665353361393134302D41646
D696E6973747261746F72004D084469617320416E61519500000000DCA740C8C042101AB4B908002B2FE18201000000000000002F4F3D494E542F4F
553D45584348414E47452041444D494E4953545241544956452047524F5550202846594449424F484632335350444C54292F434E3D5245434950494
54E54532F434E3D36303936453838354132303534444433394343443034444135423539353730372D4D532E204449415320414E41004D792F4F3D49
4E542F4F553D45584348414E47452041444D494E4953545241544956452047524F5550202846594449424F484632335350444C54292F434E3D52454
3495049454E54532F434E3D44393036373243323134434334304434394437414136454645353341393134302D41444D494E4953545241544F524D78
2F4F3D494E542F4F553D45584348414E47452041444D494E4953545241544956452047524F5550202846594449424F484632335350444C54292F434
E3D524543495049454E54532F434E3D36303936453838354132303534444433394343443034444135423539353730372D4D532E204449415320414E
414D0D41646D696E6973747261746F724D0D41646D696E6973747261746F724D0D41646D696E6973747261746F724D084469617320416E614D1D416
46D696E6973747261746F724069742D656E67696E6565722E6F72674D1D41646D696E6973747261746F724069742D656E67696E6565722E6F72674D
0120
Not much of help, but there is function (ParsePropertyBlob) within database itself which takes PropertyBlob attribute and gives you human readeable property definition. Imagine as a stored procedure for SQL:
[PS] C:\>Get-StoreQuery -Database DB01 -Query "select * from ParsePropertyBlob($($message.PropertyBlob))" | ft -AutoSize
PropertyTag PropertyName PropertyType PropertyValue
----------- ------------ ------------ -------------
0023:Boolean 00020328-0000-0000-c000-000000000046:N:0x00000023 Boolean False
0041:Binary 00020328-0000-0000-c000-000000000046:N:0x00000041 Binary 0x00000000DCA740C8C042101AB4B908002B2FE1...
0042:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000042 Unicode Administrator
0043:Binary 00020328-0000-0000-c000-000000000046:N:0x00000043 Binary 0x00000000DCA740C8C042101AB4B908002B2FE1...
0044:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000044 Unicode Dias Ana
0057:Boolean 00020328-0000-0000-c000-000000000046:N:0x00000057 Boolean True
0058:Boolean 00020328-0000-0000-c000-000000000046:N:0x00000058 Boolean False
0064:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000064 Unicode EX
0065:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000065 Unicode /O=INT/OU=EXCHANGE ADMINISTRATIVE GROUP ...
0070:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000070 Unicode Retention tag
0C19:Binary 00020328-0000-0000-c000-000000000046:N:0x00000C19 Binary 0x00000000DCA740C8C042101AB4B908002B2FE1...
0C1A:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000C1A Unicode Administrator
0C1E:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000C1E Unicode EX
0C1F:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000C1F Unicode /O=INT/OU=EXCHANGE ADMINISTRATIVE GROUP ...
0E02:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000E02 Unicode
0E03:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000E03 Unicode
0E1D:Unicode 00020328-0000-0000-c000-000000000046:N:0x00000E1D Unicode Retention tag
0E4D:Binary 00020328-0000-0000-c000-000000000046:N:0x00000E4D Binary 0x01050000000000051500000096D60630CB490B...
0E4E:Binary 00020328-0000-0000-c000-000000000046:N:0x00000E4E Binary 0x01050000000000051500000096D60630CB490B...
0E58:Binary 00020328-0000-0000-c000-000000000046:N:0x00000E58 Binary 0x01050000000000051500000096D60630CB490B...
1035:Unicode 00020328-0000-0000-c000-000000000046:N:0x00001035 Unicode <1456492931847.20787@domain.org>
300B:Binary 00020328-0000-0000-c000-000000000046:N:0x0000300B Binary 0x71AB2CD588B69D47AB665CEEBF0C71C0
3014:Binary 00020328-0000-0000-c000-000000000046:N:0x00003014 Binary 0x03000000578DDE05FC4E2B9E
3018:Binary 00020328-0000-0000-c000-000000000046:N:0x00003018 Binary 0x23F7FB3A698F3D4A8FDF7254278DD560
3019:Binary 00020328-0000-0000-c000-000000000046:N:0x00003019 Binary 0x23F7FB3A698F3D4A8FDF7254278DD560
301B:Binary 00020328-0000-0000-c000-000000000046:N:0x0000301B Binary 0x01000000802D67C89870D101
301C:SysTime 00020328-0000-0000-c000-000000000046:N:0x0000301C SysTime 2016-05-01 13:22:50.3330523
301D:Int32 00020328-0000-0000-c000-000000000046:N:0x0000301D Int32 0
301E:Int32 00020328-0000-0000-c000-000000000046:N:0x0000301E Int32 65
301F:SysTime 00020328-0000-0000-c000-000000000046:N:0x0000301F SysTime 2016-05-01 13:22:47.0000000
3A40:Boolean 00020328-0000-0000-c000-000000000046:N:0x00003A40 Boolean True
3FDE:Int32 00020328-0000-0000-c000-000000000046:N:0x00003FDE Int32 28591
3FF1:Int32 00020328-0000-0000-c000-000000000046:N:0x00003FF1 Int32 1033
3FF8:Unicode 00020328-0000-0000-c000-000000000046:N:0x00003FF8 Unicode Administrator
3FF9:Binary 00020328-0000-0000-c000-000000000046:N:0x00003FF9 Binary 0x00000000DCA740C8C042101AB4B908002B2FE1...
3FFA:Unicode 00020328-0000-0000-c000-000000000046:N:0x00003FFA Unicode Dias Ana
3FFB:Binary 00020328-0000-0000-c000-000000000046:N:0x00003FFB Binary 0x00000000DCA740C8C042101AB4B908002B2FE1...
4019:Int32 00020328-0000-0000-c000-000000000046:N:0x00004019 Int32 573
401A:Int32 00020328-0000-0000-c000-000000000046:N:0x0000401A Int32 573
401C:Int32 00020328-0000-0000-c000-000000000046:N:0x0000401C Int32 17
4023:Unicode 00020328-0000-0000-c000-000000000046:N:0x00004023 Unicode /O=INT/OU=EXCHANGE ADMINISTRATIVE GROUP ...
4025:Unicode 00020328-0000-0000-c000-000000000046:N:0x00004025 Unicode /O=INT/OU=EXCHANGE ADMINISTRATIVE GROUP ...
4030:Unicode 00020328-0000-0000-c000-000000000046:N:0x00004030 Unicode Administrator
4031:Unicode 00020328-0000-0000-c000-000000000046:N:0x00004031 Unicode Administrator
4038:Unicode 00020328-0000-0000-c000-000000000046:N:0x00004038 Unicode Administrator
4039:Unicode 00020328-0000-0000-c000-000000000046:N:0x00004039 Unicode Dias Ana
4059:Int32 00020328-0000-0000-c000-000000000046:N:0x00004059 Int32 573
405A:Int32 00020328-0000-0000-c000-000000000046:N:0x0000405A Int32 1661
5D01:Unicode 00020328-0000-0000-c000-000000000046:N:0x00005D01 Unicode Administrator@domain.com
5D02:Unicode 00020328-0000-0000-c000-000000000046:N:0x00005D02 Unicode Administrator@domain.com
5FE5:Unicode 00020328-0000-0000-c000-000000000046:N:0x00005FE5 Unicode
65C6:Int32 00020328-0000-0000-c000-000000000046:N:0x000065C6 Int32 2
8018:Boolean 00000000-0000-0000-0000-000000000000:N:0xFFFFFFFF Boolean True
8029:Boolean 00000000-0000-0000-0000-000000000000:N:0xFFFFFFFF Boolean False
There is lot of properties on message, but we are interested in this case only on 0x3019 (PidTagPolicyTag ) and that property contains following value:
3019:Binary 00020328-0000-0000-c000-000000000046:N:0x00003019 Binary 0x23F7FB3A698F3D4A8FDF7254278DD560
That hex string is GUID representation of policy tag that is stamped on message. So convert that hex value to guid and get policy tag:
Get-GuidFromHex -HexString '23F7FB3A698F3D4A8FDF7254278DD560' -LittleEndian
3AFBF723-8F69-4A3D-8FDF-7254278DD560
[PS] C:\>Get-RetentionPolicyTag 3AFBF723-8F69-4A3D-8FDF-7254278DD560
Name Type Description
---- ---- -----------
Personal 60 Day Archive Personal Managed Content Settings
Get-GuidFromHex function can be found on following link.