We often run into laptops & desktop machines that don’t connect to corporate Active directory and are purged from AD after the retention period has expired. This usually accord when a machine is returned by a employee that has left the company & the manager places the machine into a draw & forgotten about until data off the laptop needs to be recovered, making it difficult or impossible to access the data. To address this problem, Its possible to use Power shell to export all of the Bitlocker keys periodically & store them in a file or into a SQL Database.
The simple command to scan AD for all machines and collect their BitLocker Keys is:
If you get an error that "Get-ADComputer" is missing, you will need to import the ActiveDirectory Module: "Import-Module -Name ActiveDirectory".
This command will also output some basic metadata for each machine including OS version, Description, Location, Computer AD account creation date, & last logged in date. This can be piped into a text file for future reference. The command does require the user to have administrative access to Active directory. Non-Admin account will not be able to read the data stored in AD machine accounts. The Bitlocker Key will be under the property “msFVE-RecoveryPassword”.
To Archive to a SQL table, please review the following powershell script:
Staff Writer ## SQL Server connection info. Using SSPI -Single Signon credentials.
$SQLServer = "ServerName\Instance" ## SQL Server Name & SQL Instance Name
$SQLDB = "SQLDBName" ## SQL Database Name
$SQLTBL = "ADMachineInventory" ##SQL table Name
$connectionString = "Data Source=$SQLServer; " + "Integrated Security=SSPI; " + "Initial Catalog=$SQLDB"
$connection = new-object system.data.SqlClient.SQLConnection($connectionString)
$connection.Open()
$mcnt = 0 ##Count number of machines scanned
##mlist list of all computers accounts available in Active Directory
$mlist = Get-ADComputer -filter * -Properties lastlogontimestamp, LastLogonDate, AccountExpirationDate, \
accountExpires, Description, whenCreated, whenChanged, PrimaryGroup, OperatingSystem, \
OperatingSystemVersion, OperatingSystemServicePack, Enabled, IPv4Address, IPv6Address, \
LockedOut, Location, msFVE-RecoveryPassword, ms-Mcs-AdmPwd
ForEach ($mach in $mlist) {
# See if machine already exists in the SQL table, we'll update the record if it already exists
$SQLQUERY = "SELECT MachineName From $SQLDB..$SQLTBL Where MachineName='$($mach.Name)'"
Write-Host -f Yellow "SQLQUERY: $SQLQUERY"
$SQLCMD = New-Object System.Data.SQLClient.SQLCommand
$SQLCMD.Connection = $Connection
$SQLCMD.CommandText = $SQLQuery
$SQLReader = $SQLCMD.ExecuteReader()
$SQLResult = $null
while ($SQLReader.Read()) {
$SQLResult = $SQLReader.GetValue(0)
Write-Host -f Yellow "SQLResult: $SQLResult"
} ##EndOf while ($SQLReader.Read())
$SQLReader.Close()
if ($SQLResult -eq $null) { ## new Machine not in SQL TABLE - We'll insert a new record
$bitLock = Get-ADObject -Filter 'objectClass -eq "msFVE-RecoveryInformation"' -SearchBase \
$($mach.DistinguishedName) -Properties msFVE-RecoveryPassword
##Query local machine Windows Event Log to get the last logon username
$LastUserLogin = $null
##Use WMI to connect to machine to get the last logon user
$LastUserArray = Get-WinEvent -Computer $($mach.Name) -FilterHashtable @{Logname='Security';ID=4672} \
-MaxEvents 5| select @{N='User';E={$_.Properties[1].Value}}
ForEach ($lul in $LastUserArray) {
$LUN = $($lul.User)
##Write-Host -f Yellow "LUL: $LUN :: $($LUN.SubString($LUN.length - 1, 1))"
if ($LUN.length -gt 4) {
if ($($LUN.SubString($LUN.length - 1, 1)) -ne "$") {
$LastUserLogin = $LUN
##Write-Host -f Yellow "LastUserLogin: $LastUserLogin"
} ##EndOf if ($lul.SubString($lul.length - 1, 1) -ne "$")
} ##EndOf if ($lul.length > 4)
} ##EndOf ForEach ($lul in $LastUserArray)
Write-Host -f Yellow "Bitlocker: $($bitLock."msFVE-RecoveryPassword")"
##Inserting new record in SQL DB
$SQLQUERY = "insert into $SQLDB..$SQLTBL (MachineName, InsertDate, UpdateDate, DNShostname, FQDN, \
LastLogonDate, accountExpires, whenCreated, whenChanged, Description, OperatingSystem, \
OperatingSystemVersion, OperatingSystemSP, Enabled, IPv4Address, IPv6Address, LockedOut, \
Location, BitLockerKey, AdminPW, LastLogonUser) VALUES ('$($mach.Name)', GETDATE(), \
GETDATE(),'$($mach.DNSHostName)','$($mach.DistinguishedName)','$($mach.LastLogonDate)', \
'$($mach.AccountExpirationDate)','$($mach.whenCreated)','$($mach.whenChanged)',\
'$($mach.Description)','$($mach.OperatingSystem)', \
'$($mach.OperatingSystemOperatingSystemVersion)', \
'$($mach.OperatingSystemServicePack)', \
'$($mach.Enabled)','$($mach.IPv4Address)','$($mach.IPv6Address)', \
'$($mach.LockedOut)','$($mach.Location)', \
'$($bitLock."msFVE-RecoveryPassword")','$($mach."ms-Mcs-AdmPwd")', \
'$LastUserLogin' )"
Write-Host -f Yellow "SQLQUERY: $SQLQUERY"
$SQLCMD.CommandText = $SQLQuery
$SQLCMD.ExecuteNonQuery()
} ##EndOf if ($SQLResult -eq $null)
if ($SQLResult -ne $null) {
$bitLock = Get-ADObject -Filter 'objectClass -eq "msFVE-RecoveryInformation"' -SearchBase $($mach.DistinguishedName) -Properties msFVE-RecoveryPassword
$LastUserLogin = $null
$LastUserArray = Get-WinEvent -Computer $($mach.Name) -FilterHashtable @{Logname='Security';ID=4672} -MaxEvents 5| select @{N='User';E={$_.Properties[1].Value}}
ForEach ($lul in $LastUserArray) {
$LUN = $($lul.User)
##Write-Host -f Yellow "LUL: $LUN :: $($LUN.SubString($LUN.length - 1, 1))"
if ($LUN.length -gt 4) {
if ($($LUN.SubString($LUN.length - 1, 1)) -ne "$") {
$LastUserLogin = $LUN
##Write-Host -f Yellow "LastUserLogin: $LastUserLogin"
} ##EndOf if ($lul.SubString($lul.length - 1, 1) -ne "$")
} ##EndOf if ($lul.length > 4)
} ##EndOf ForEach ($lul in $LastUserArray)
$SQLQUERY = "Update $SQLDB..$SQLTBL Set UpdateDate=GETDATE(),LastLogonDate= \
'$($mach.LastLogonDate)',accountExpires='$($mach.AccountExpirationDate)', \
whenChanged='$($mach.whenChanged)',Description='$($mach.Description)', \
Enabled='$($mach.Enabled)',IPv4Address='$($mach.IPv4Address)', \
IPv6Address='$($mach.IPv6Address)',LockedOut='$($mach.LockedOut)', \
Location='$($mach.Location)',BitLockerKey='$($bitLock."msFVE-RecoveryPassword")', \
AdminPW='$($mach."ms-Mcs-AdmPwd")', LastLogonUser='$LastUserLogin' \
Where MachineName='$($mach.Name)'"
Write-Host -f Yellow "SQLQUERY: $SQLQUERY"
$SQLCMD.CommandText = $SQLQuery
$SQLCMD.ExecuteNonQuery()
} ##Endof if ($SQLResult -ne $null)
Write-Host -f Green "Machine: $($mach.Name)"
$mcnt = $mcnt + 1 ##Count number of machines scanned
} ##endof ForEach ($mach in $mlist)
## End of AD Scann process
write-Host "Machines Fount: $mcnt"
$Connection.Close()
Below is the SQL Table Defination:
Create Table ADMachineInventory (
MachineName varchar(256),
InsertDate varchar(384),
UpdateDate datetime,
DNShostname datetime,
FQDN datetime,
LastLogonDate datetime,
accountExpires varchar(384),
whenCreated varchar(256),
whenChanged varchar(256),
Description varchar(256),
OperatingSystem varchar(128),
OperatingSystemVersion varchar(128),
OperatingSystemSP varchar(128),
Enabled varchar(128),
IPv4Address varchar(384),
IPv6Address varchar(256),
LockedOut varchar(256),
Location varchar(256),
Manufacturer varchar(256),
MfgModel varchar(128),
BitLockerKey datetime,
AdminPW varchar(256),
LastLogonUser varchar(128),
LastLogonUserDate datetime)