nix, shell, perl, php, mysql and mac os x tips and tricks

Saturday, August 13, 2016

grep mail.log for only sent emails

tail -1000 /var/log/mail.log | grep -E -o "\bto=<[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,6}\b" | grep -v '[DOMAINS YOU WANT TO EXCLUDE]'

regex to grab the last path of a url


Saturday, July 30, 2016

Geocode thousands of addresses with perl

I'm using this jquery store locator and needed a way to geocode 7K addresses. Google only lets you code 2500 in a 24H hour period, so this will quit when the query limit is reached. When it runs again it will pick up where is left off. So it could theoretically be run on a cron until it's done.

use strict;
use DBI;
use Google::GeoCoder::Smart;

my $geo = Google::GeoCoder::Smart->new();

my $host =""; 
my $database =""; 
my $user =""; 
my $mysqlpassword ="";

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1}); 

 my $ctr= 0;
 my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";  
 my $sth = &db_query($sql);
 while (my $ref = $sth->fetchrow_hashref) {
        my ($num, $error, @results, $returntext) = $geo->geocode(
        "address" => stripChars($ref->{'address'}), 
        "city" => stripChars($ref->{'city'}), 
        "state" => stripChars($ref->{'state'}), 
        "zip" => stripChars($ref->{'postal'})
        my $lat;
        my $lng;
        eval {
         $lat = $results[0]{geometry}{location}{lat};
        eval {
         $lng = $results[0]{geometry}{location}{lng};
        print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
        if ($error eq 'OVER_QUERY_LIMIT') {
        if (($lat) && ($lng)) {
         $lat = $dbh->quote($lat);
         $lng = $dbh->quote($lng);
         my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
         my $sth2 = &db_query($upd_q);
        if (($ctr%10)==0) {
         print "sleeping\n";
         sleep 5;
 undef ($sql);
 undef ($sth);


sub stripChars {
        my($text) = @_;
        $text =~ s/^\s*//; # strip out leading space if there is one
        $text =~ s/\n/ /g; # strip carraige returns
        $text =~ s/\t/ /g; # strip tabs
        $text =~ s/\a/ /g; # strip carraige returns
  $text =~ s/"/'/g; # strip quotes and replace with single quotes
        $text =~ s/\s+/ /g; # strip repeating spaces and replace with one
        $text =~ s/[^[:ascii:]]+//g;
        return ($text);
} # end sub strip chars

sub db_query {
 my ($query) = @_;

 my $sth = $dbh->prepare("$query"); 
 #print $query;
 #print $dbh->err; 
 my $err = $dbh->err;  
 my $errstr = $dbh->errstr;
 if ($err) { print "$err: $errstr on query $query"; }
 return $sth;
} # end sub db_query

Tuesday, April 26, 2016

Add transparent PNG to a JPG using ImageMagick Convert

convert shirt.jpg art.png -gravity center -composite -matte output.jpg

Tuesday, March 22, 2016

recursively search and replace ambersands (or any character) in filenames

Replaces the ampersands with underscores in any file ending in .jpg (all subdirs)
find . -type f -iname "*.jpg" -exec rename 's/&/_/' {} \;
Same thing, but on systems that don't have 'rename' (like OS X)
find . -name '*.jpg' -type f -exec bash -c 'mv "$1" "${1/\/\&/_/}"' -- {} \;

Friday, March 11, 2016

wget a site for caching purposes (spider it)

Sometimes you want to crawl a site so that it gets cached on its server. This will "spider" a site and not download content
wget -r -l6 --spider -D

Wednesday, September 23, 2015

MySQL query to compare two COUNT results to get a percentage

 keyed.keyed_codes AS "Entered Codes",
 totals.total_table AS "Total Codes",
 ROUND(((keyed.keyed_codes/totals.total_table)*100),2) AS "Percent Keyed"
(SELECT count(*) AS keyed_source FROM `table` WHERE code RLIKE "^[1-9]" AND timestamp BETWEEN '20150801000000' AND '20150831000000') AS keyed,
(SELECT count(*) AS total_table FROM `table` WHERE timestamp BETWEEN '20150801000000' AND '20150831000000') AS totals

Saturday, January 17, 2015

Stop iTunes v12 from Expiring Podcasts

If you're a heavy podcast listener you've probably seen this iTunes message:

 "iTunes has stopped updating this podcast because you have not listened to any episodes recently. Would you like to resume updating this podcast?"

 iTunes stops downloading podcasts if you haven't listened to any of the last 5 downloaded episodes. I perhaps because bandwidth gets expensive? But it's annoying because sometimes you're traveling and not able to sync, or you occasionally listen to the podcast on Stitcher or some other podcatcher right on your phone. Doesn't mean you want them to stop downloading.

 This script used to work, but somewhere in iTunes version 11 they wised up to it and it stopped working. Now they are actually checking the downloaded tracks to see if any are played.

 So, I wrote a little script that gives 'em what they want. It plays the first unplayed episode in each subscribed podcast, and then back-tracks to the beginning of the podcast so that when you go to listen you don't know the difference. In order for a track to be identified as "played" it appears it needs to have been playing for at least 15 seconds. While the script is running it sets the volume to 0. But it will return the volume to what it was previously set at when it's done. So you can run this via launchd or cron once a day, in the middle of the night.

 Yes, it's kludgey. I can't even say for sure that it works, as I just wrote it. I borrowed some of Doug's code, so credits to him.

UPDATE: This actually appears to work.  I've been running it as a cron job late at night for a couple weeks and haven't had the "expired podcast" issue.  Only weirdness I'm seeing is that the iTunes "messages" (little note that pops up to tell you when a new track is playing) sticks on the last track processed.

--check if itunes is running.  if not, activate it
set itunesOK to my itunes_is_running()
if itunesOK is false then
 tell application "iTunes"
 end tell
end if

tell application "iTunes"
 with timeout of 300 seconds
  --see if there's a currently playing track so we can return to it when done
  set playTrack to false
  if player state is playing then
   set aTrack to current track
   set playTrack to true
   set myMessage to "Running the StopExpired script, will return to your track shortly"
   set myReturn to my growlMessage(myMessage)
  end if
  --see what the volume is currently set at
  set curVol to (get sound volume)
  --turn the volume all the way down so what we're about to do is not audible
  set sound volume to 0
  set podcast_playlist to some playlist whose special kind is Podcasts
  -- filter multiple names
  set each_podcast to {}
   set each_podcast to my ASCII_Sort(my get_discrete_list_of(get album of every track of podcast_playlist))
  end try
  --display the list (debugging only)
  --set selectedVoice to {choose from list each_podcast}
  -- loop thru each podcast
  if each_podcast is not {} then
   repeat with this_podcast in each_podcast
    --create a list of unplayed episodes of within each podcast
    set each_unplayed to {}
     set each_unplayed to (every track of podcast_playlist whose album is this_podcast and unplayed is true)
    end try
    if each_unplayed is not {} then
     set this_unplayed to (item 1 of each_unplayed)
     --for debugging
     set myMessage to this_podcast & ": " & name of this_unplayed as string
     set myReturn to my growlMessage(myMessage)
     --download the episode, if it's not already
      set url_track to (get URL track of this_unplayed)
      set myMessage to "DOWNLOADING " & name of this_unplayed & " URL: " & URL track as string
      set myReturn to my growlMessage(myMessage)
      download url_track
     end try
      play this_unplayed
      delay 20
      set player position to 0
      stop this_unplayed
      --back track this_unplayed
     end try
     --why is this necessary? I don't know.  Sometimes the last track just keeps playing.
    end if
   end repeat
  end if
  --update all the podcasts
  --return the sound volume to previous level
  set sound volume to curVol
  set myMessage to "StopExpired script complete"
  set myReturn to my growlMessage(myMessage)
  --if there was a track already playing, return to it
  if playTrack is true then
   play aTrack
   set playTrack to true
  end if
 end timeout
end tell

--if itunes was originally closed, then close it again
if itunesOK is false then
 tell application "iTunes"
 end tell
end if

to get_discrete_list_of(list1)
 script a
  property list1ref : list1
 end script
 set list2 to {}
 script b
  property list2ref : list2
 end script
 repeat with i from 1 to length of list1
  set this_item to item i of a's list1ref
  considering case
   if this_item is not "" and this_item is not in b's list2ref then set end of list2 to this_item
  end considering
 end repeat
 return b's list2ref
end get_discrete_list_of

on ASCII_Sort(my_list)
 set the index_list to {}
 set the sorted_list to {}
 repeat (the number of items in my_list) times
  set the low_item to ""
  repeat with i from 1 to (number of items in my_list)
   if i is not in the index_list then
    set this_item to item i of my_list as text
    if the low_item is "" then
     set the low_item to this_item
     set the low_item_index to i
    else if this_item comes before the low_item then
     set the low_item to this_item
     set the low_item_index to i
    end if
   end if
  end repeat
  set the end of sorted_list to the low_item
  set the end of the index_list to the low_item_index
 end repeat
 return the sorted_list
end ASCII_Sort

--subroutine showing messages in growl (preferably)
--and if no growl, default dialog with timeout
to growlMessage(myMessage)
 --show our output message
 -- Check if Growl is running:
 set isRunning to my growl_is_running()
 --Only display growl notifications if Growl is running:
 if isRunning = true then
  tell application "GrowlHelperApp"
   -- Make a list of all notification types:
   set the allNotificationsList to ¬
    {"Notification 1", "Notification 2"}
   -- Make a list of the default enabled notifications:
   set the enabledNotificationsList to ¬
    {"Notification 1"}
   -- Register the script with Growl
   -- using either "icon of application"
   -- or "icon of file":
   register as application ¬
    "StopExpired" all notifications allNotificationsList ¬
    default notifications enabledNotificationsList ¬
    icon of application "Script Editor"
   -- Send a notification:
   notify with name "Notification 1" title "StopExpired output" description myMessage application name "StopExpired"
  end tell
  display dialog myMessage giving up after 1
 end if
end growlMessage

--sub checks if growl is running
on growl_is_running()
 tell application "System Events" to return (exists process "GrowlHelperApp")
end growl_is_running

--subroutine checks if itunes is running
on itunes_is_running()
 tell application "System Events" to return (exists process "iTunes")
end itunes_is_running

Friday, September 19, 2014

Perl subroutine to fix when html entities get recursively applied

sub enEntity {
 my $string = shift;
 if ($string =~ /&/) {
  #print "string: $string\n";
 if ($string =~ /&(amp;)+quot;/) { $string =~ s/&(amp;)+quot;/"/ig; }
 if ($string =~ /&quot;/) { $string =~ s/&quot;/"/ig; } 
 if ($string =~ /"/) { $string =~ s/"/"/ig; } 
 if ($string =~ /&(amp;)+apos;/) { $string =~ s/&(amp;)+apos;/'/ig; }
 if ($string =~ /&apos;/) { $string =~ s/&apos;/'/ig; } 
 if ($string =~ /'/) { $string =~ s/'/'/ig; } 
 if ($string =~ /&(amp;)+/) { $string =~ s/&(amp;)+/&/ig; }
 if ($string =~ /&amp;/) { $string =~ s/&amp;/&/ig; }
 if ($string =~ /&/) { $string =~ s/&/&/ig; }
        #$string = unidecode($string);

 return ($string);

} # end sub unescape html entities

Convert raw (batch) mysql command-line output to CSV

mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

Thursday, May 16, 2013

This perl code recurses down thru a category hierarchy and indents. You pass into it the category you want to drill into
# call the sub
sub drillTree {
 my ($root,$level) = @_;
 my $tab = "";
 for (my $i=1;$i<=$level;$i++) {
  $tab .= "\t";
 my $drill_q = "SELECT category_code,category_name FROM $categories WHERE category_parent = '$root'";
 my $sth = &db_query($drill_q);
 my $rc = $sth->rows;
 if ($rc>0) {
  while (my $ref = $sth->fetchrow_arrayref) {
   print "${tab}sub_code: $$ref[0] sub_name: $$ref[1]\n";
 } else {
this one will print out the full path
# Launch the category drill-down sub
my @path = ();
$path[0] = "Products";
my @codes = ();
$codes[0] = "0";

sub drillTree {

 my ($dbh,$root,$level) = @_;
 my $tab = "";
 for (my $i=1;$i<=$level;$i++) {
  $tab .= "\t";
 my $drill_q = "SELECT category_code,category_name FROM $categories WHERE category_parent = '$root'";
 my $sth = &db_query($dbh,$drill_q);
 my $rc = $sth->rows;
 if ($rc>0) {
  while (my $ref = $sth->fetchrow_arrayref) {
   $path[$level] = $$ref[1];
   $codes[$level] = $$ref[0];
   my $fullpath;
   my $fullcodes;
   for (my $i=1;$i<=$level;$i++) {
    $fullpath .= $path[$i];
    $fullcodes .= $codes[$i];
    if ($i<$level) {
     $fullpath .= "|";
     $fullcodes .= ";";
   $fullpath =~ s/®//g;
   $fullpath =~ s/é/e/g;
   print "level: $level ${tab}sub_code: $$ref[0] sub_name: $$ref[1] $fullpath $fullcodes\n";
 } else {
} # end drillTree sub

Tuesday, April 23, 2013

Generate random bytes

find /var/ /usr /lib /srv -type f -print0 | xargs -0 cat > /dev/null

Friday, March 15, 2013

Recursively delete backup files created by rsync

These are mega annoying
/usr/bin/find ./ -name '*~' -exec rm '{}' \; -print -or -name ".*~" -exec rm -f {} \; -print

Saturday, February 23, 2013

Uninstall and remove all traces of mysql from OSX

First be sure the server is shut down (first line)
/Library/StartupItems/MySQLCOM/MySQLCOM stop
sudo rm /usr/local/mysql
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*

edit /etc/hostconfig and remove the line MYSQLCOM=-YES-

sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm -rf /var/db/receipts/com.mysql.*
rm -rf /var/db/receipts/com.mysql.mysql*

Tuesday, November 27, 2012

Cron Job to be texted/emailed when a server has a high load average

You could feasibly add this to your cron tab. Better have an unlimited text plan.
* * * * *       THRESHOLD="10.00"; LOAD=$(/usr/bin/uptime | /bin/awk '{print $8}' | /bin/cut -d "," -f 1;); if /usr/bin/test $(/bin/echo "$LOAD > $THRESHOLD" | /usr/bin/bc -l) = 1 ; then /bin/echo "Load average too high: $LOAD " | /bin/mail -s "calliope load avg: $LOAD"; else /bin/echo ""; fi;  > /dev/null 2>&1

Thursday, October 25, 2012

Use mod_rewrite RewriteCond to do something when a requested file (like an image) does not exist

RewriteCond %{REQUEST_URI} ^/images/sa/regular/(.*)\.JPG$ [NC]
RewriteRule ^(.*) /tools/missing_image.jsp\?image=%1 [L]

Monday, June 18, 2012

UNMOUNTABLE_BOOT_VOLUME error when resizing Parallels disk

You can't actually re-size a disk using Parallels. At least, not a plain disk, and not using Parallels Desktop 7. It will cause windows to be unbootable, showing the blue screen of death error "UNMOUNTABLE_BOOT_VOLUME". What you have to do is un-check the "Resize file system" checkbox (Parallels VM configuration), and then make your change (assuming you are making it bigger). That will add unallocated space to your virtual disk. When in windows, you partition and format the space in the Disk Manager->Storage. Give it a drive letter and everything. You then use a partition manager tool like "EaseUS Partition Master Home Edition" (free) to merge the two partitions. Pretty risky, so be sure you have your whole VM backed up.

Thursday, May 3, 2012

change apple display factor for new windows on a mac

Change display factor for new windows
defaults write -g AppleDisplayScaleFactor 0.8
note: doesn't work anympore

Saturday, April 7, 2012

Ban an IP address from your *nix server

iptables -A INPUT -s IP-ADDRESS -j DROP

Friday, February 10, 2012

Check that curl is connecting through your proxy

http_proxy=http://localhost:8082 curl -C - -O -# -v -A 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv: Gecko/20080311 Firefox/' > index.html

Then open that html file in your browser