https://github.com/osquery/osquery logo
Join Slack
Powered by
is there a way to retrieve all available tables vi...
# general
i
is there a way to retrieve all available tables via the Thrift API?
to clarify, i see there is no such API available - is there some other way with IPC to retrieve this list, or plans to include this in the Thrift API?
turns out a regular old query works 🙂
Copy code
select tbl_name FROM sqlite_temp_master WHERE type = "table";
and 
pragma table_info("<table name>")
returns all the columns
t
Nice, you can also see all osquery components using the osquery_registry table. That won’t give you the schemas though.
đź‘€ 1
i
hey @theopolis - interesting! 
osquery_registry
returns a lot more tables though.
quick 'n dirty:
Copy code
select sql.name AS 'from sqlite', reg.name as 'from osquery registry' FROM osquery_registry reg 
  LEFT JOIN sqlite_temp_master sql ON reg.name = sql.name 
  WHERE reg.active = 1 
  AND internal = 0
  AND registry = 'table'
good news is that 
pragma table_info("<table>")
works for tables not included in 
sqlite_temp_master
- so I'll just use 
osquery_registry
then. thanks a lot!
t
Very cool, sorry you had to hack through that. We should invent a 
osquery_tables
that structures the schema info.
đź‘Ť 1
i
in the interim, would you like me to contribute some documentation around this?
or open an issue around your suggestion
t
Maybe some documentation on the Extensions page of the readthedocs, mentioning that metadata can be accessed via the osquery_* tables and SQLite internals. Then show your examples.
The important connection we should make for folks IMO is that this information is available via queries and not a specific API
i
agreed. that's what was missing for me in my use-case
t
Ah I forgot, there is a more rich API for explaining a query too. That allows you to inspect type information a query.
getQueryColumns
If you had a complex query, you can use that to see what columns would be returned, how they would be named, and their types as understood by SQLite.
i
yes! i just discovered that today. i'm using Go which has unordered maps, so the columns were not being returned in a consistent order, so i'm using that 
getQueryColumns
to solve that
t
Very cool
i
just to be clear, your mention of `osquery_*`tables above just covers 
osquery_registry
for now, but will cover 
osquery_tables
(or whatever) in the future?
(i wish threads had threads)
scratch that, i see there are several available tables with the 
osquery_
prefix
z
Heads up https://github.com/osquery/osquery/commit/c3f9a3dae22d43ed3b4f6a403cbf89da4cba7c3c disables pragmas. We could re-enable them or just that one.
i
@zwass i'd prefer to not use it, frankly - is there perhaps another way to describe a table in SQL?
z
In the sqlite shell there's 
.schema table
. I wonder if that works via the API?
i
i don't believe it does
.schema
and 
.tables
are exactly what i need, but those do not work via the Thrift API, so I have to revert to plain ol' SQL
s
.table
and 
.schema
can be queried.
Baking something like that into the thrift API would be interesting. I'd love to hear about the use case driving it
i
@seph when you say they can be queried, what do you mean? outside of the CLI?
in my use-case, i need to list all the available tables and constituent columns to provide autocompletion
s
z
I think they are saying that particular "query" doesn't work. I think it might be a sqlite shell builtin.
đź‘Ť 1
i
we're missing each other. i'm issuing SQL queries over the API - but I can't use 
.tables
or 
.schema
over that API unless i'm mistaken
s
Huh. I thought it worked, but it’s been awhile since I know I’ve tried. (And my usual fleet manager now parses and doesn’t allow things that don’t look like sql)
For completion, I’ve seen implementations that use the spec files from source. But that won’t get you extensions
goquery uses 
select name from osquery_registry where registry = 'table' and active = 1
to fetch the active tables for tab completion. Not sure it uses colunmns
i
@seph indeed; the tables aspect is solved - it's just the columns i'm trying to figure out now
@zwass from my readings, 
PRAGMA
seems to be the only way to describe tables without using the shell builtins. how feasible is it to whitelist the 
table_info
pragma in light of that PR you linked earlier? if it weakens the security stance then i can live without it
s
I think it’s reasonable to allow some pragmas.
z
Agreed, and it wouldn't be difficult. It just won't make the 4.6.0 release which I believe was already cut.
i
fine by me - i'll add some error handling to account for the absense of 
pragma
đź‘Ť thanks a lot!
s
Okay, y’all are correct. 
.schema
does not work on the thrift socket.
z
Can you please file this as an issue and link me? I can handle it.
i
to be clear: the 
pragma
whitelistings - or the fact that the shell builtins don't work via Thrift?
s
The pragma whitelistings should be an issue.
The shell builtins are probably just me being confused
i
thought so, will do đź‘Ť
s
Poking around… It looks like 
sqlite_schema
should contain this. But does not. OTOH, I’m not really a sqlite expert.
ah right. it’s been awhile. The osquery tables are all “eponymous virtual tables” which means they don’t show up in the 
sqlite_schema
table
đź‘Ť 1
i
4 Views
Open in Slack
PreviousNext
Powered by