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 me@mywork.com
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;
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
sqlite3
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 :).