Something that tripped me up with the `unified log` table is osquery #macos

Something that tripped me up with the `unified_log...

Brad Girardeau

05/11/2023, 5:45 AM

Something that tripped me up with the

unified_log

table is that pagination is global even within a single query. For example, this gives incomplete results:

Copy code

select subsystem, count(*) from unified_log where 
  timestamp > -1 and max_rows = 10000 and 
  (subsystem = 'com.apple.securityd' and category = 'kcacl') or
  (subsystem = 'com.apple.Authorization' and category = 'authd')
  group by subsystem;

Internally sqlite splits it into two subqueries for each OR clause, where the first moves pagination ahead before the second runs. I didn't see an easy way to change how the pagination works, so I made a PR adding a predicate column to be able to pass in complex conditions with one SQL constraint

Brad Girardeau

05/11/2023, 5:47 AM

The current workaround I found is doing something like this instead:

Copy code

with prev_timestamp as (
       select
         timestamp as t
       from unified_log where
         timestamp > -1 and
         max_rows = 1
     ),
     filtered_log as (
       select * from unified_log where
         timestamp > (select t from prev_timestamp) and
         subsystem = 'com.apple.securityd' and 
         max_rows = 10000
       union all
       select * from unified_log where
         timestamp > (select t from prev_timestamp) and
         subsystem = 'com.apple.Authorization' and
         max_rows = 10000
     ),
     next_timestamp as materialized (
       select timestamp as t from unified_log where
       timestamp > -1 and
       max_rows = 1 and
       timestamp >= (select max(timestamp) from filtered_log)
     )
select next_timestamp.t, subsystem, count(*) from filtered_log left join next_timestamp group by subsystem;

Gavin

05/11/2023, 3:31 PM

This seems to align pretty well to the macadmins unified_log behaviour https://github.com/macadmins/osquery-extension

8 Views

Open in Slack

Previous Next