Mdthbs

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.

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]

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.

-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.

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!