SQL query

[RaulSantos]

I need a SQL query to pull computer name and serial number plus "Legacy FileVault Enabled" but i can't figure out what table has the "Legacy FileVault Enabled" yes/no information.

[bentoms]

@RaulSantos, rather than SQL query... Have you looked at the API?

I'm not fluent on it myself, but am sure @mm2270 will post an awesome solution soon.

[mm2270]

Heh. I'd love to help, but I'm not 100% sure what you mean by "Legacy FileVault Enabled" I mean, I know what that refers to, but I don't see anything like that as an inventory item in our JSS 8.73, nor in in our 9.22 dev setup. Is it perhaps called something else? Or maybe I'm just blind (possible) If you can direct me to where you would see this in the inventory record in the JSS, I may be able to help you with a script to pull that value.

[RaulSantos]

am on 9.21 if you look at a computer record and >local users accounts you will see it there not sure what it would be called on the table @mm2270

[mm2270]

@RaulSantos I see what you're talking about now, but I'm not clear where that lives in the API. I looked up a system and although I see that Legacy FileVault Enabled column, all with "No" for each account, when I pull up the API record for that same Mac, I don't see xml tags for that data. Maybe the sheer absence of any tags in the API record indicates that its not enabled? I'm just not sure. Here's an example from our 9.22 setup (actual names and UIDs changed)

<local_accounts>
    <user>
        <name>localadmin</name>
        <realname>Local Admin</realname>
        <uid>501</uid>
        <home>/Users/localadmin</home>
        <home_size>-1MB</home_size>
        <home_size_mb>-1</home_size_mb>
        <administrator>true</administrator>
        <filevault_enabled>false</filevault_enabled>
    </user>
    <user>
        <name>jdoe</name>
        <realname>Doe, John</realname>
        <uid>1000111222</uid>
        <home>/Users/jdoe</home>
        <home_size>-1MB</home_size>
        <home_size_mb>-1</home_size_mb>
        <administrator>true</administrator>
        <filevault_enabled>true</filevault_enabled>
    </user>
    <user>
        <name>john</name>
        <realname>John</realname>
        <uid>502</uid>
        <home>/Users/john</home>
        <home_size>-1MB</home_size>
        <home_size_mb>-1</home_size_mb>
        <administrator>true</administrator>
        <filevault_enabled>false</filevault_enabled>
    </user>
</local_accounts>

The filevault_enabled items you see for each account are for FileVault 2, not Legacy FileVault. In short, I don't see the older info listed for each account. Perusing the whole JSS record, I don't see it listed anywhere else either. As such, I'm not sure how you'd query via the API for that information. Again, maybe for each record, if that tag doesn't exist it means its not On?
This may be a question to direct to JAMF, since I'm not sure how its determining the Legacy FileVault status and where it would be storing it.

[rtrouton]

This misunderstanding may be my fault. I had talked with @RaulSantos elsewhere and had thought that the FileVault Enabled status also captured Legacy FileVault status.

If it's just for FileVault 2, then an extension attribute may be needed. I've got an older script (originally written for Absolute Manage) that can be repurposed for this:

https://github.com/rtrouton/rtrouton_scripts/tree/master/rtrouton_scripts/Absolute_Manage_Custom_Inf...

[tron_jones]

Don't know if this helps or was what you were after.
I could only find file vault status in the JSS API under <General><hardware><storage><device><partition> with these four fields:

<filevault_status>Encrypted</filevault_status>
<filevault_percent>100</filevault_percent>
<filevault2_status>Encrypted</filevault2_status>
<filevault2_percent>100</filevault2_percent>

I don't have any systems running legacy file vault but would assume that it would return <filevault_status>Encrypted</filevault_status> and <filevault2_status>Not Encrypted</filevault2_status> for anything running Legacy File Vault.

Heres a script to grab the filevaultstatus API.

compName=$(scutil --get ComputerName)

fileVaultStatus=$( curl -s -k -u apiUser:apiPass https://yourjss.com:8443/JSSResource/computers/name/$compName 2>&1 | xpath /computer/hardware/storage/device/partition/filevault_status | awk -F'[>|<]' '{print $3}' )
echo "${fileVaulltStatus}"

10.9.2, jss 9.24

[mm2270]

@tron_jones Yeah, I saw those too and meant to mention it, but they are related of course to full disk encryption FileVault 2. It seems @RaulSantos is asking for information on the older home folder encryption, aka Legacy FileVault. That encryption only applied to systems prior to 10.8.

[krichterjr]

I'm not sure your ultimate goal but I believe you can gather this information through an Advance Search on the Inventory page instead of jumping into the API or database directly. I'm on Casper 8 still but I have a VM with 9 and I see the same options available.

Criteria
OS Configuration Information - FileVault Status - XXXXXX

Display Fields
Select whatever fields you want to display

Of course if you are looking to do more that just get a simple report then this probably won't do. I just know sometimes we jump to the more complicated solutions instead of the simple ones. :-)

[mm2270]

@krichterjr raises a good point. @RaulSantos, can you elaborate on the end goal here? Were you looking to just generate a report on Legacy FileVault status across your systems? Or were you looking to script something that runs on each Mac to get its own information? If the former, try the method Kenny lists. if the latter, try using the script that @rtrouton linked to above.

Either way though, it may help us to understand the end goal here..

[RaulSantos]

@mm2270 looking to see what computers have Legacy FileVault enable since Jamf already captures this I felt a sql query would be the fastest way to pull the data. While @rtrouton way would work i would have to wait to for computer to inventory while the data already is there. Am just not sure in what table jamf stores it.

[Hego_Damask]

select computer_id, computer_name, file_vault_1_status, file_vault_1_status_percent from computers_denormalized group by computer_id;

[Hego_Damask]

Legacy FV status is in the computers_denormalized table. FV2 status is in file_vault_2, file_vault_2_certificate, file_vault_2_computer_key, and file_vault_2_users.