Title
#macos
a

allister

07/09/2021, 9:57 AM
what am I missing here…
{
  "auto_table_construction": {
    "munki_app_usage_local": {
      "query": "select event, bundle_id, app_version, app_path, last_time, number_times from munki_app_usage_local;",
      "path": "/Library/Managed Installs/application_usage.sqlite",
      "columns": [
        "event",
        "bundle_id",
        "app_version",
        "app_path",
        "last_time",
        "number_times"
      ]
    }
  }
}
and it's got stuff in it
sqlite> select count(*) from application_usage;
290
but osqueryi isn't seeing it
osquery> SELECT count(*) FROM munki_app_usage_local;
I0709 18:58:29.412958 86793728 virtual_sqlite_table.cpp:111] ATC table: Could not prepare database at path: "/Library/Managed Installs/application_usage.sqlite"
W0709 18:58:29.413038 86793728 auto_constructed_tables.cpp:47] ATC Table: Error Code: 1 Could not generate data: Could not prepare database for path /Library/Managed Installs/application_usage.sqlite
count(*) = 0
s

seph

07/09/2021, 10:59 AM
Try pasting the query from the ATC config into SQLite. I bet a column is named wrong.
a

allister

07/09/2021, 11:48 AM
Copy-pasted out of .schema output, but as I tend to say ‘everything up to and including copy-paste can fail’, worth a shot
12:09 PM
oh duh, it's the table name isn't it
12:10 PM
yup, that was it. Thanks!
12:16 PM
I should PR that clarification to the docs…
Magneto

Magneto

08/03/2021, 8:43 PM
to clarify on this thread, since it was interesting and might be referenced or wondered about by others later, here's the working ATC block for that:
{
  "auto_table_construction": {
    "munki_app_usage_local": {
      "query": "select event, bundle_id, app_version, app_path, last_time, number_times from application_usage;",
      "path": "/Library/Managed Installs/application_usage.sqlite",
      "columns": [
        "event",
        "bundle_id",
        "app_version",
        "app_path",
        "last_time",
        "number_times"
      ]
    }
  }
}
👍 1