One of the things I’ve long wanted to do with Lightroom is get a graph of my lens usage.  You know, I’d like to see the focal lengths with which I spend most of my time shooting .  Unfortunately, getting this information out of Lightroom has been practically impossible.  Knowing that Lightroom stores its catalogs as SQLite databases, I tried to use some SQLite knowledge to pull this information out of Lightroom.  You know what? It possible.  If you’re not comfortable with terminology such as DOS/Command prompts on Windows and/or Terminals on OSX, you might want to turn back now, otherwise, keep reading.

First I must say something very important!!!

Doing this may break Lightroom for you. While I don’t believe it will, I take no responsibility for what may happen. To be safe, work on a COPY of your Lightroom Catalog, and delete the copy when you are finished.

Here are the steps you need to pull data out of Lightroom.  I’ve tested this with Lightrom 2.5 and it worked perfectly.  It’s unlikely this will work with Lightroom 1.x or the Lightroom 3.0 Beta. (UPDATE: Troy Gaul, engineering lead for Lightrom dropped me a line and said that this will likely work for Lightroom 3 Beta and Final as this part of the catalog isn’t scheduled to be modified.) Let’s get to it!

1. Make sure you quit Lightroom. Don’t have it running while you do this.
2. Open a window and go find your <catalog name>.lrcat file on your disk.
3. Copy your <catalog name>.lrcat file to another folder.  We’ll be working from this copy.  I will assume you put this file in c:\catalog.lrcat
4. Go over to the SQLite download page and download the precompiled binaries for your OS. (i.e. Mac, Windows, etc.)
5. Open the downloaded file, and you’ll be looking for sqlite3.exe on Windows or sqlite3 on OSX.
6. Copy sqlite3.exe (or on OS X just sqlite3) to the same location where you put the copy of your Lightroom catalog. For me, that’s just inside c:\.
7. On Windows you need to open a DOS prompt. Goto the Start Menu, goto Run, and type: cmd.exe and press <enter>.
8. On OS X, you’ll want to goto Applications -> Utilities -> Terminal.
9. At this point you’ll want to go to the directory where you put sqlite3.exe (just sqlite3 on OSX) and your catalog.lrcat.  Since I put it in c:\, I’ll type: “cd c:\“.
10. Now, you’ll want to copy and paste the following command, note the bolded text.  You’ll want to replace the text in bold with the name of your catalog copy.  Here we’re using catalog.lrcat:
11. sqlite3.exe -csv “catalog.lrcat” “SELECT focalLength, COUNT(focalLength), AgInternedExifCameraModel.value, AgInternedExifLens.value from AgHarvestedExifMetadata, AgInternedExifCameraModel, AgInternedExifLens WHERE cameraModelRef = AgInternedExifCameraModel.id_local AND lensRef = AgInternedExifLens.id_local GROUP BY focalLength, AgInternedExifCameraModel.value, AgInternedExifLens.value ORDER BY focalLength ASC;” > analysis.csv
12. Now, the results are stored in a file called analysis.csv.
13. You’ll need to do some of your own processing of the data using Excel or Google Docs, but that’s beyond the scope of this tutorial.

The lines of the file analysis.csv will look something like this:

13,9,”Canon EOS 40D”,”EF-S10-22mm f/3.5-4.5 USM”

13,5,”Canon EOS DIGITAL REBEL XTi”,”EF-S10-22mm f/3.5-4.5 USM”

14,21,”Canon EOS 40D”,”EF-S10-22mm f/3.5-4.5 USM”

The first number is the focal length in mm, the next number is the number of photos that were taken at that focal length, using the camera and lens specified.  From this, you’re own your own.  Perhaps some enterprising person will write a tool to extract and generate the graph automatically, but I don’t have the time right now.  Hopefully you found this useful!

If you’re curious to see what I was able to make, take a look:

This is a graph of what focal length’s I’ve used over the previous 2 years.  It is interesting  I tend to use the extremes on my zoom lenses more than anything else.  Note the position of 10mm & 22mm, coinciding with my use of the Canon EF-S 10-22mm ultra-wide angle lens.  The same thing occurs for my use of the Canon 24-105mm, Canon 17-40mm, and the 70-200mm lenses.