Hey! I know some people (cc @puffycid @Seth Hanford) have gotten ATCs to work for safari history, but every time I've tried I get errors like

I1119 08:43:21.214730 306961920 virtual_sqlite_table.cpp:91] Cannot open specified database: SQLITE_CANTOPEN
I1119 08:43:21.214769 306961920 auto_constructed_tables.cpp:38] ATC Table: Unable to detect journal mode, applying default locking policy for path /Users/xxx/Library/Safari/History.db
I1119 08:43:21.215098 306961920 virtual_sqlite_table.cpp:91] Cannot open specified database: SQLITE_CANTOPEN

Is this potentially related to the extra security on the

Library/Safari

directory, or the locking if Safari is running? Just curious if anyone had encountered similar errors. I don't have my own Mac so debugging is challenging!

osquery was not supposed to allow accessing the browser history 😞 it's one of the many ways ATC can be misused to go against the osquery guidelines (respect user privacy)

I thought that was just why it's relegated to ATC instead of in core tables? which totally makes sense!

Hey @Julia Cox I just tested my basic safari atc config on macOS Monterey and it stilled seemed to work fine (using safari 15.1).

osquery> select * from safari_history;
                                path = /Users/puffycid/Library/Safari/History.db
                                   id = 103
                                  url = <http://cnn.com/>
                     domain_expansion = cnn
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 104
                                  url = <https://www.cnn.com/>
                     domain_expansion = cnn
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 105
                                  url = <https://www.google.com/search?client=safari&rls=en&q=osquery&ie=UTF-8&oe=UTF-8>
                     domain_expansion = google
                          visit_count = 2
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 106
                                  url = <https://osquery.io/>
                     domain_expansion =
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 107
                                  url = <https://www.google.com/search?client=safari&rls=en&q=honestsecurity&ie=UTF-8&oe=UTF-8>
                     domain_expansion = google
                          visit_count = 2
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

                                 path = /Users/puffycid/Library/Safari/History.db
                                   id = 108
                                  url = <https://honest.security/>
                     domain_expansion =
                          visit_count = 1
                   daily_visit_counts = d
                  weekly_visit_counts =
                autocomplete_triggers =
should_recompute_derived_visit_counts = 0
                    visit_count_score = 100
                          status_code = 0

this worked with both safari opened and closed Can u share ur atc config file? In regards to browser history access, I think viewing browser history can be useful for investigations and if analyst is trying to figure out if an attacker/compromised account accessed any internal websites (ex: Sharepoint, source code, web apps, etc) or the account downloaded any malware, or uploaded any files to external services (or if the account visited them) During an incident response (IR) investigation browser history is likely going to be collected in order to do full investigations, and if an organization is trying to use osquery to investigate having the option query it (either native, extension, atc, etc) would probably be nice Just my 2 cents

This sounds like a "Full Disk Access" issue. Perhaps https://osquery.readthedocs.io/en/stable/deployment/process-auditing/#full-disk-access helps?

@puffycid my atc config:

"safari_history":{
      "query":"SELECT url, domain_expansion AS title, MAX(visit_count) AS visit_count, datetime(MAX(visit_time)+978307200, \"unixepoch\") AS last_visited FROM history_items JOIN history_visits GROUP BY url, domain_expansion",
      "path":"/Users/%/Library/Safari/History.db",
      "columns":[
        "last_visited",
        "url",
        "title",
        "visit_count"
      ]
    }

just to follow up, i tried ur config julia and it worked for me so i agree may be full disk access issue

thanks so much! really appreciate it. I'll try with full disk access