Example query:
with compliance_check as (
select sha256,
case when sha256 = 'omgwtfbbqrandomchars00112233445566' then 'OK' else 'FAILED' end ztl_status
from hash
where path = '/path/to/conf'
)
select compliance_check.sha256,
coalesce(compliance_check.ztl_status, 'FAILED') ztl_status
from (select 1 as x) x
left join compliance_check on x.x = 1;
For osquery via sqlite, 'join's allow us to pull results to correlate or otherwise mash up from multiple tables. Dot-notation is used with those table names to map the 'field'/column label to either return in the subsequent results or match on when sewing tables together. You can additionally adhoc create new tables to work with or wrap the output from a select statement looking up values in a table into a newly named one.
Zentral provided code to cover the case when the thing we're auditing may actually not be present and therefore not return a result, whereas we'd prefer to fail the check in that context instead of letting it be unknown, to improve on the outcome of the v1 query above. To explain what's going on in the v2 example code provided:
- First we're overriding the name of the table 'nesting' the results of the query in parentheses using the 'as' keyword to make it 'compliance_check'
- We're then requesting two columns/rows -
- sha256,
- and the results of the case statement, labeled 'ztl_status'.
- From that new 'metatable', we say we'd like to end up with its sha256 column and a default of failed for ztl_status (via coalesce)
- In the process/same line we're also re-applying the ztl_status label (since the way we're asking for it would be the column header otherwise)
- To 'trick' the query into returning/generating/allowing an empty/NULL result (when as in this case the 'where path' isn't found/the query would normally return no result) we make a SECOND, new table to join against with one key/value, x = 1, and re-lable that single column 'x'.
- Left joining excludes that new tables content but now we can return an empty sha256 and failed for that case on the right-side's table. Think of it like a venn diagram where our join only cares about the values distinctly only in the 'compliance_check metatable'