Tracking automated scripts

From UA Libraries Digital Services Planning and Documentation
Revision as of 10:27, 11 August 2016 by Jlderidder (talk | contribs) (How to add tracked cronjobs)


The "checkscripts" MySQL database currently resides on the libcontent server. This database is composed (currently) of two tables: "scripts" and "ran".

The scripts table contains the following information for each cron script that we depend upon for support of our infrastructure:

  1. id -- an identifying number, used by each script to log in when it runs
  2. scriptname (name of the script)
  3. server on which it resides
  4. directory in which it exists
  5. cron -- the crontab specification for when it runs
  6. runswhen -- a textual explanation of when it runs
  7. doeswhat -- a textual explanation of what the script does
  8. succeeds -- the name of scripts which must precede this one for it to do its job properly (dependencies)
  9. precedes -- the name of scripts which this script must precede in order for them to do their job properly (dependencies)

The "ran" table contains the entries made by each script after it completes its task, and before it exits. It contains:

  1. an auto-incrementing number for the database entry
  2. scriptid, the number of the script, which corresponds to the id number in the scripts table
  3. datestamp -- added automatically at the time of the entry
  4. errors -- a textual description of any errors encountered by the script during its run

As described in Watching Our Backs, Once a week, a script called "checkscripts" in /srv/scripts/cya looks through the entries in the checkscripts database for the past week, and compares them with the list of entries of existing cron scripts and when they are due to run. If any scripts did NOT run, which were scheduled, or any of them logged errors, this script sends emails to notify us of problems. Of course, this script also logs in with the checkscripts database to verify that it ran, and when.

Then, a third script ("checkscriptcheck" in /srv/scripts/cya) runs to verify that the checkscripts script ran as it should. Again, this one sends us any errors, and it logs in with the checkscripts database.

If there's problems with servers going down frequently, these three scripts can reside on three different servers. This helps track down what has/hasn't been taken care of, as well as notification of servers not functioning correctly.

How to add tracked cronjobs

Creating a cron job

  1. Log in to root
  2. crontab -l > cronjobs ("crontab -l" will list the current cronjobs; using "> cronjobs" will write them to a file by that name in the directory where you are)
  3. vi cronjobs
  4. add time and full path and script
    1. example: 0 2 4 * * /srv/scripts/storing/storingSpreadsheets # be sure to use a day already in use, as checkscripts doesn't check every day! Have the time vary from what's already in the cron jobs.
  1. crontab cronjobs (this OVERWRITES the current cronjob list with what you have in the "cronjobs" file)

Ensuring it is tracked: add to database

  1. log into the database
  2. use checkscripts;
  3. show columns from scripts;
  4. select max(id) from scripts; (if you increment this you'll know what number is assigned to this entry; you'll want to add it to the script in the next steps)
  5. Add new script to database
    1. Example: insert into scripts VALUES(null, "storingSpreadsheets", "", "/srv/scripts/storing", "0 2 4 * *", "4:02 am", "Checks S:/Digital Projects/Administrative/collectionInfo/JodyPickup for updated Metadata Spreadsheets and makes a version copy and puts it in the archive directory", NULL, NULL);
  6. Check it: select * from scripts where id like "##"; (where the id is the incremented one)
  7. Check what's there: select scriptname from scripts order by scriptname;

Ensuring it is tracked: modify end of script

  1. Go back to script, and at the end, add this call where it checks in to the database to say when it ran, and what errors it had. Modify this to include correct database info.
    1. Example:
$mynum = "##";  (the incremented id number, that is the reference in checkscripts.scripts table for this script)
$username="lookitup"; # :-)

$dbh = DBI->connect ("database things look it up in another script", $username, $password) or &sendmessage;
$h->{PrintError} = 1;
$h->{RaiserError} = 1;
$mynum = $dbh->quote($mynum);
$sth= $dbh->prepare("insert into ran values(NULL,$mynum,NULL,NULL)");
$sth->execute or die "can't insert into checkscripts: ",$dbh->errstr(),"\n";
sub sendmessage{
  $email = "soAndSo\";
  $email2 = "backupPerson\";
  $message ="X script just finished on Y server. However, it can't get to checkscripts database!";
  open (TELL, ">/my/script/path/message");
  print TELL $message;
  $subject = q("x script on y problems!");
  `mail -s $subject $email $email2 < /my/script/path/message`;