If you have enabled bitlocker encryption on your system there are circumstances when it may come up with errors and ask for the recovery keys to the encrypted volume. You can sometimes get these from your Microsoft Account if you are using a Microsoft Account to login to your system. Alternatively in a business office or datacenter you can setup a Key Management server to manage these keys. However it is still a good idea to grab another copy and back it up to a secure location. In smaller environments this may be as simple as sending it to a secured share and backing that up to a secure location or even putting it on a thumb drive and storing that in a firebox/firesafe. But how do you access these keys. There are several ways.
Access the Keys from your Microsoft Account if you are using one. However if you are a business then you are likely using a local account or a domain account.
Access the keys from your Domain Controller Active Directory. Under {domain}->{Computers}->{Computer Name} and Properties there is a Bitlocker tab with the keys.
Use a powershell script to access the keys. The script will need to be run with administrator privileges. This option is what this post is primarily about.
The code below may be saved to a script GatherBitLockerRecoveryKeys.ps1. This script will loop through each partition on a system and list its recovery keys to a file. The BasePath should be set to a secure location. Obviously in this example it is pointing to the C drive which in all likelihood is encrypted and therefore would be of no use in an emergency since you would have chicken and egg scenario where you couldn’t get to you recovery keys if an issue occurred.
The previous two posts covered how to extract numbers and then letters from a cell. This post brings it altogether into a function that can convert a number of bytes with a unit to gigabytes. In the past I have done this with VBA macro functions such as the one inserted below. The issue with using VBA macros is it can limit the portability of a spreadsheet. Since hackers started using VBA macros to write malware and spyware many organizations have put policies in place that block the execution of VBA macros or require macros signatures registering the functions to the organization. These limit the ability of a spreadsheet to be distributed and used without significant steps to get approvals and registrations within a company’s policies.
VBA GetNumber Function (Select Arrow to Open Below)
Function GetNumber(rWhere As Variant, Optional Accept_Decimals As Boolean, Optional Accept_Negative As Boolean) As String
Dim CharPostion As Integer, i As Integer, StringLength As Integer
Dim sText As String, mchNeg As String, mchDec As String
Dim ThisNum As String
Dim vChar, vChar2
mchNeg = vbNullString
mchDec = vbNullString
Select Case TypeName(rWhere)
Case Is = "Range"
' Get the text from the supplied range
sText = Trim(rWhere.Text)
Case Else
sText = Trim(rWhere)
End Select
If Accept_Decimals = True Then
mchDec = "."
End If
If Accept_Negative = True Then
mchNeg = "-"
End If
StringLength = Len(sText)
For CharPostion = StringLength To 1 Step -1
vChar = Mid(sText, CharPostion, 1)
If IsNumeric(vChar) Or vChar = mchNeg Or vChar = mchDec Then
i = i + 1
ThisNum = Mid(sText, CharPostion, 1) & ThisNum
If IsNumeric(ThisNum) Then
If CDbl(ThisNum) < 0 Then Exit For
Else
ThisNum = Replace(ThisNum, Left(ThisNum, 1), "", , 1)
End If
End If
If i = 1 And ThisNum <> vbNullString Then ThisNum = CDbl(Mid(ThisNum, 1, 1))
Next CharPostion
'GetNumber = CDbl(ThisNum)
GetNumber = ThisNum
End Function
VBA ByteToGB Function (Select Arrow to Open Below)
Function BytesToGb(Where As Range) As Double
Dim NumMB As Double
' Get the text from the supplied range
strWhere = Trim(Where.Text)
'Extract the number and unit from the text
'NumPart = CDbl(Mid(strWhere, 1, (InStr(1, strWhere, "b", vbTextCompare) - 2)))
NumPart = GetNumber(Where, True, True)
NumUnit = LCase(Trim(Replace(Where, NumPart, "")))
'Mid(strWhere, InStr(1, strWhere, "b", vbTextCompare) - 1, Len(strWhere) - (InStr(1, strWhere, "b", vbTextCompare) - 2))
'Use unit to convert the number part to MB value
Select Case NumUnit
Case "kb", "k"
NumMB = NumPart / 1024 ^ 2
Case "mb", "m"
NumMB = NumPart / 1024
Case "gb", "g"
NumMB = NumPart
Case "tb", "t"
NumMB = NumPart * 1024
Case Else
NumMB = NumPart / 1024
End Select
' Return the MBs
BytesToGb = CDbl(NumMB)
End Function
Rather than use a VBA macro function like the one above since with newer versions of Excel we now have more available array functions this can be done using the formulae discussed in the previous two posts combined into a named lambda function.
For the explanation of the formulas on either side of the /10^ please see the pervious two posts. So let’s make it into a function using the Name Manager under formulas with the Lambda function. You may be familiar with creating named ranges. Under the Formula menu item in Excel you can select a range of cells and choose define name so that you can refer to it else where. But what you can also do is create a name that behaves like a function. Open Name Manager and add a name BytesToGB and paste into the reference field the formula above. Then you can reuse this formula against any reference just by using =BytesToGB(Ref).
In this function we divide the number extracted from the cell by the number created when we match the letters extracted to the array {“P”,”T”,”G”,”M”,”K”} subtracting 3. So for Petabytes 1 minus 3 would equal negative 2 time 3 would equal negative 6. Thus dividing a number by 10 raised to the power of -6 would add six zeros to it arriving at the number of gigabytes. Similarly for Terabytes 2 minus 3 would equal -1 multipled by 3 would be -3 and would result in 3 zeros or decimal places being added to the number converting it to gigabytes.
DCA Wired 