is there a way to join apt_sources and deb_packages to see all of the available packages by where it's from?

It doesn't look like this is possible with the current data exposed in osquery. It does seem like a powerful feature if the data does exist and osquery is simply missing it.

it looks like the code for deb_packages filters out not installed packages. I don't think you can really change that without being a huge break in backwards compatibility. If it was added it seems like it would have to be like a new table or something. Getting the source of the package seems harder. So there is already a source column on deb_packages but it seems unrelated. It gives the value of the source field in the control file. Based on the output from

apt-cache policy

this information looks like a many to many relationship. It looks like when a package is installed it gets added to

/var/lib/dpkg/status

. Then when ubuntu gets upgraded the original source gets commented out if it's not a source ubuntu knows about. At this point the only source for a package might be the status file. Then when you uncomment out the source it will appear in the

apt_sources

table since it parses the files but

apt-cache policy

still won't say that's where a package is from until you run

apt update

. Seems complicated. I guess if I were implementing this I would make a new table to join

apt_sources

to

deb_packages

somehow then add a column to

apt_sources

that says if and how long ago the index for that source has been updated in order to show that the information in the new table might be wrong. I'm not really sure though.