At Songbird we have a server set up to receive crash reports from our users. It's the same system Mozilla uses, written largely by Google employees and it's called socorro. It's great having that data and there is a web app that you can run that will display the data, the stack frames, charts and other goodies. You can do some simple searches and see the top crashers over a certain period of time.

There is a lot of data there though, and to make really good use of it you need some automation. So I'm working on setting up some automatic queries to run each night via a crontab that will pull topcrash data and deposit the results in a file somewhere in such a manner that I can create a csv file and create a nifty chart for it.

The first step has been figuring out how to run a postgres query command in a crontab. It's taken some time but I've finally figured it out. I should make a little tutorial section somewhere like I did for extensions but I'm so busy I doubt I'll get to that. So here's a list and some links (I assume an already setup postgres socorro database with an additional user: crashreports_user)

- allow the crashreports_user to log in with a password. I didn't actually create the user, but I believe it was created something like this: http://www.ubuntugeek.com/howto-setup-database-server-with-postgresql-and-pgadmin3.html

- set up the permissions on the postgres server to allow a user to connect that *doesn't* exist as a system account (can't log into the machine): http://solyaris.wordpress.com/2008/08/09/setup-postgres-in-ubuntu/ (I also looked at the comments in the pg_hba.conf and pg_ident.conf files)

- the kicker was to set up my account on the machine with the password for the crashreports_user in a .pgpass file, like so: http://www.postgresql.org/docs/8.1/interactive/libpq-pgpass.html

After those steps I was able to run a psql command from the command line as the crashreporst_user and not get prompted for the password. Next step is to work on the proper sql query statement to return exactly the data I need.