Site Map - skip to main content - dyslexic font - mobile - text - print

Hacker Public Radio

Your ideas, projects, opinions - podcasted.

New episodes Monday through Friday.


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.
Listen in ogg, spx, or mp3 format. | Comments (6)

Crontab

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

Script

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;

Comments

Subscribe to the comments RSS feed.

Comment #1 posted on 2016-09-07T02:35:07Z 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-08T11:34:46Z 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-08T16:25:37Z 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-08T19:18:21Z 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-08T19:28:17Z by norrist

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

Comment #6 posted on 2016-09-10T22:02:47Z 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:
Title:
Comment:
Anti Spam Question: What does the P in HPR stand for ?
Are you a spammer →
Who hosted this show →
What does HPR mean to you ?