How do I list installed packages with source URI, distribution and section in Debian?

Question

I have a set of (source) URIs, distributions and sections enabled in my Debian Wheezy apt configuration. Now I want a list of all installed packages, and for each of those, the source URI, "distribution" and "section" that the currently installed version of each respective package has been installed from.

I'm basically looking for something like the output of dpkg -l but instead of the description it would show the source URI, distribution and section. Something like:

||/ Name       Version           Arch  InstalledFrom
+++-==========-=================-=====-===================
ii  package1   1:4.14-1.1+deb7u1 amd64 ftp://ftp.se.debian.org/debian wheezy main
ii  package2   1.7-1             all   ftp://ftp.se.debian.org/debian wheezy/updates main
ii  package3   1.0.25+3~deb7u1   all   ftp://ftp.se.debian.org/debian wheezy non-free
ii  package4   0.9.8~3           amd64 local
ii  package5   7.2.107           i386  local

How can I obtain such a list of packages?

Nicely formatted would be a bonus, but mostly anything that I can grep for interesting line substrings (or even just browse through) will likely do.

Answer 1

As well as apt-cache policy, described in Braiam's answer, you can use apt-show-versions and apt-forktracer.

apt-show-versions by default will list all installed packages with the suite they come from, their version and whether they can be upgraded; for example

afl:amd64/experimental *manually* upgradeable from 1.28b-1 to 1.36b-1
agedu:amd64/testing 9723-1 uptodate
devio:armhf 1.2-1 installed: No available version in archive

With the -a option it lists the installed version and all available versions in all the repositories you have configured:

afl:amd64 1.28b-1 install ok installed
No stable version
No testing version
No unstable version
afl:amd64 1.36b-1 experimental ftp.fr.debian.org
afl:amd64/experimental *manually* upgradeable from 1.28b-1 to 1.36b-1
agedu:amd64 9723-1 install ok installed
No stable version
agedu:amd64 9723-1 testing  ftp.fr.debian.org
agedu:amd64 9723-1 unstable ftp.fr.debian.org
No experimental version
agedu:amd64/testing 9723-1 uptodate

apt-forktracer lists the packages which don't come from the standard repositories or whose installed version doesn't match those available in the standard repositories:

afl (1.28b-1) [Debian: 1.36b-1]
bb (1.3rc1-8.2) [Debian: 1.3rc1-8.1+b1 1.3rc1-8.1+b1]
biew (5.7.3.1-0.1) [SK2: 5.7.3.1-0.1]

Answer 2

I found myself putting together pieces from the different answers, particularly Braiam's and muru's. In the end, I ended up with this oneliner, which gets me close enough to a start that I should be able to figure out the rest:

dpkg-query --showformat '${Package}\n' -W | \
while read package; do \
  apt-cache policy $package | grep -q wheezy/non-free && echo $package ; \
done

The above lists the names of all packages which apt-cache policy reports as coming from wheezy/non-free (not exactly, but close enough for the moment). By adjusting what the script does with the apt-cache policy output, while it won't be in a nice tabular format (at least out of the box), it should be possible to get fairly close to what I am after.

Thanks to everyone who helped out!

Answer 3

You are looking for apt-cache policy output:

➜  ~  apt-cache policy skype
skype:i386:
  Installed: 4.3.0.37-1
  Candidate: 4.3.0.37-1
  Version table:
 *** 4.3.0.37-1 0
        100 /var/lib/dpkg/status
➜  ~  apt-cache policy irssi
irssi:
  Installed: 0.8.17-1
  Candidate: 0.8.17-1
  Version table:
 *** 0.8.17-1 0
        900 http://http.debian.net/debian/ testing/main amd64 Packages
        500 http://http.debian.net/debian/ unstable/main amd64 Packages
        100 /var/lib/dpkg/status
➜  ~  apt-cache policy megasync
megasync:
  Installed: 1.0.39
  Candidate: 1.0.39
  Version table:
 *** 1.0.39 0
        500 http://mega.nz/linux/MEGAsync/Debian_7.0/ ./ Packages
        100 /var/lib/dpkg/status

In this case, the installed version of skype isn't available from any repository (that includes local installation), irssi is installed from Debian repositories, and megasync is from a third party repository.

Answer 4

-l is one of the options of dpkg-query, which has another option, -W (or --show). -W allows use to select an output format. You can try:

dpkg-query --showformat '${Status}\t${Package}\t${Version}\t${Architecture}\t${Origin}\t${Section}\n' -W | 
  column -ts $'\t'

For example:

$ dpkg-query --showformat '${db:Status-Abbrev}\t${Package}\t${Version}\t${Architecture}\t${Origin}\t${Section}\n' -W | column -ts $'\t' | head
ii  account-plugin-aim           3.8.6-0ubuntu9.1                amd64  gnome
ii  account-plugin-facebook      0.11+14.04.20140409.1-0ubuntu1  all    gnome
ii  account-plugin-flickr        0.11+14.04.20140409.1-0ubuntu1  all    gnome
ii  account-plugin-google        0.11+14.04.20140409.1-0ubuntu1  all    gnome
ii  account-plugin-jabber        3.8.6-0ubuntu9.1                amd64  gnome
ii  account-plugin-salut         3.8.6-0ubuntu9.1                amd64  gnome
ii  account-plugin-twitter       0.11+14.04.20140409.1-0ubuntu1  all    gnome
ii  account-plugin-windows-live  0.11+14.04.20140409.1-0ubuntu1  all    gnome
ii  account-plugin-yahoo         3.8.6-0ubuntu9.1                amd64  gnome
ii  accountsservice              0.6.35-0ubuntu7.1               amd64  admin

The URI is not known to dpkg, since that's a frontend (apt) concern. It shouldn't be that difficult to use awk or something to obtain it too, though.