FileVault Key Reveal MySQL Query (Verification Testing)

[henryxyz]

Looking for validation from folks on Jamfnation. I have a MySQL query that will show who has accessed/revealed the FileVault key. The query will return who revealed the key, when they revealed the key, and the JSS computerID. Hoping others can test the query to make sure it is catching every permutation of revealing the FileVault key. Revealing in this context means the person went to the device and looked at the FileVault key. Not looking to identify when FileVault was updated or placed into the database. Also not looking to find out the key itself, since it is encrypted in the database. The query was written on MySQL 5.6 on Linux. It should work on Windows, but you have to change the file location.

Below are multiple variations for the same MySQL query in case you want to test for your circumstance. You have to modify the query for your MySQL username, MySQL password, and location to save your file. Assumption you have sufficient rights in MySQL and Linux to query to get the data and write to a file. I recommend trying this in your labs or QA. It is only a select command, so it is not destructive in anyway, but testing in non-production environments is always best practice.

Save query result to file with commas.

mysql -u <MySQLaccount> -p<PassWord> -e "select audit_who as 'Changed By',from_unixtime(audit_when/1000) as 'Change Date', primary_object_id as 'Computer ID' from jamfsoftware.jss_audit where (audit_what_class_name = 'FileVault2ComputerKey') and (audit_where = 'Read (CRUD Level)') and (primary_object_type = '105') order by 'audit_when';" | tr ' ' ',' | column -t > /<file>/<location>/localfile.txt

HTML: Save query result to file as HTML. (-H)

mysql -u <MySQLaccount> -p<PassWord> -H -e "select audit_who as 'Changed By',from_unixtime(audit_when/1000) as 'Change Date', primary_object_id as 'Computer ID' from jamfsoftware.jss_audit where (audit_what_class_name = 'FileVault2ComputerKey') and (audit_where = 'Read (CRUD Level)') and (primary_object_type = '105') order by 'audit_when';"> /<file>/<location>/localfile.htm

XML: Will save query result to file as XML. (-X)

mysql -u <MySQLaccount> -p<PassWord> -X -e "select audit_who as 'Changed By',from_unixtime(audit_when/1000) as 'Change Date', primary_object_id as 'Computer ID' from jamfsoftware.jss_audit where (audit_what_class_name = 'FileVault2ComputerKey') and (audit_where = 'Read (CRUD Level)') and (primary_object_type = '105') order by 'audit_when';" > /<file>/<location>/localfile.xml

Comma Separated Using MySQL to parse.

mysql -u <MySQLaccount> -p<PassWord> -e "select audit_who as 'Changed By',from_unixtime(audit_when/1000) as 'Change Date', primary_object_id as 'Computer ID' from jamfsoftware.jss_audit where (audit_what_class_name = 'FileVault2ComputerKey') and (audit_where = 'Read (CRUD Level)') and (primary_object_type = '105') order by 'audit_when' INTO OUTFILE '/<File>/<Location>/filevault.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';"

If you want to specify what time period to grab data, you can add another condition for audit_when specified in EPOCH time. You can search Bing or Google for an EPOCH converters and put in the date or time you want to use. Make sure you include the EPOCH time with milliseconds. The example below use 1540857720000, which is October 30th.

Using additional condition of when you want to collect from using Epoch time in milleseconds.

mysql -u <MySQLaccount> -p<PassWord> -e "select audit_who as 'Changed By',from_unixtime(audit_when/1000) as 'Change Date',primary_object_id as 'Computer ID' from jamfsoftware.jss_audit where (audit_what_class_name = 'FileVault2ComputerKey') and (audit_where = 'Read (CRUD Level)') and (primary_object_type = '105') and (audit_when > 1540857720000) order by 'audit_when' INTO OUTFILE '/<File>/<Location>/filevault.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';"

Let me know if data returned equals the reveals that occurred. Thanks

[henryxyz]

Doh! Correct query should use child_object_id instead of primary_object_id. Will not repost, but you will need to change the queries above. Mea culpa!

[Sonic84]

Thank you very much for posting this! I asked jamf support for something similar and got no response.

[donmontalvo]

@henryxyz not sure if you saw @dan-snelson's recent post:

https://www.jamf.com/jamf-nation/discussions/35550/jamf-pro-admins-with-view-disk-encryption-recover...