Site Map - skip to main content

Hacker Public Radio

Your ideas, projects, opinions - podcasted.

New episodes every weekday Monday through Friday.
This page was generated by The HPR Robot at

hpr2113 :: sqlite and bash

Using cron, du,sqlite, and bash to find directory growth

<< First, < Previous, , Latest >>

Hosted by norrist on 2016-09-07 is flagged as Clean and is released under a CC-BY-SA license.
bash, sqlite. 6.
The show is available on the Internet Archive at:

Listen in ogg, spx, or mp3 format. Play now:

Duration: 00:14:54



0 3 * * 0 /bin/du -m /data/ > /home/USER/du_files/"du_$(/bin/date +\%Y\%m\%d)"


cd ~/du_files
TODAYS_FILE="du_$(/usr/bin/date +%Y%m%d)"
YESTERDAYS_FILE="du_$(/usr/bin/date --date="7 days ago" +%Y%m%d)"
/usr/bin/echo "create table old (oldsize integer, path varchar);" > delta.sql
/usr/bin/echo "create table new (newsize integer, path varchar);" >> delta.sql
/usr/bin/echo '.separator "\t" ' >> delta.sql
/usr/bin/echo ".import $TODAYS_FILE new" >> delta.sql
/usr/bin/echo ".import $YESTERDAYS_FILE old" >> delta.sql
/usr/bin/echo ".mode csv" >> delta.sql
/usr/bin/echo ".headers on" >> delta.sql
/usr/bin/echo ".out deltas.csv" >> delta.sql
/usr/bin/echo "select *,newsize-oldsize as delta_in_megabytes from old natural join new where oldsize<newsize order by delta_in_megabytes desc;" >> delta.sql

/usr/bin/sqlite3 < delta.sql

echo $YESTERDAYS_FILE|/usr/bin/mailx -a deltas.csv -s deltas.csv

Resulting SQL

create table old (oldsize integer, path varchar);
create table new (newsize integer, path varchar);
.separator "\t"
.import du_20160821 new
.import du_20160814 old
.mode csv
.headers on
.out deltas.csv
select *,newsize-oldsize as delta_in_megabytes
from old    natural join new    where oldsize<newsize
order by delta_in_megabytes desc;


Subscribe to the comments RSS feed.

Comment #1 posted on 2016-09-07 02:35:07 by mackrackit

I found this episode very useful. It gave me a lot of ideas. Looking forward to more like it.

Comment #2 posted on 2016-09-08 11:34:46 by JONATHAN KULP

Worst ever?

I thought I heard you say at the end of this episode that it might be the worst HPR ever? No way! I really enjoyed this, was great hearing how you worked your way through the problem and arrived at a usable solution. Please do more!

Comment #3 posted on 2016-09-08 16:25:37 by Gumnos

Cleaning up the script

You could clean up the script a bit by using a "here document" instead of a temporary SQL file, something like


Comment #4 posted on 2016-09-08 19:18:21 by Kevin O'Brien

Excellent show!

As I was listening to this show on my drive in to work I was thinking that it epitomizes what we mean by something of interest to hackers. I want to hear more from norrist.

Comment #5 posted on 2016-09-08 19:28:17 by norrist

I have never heard of a "here document". Thanks for the tip.

Comment #6 posted on 2016-09-10 22:02:47 by Dave Morriss

I enjoyed this

A good topic for a show I thought. I enjoyed following your thinking and your solution. The audio was good and the background noise was not distracting at all.

I never use full paths to commands, though I can see cases where perhaps I should. Have you been bitten by not doing this in the past? If so I'd like to hear about it.

I wondered why the date program used in the crontab entry was /bin/date whereas it was /usr/bin/date in the main script. Are you working across different OSes or architectures?

Looking forward to more!

Leave Comment

Note to Verbose Commenters
If you can't fit everything you want to say in the comment below then you really should record a response show instead.

Note to Spammers
All comments are moderated. All links are checked by humans. We strip out all html. Feel free to record a show about yourself, or your industry, or any other topic we may find interesting. We also check shows for spam :).

Provide feedback
Your Name/Handle:
Anti Spam Question: What does the letter P in HPR stand for?
Are you a spammer?
What is the HOST_ID for the host of this show?
What does HPR mean to you?