https://github.com/osquery/osquery logo
Powered by
#macos
Title
# macos
b

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
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;
g

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
7 Views
Powered by