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

Try pasting the query from the ATC config into SQLite. I bet a column is named wrong.

Copy-pasted out of .schema output, but as I tend to say ‘everything up to and including copy-paste can fail’, worth a shot

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"
      ]
    }
  }
}