It was suggested by Adam Bell and James Nierodzik that I post this little script of mine for others to get some use out of. 
It’s less useful as-is really than it is for the individual handlers and key lines of code, especially the parts dealing with updating a running datasheet in Excel and updating an Excel chart on an ongoing basis.
BACKGROUND
In my work environment we an AppleShare OS X server with 1.2 TB of storage…that the staff chronically eats-into at an alarming rate. We’ve put our foot down about adding more space, and so I got it into my head to put an “in your face” update on the amount of free space (as a percentage) that users can’t miss.
At the same time, I wanted to track long-term trends of server useage.
DEPENDENCIES
This script will not work “as-is” in the wild. It depends on certain preconditions.
–“SERVER STATUS” folder at the root level of the server called “Diamond Design”: clever scripters can likely find better ways to customize this.
–A pre-existing spreadsheet named “Diamond Server Free Space.xls” with two tabs…“Data Capture” and “Charting”
–A pre-existing, pre-formatted chart on the “Charting” tab. This script cannot generate a chart from scratch. Try as I might, I could never get Excel to do that in a way I was happy with. But, it does have a feature called “ExtendSeries” which will very neatly append data to an existing chart.
–The “Data Capture” tab contains four columns…Date (A), Capacity (B), Free Space (C), and Percentage Free (D). Percentage Free is calculated by Excel as “=C2/B2”. columns B and C are numbers to two decimal places.
–Requires Microsoft Office X. A quick Get Info on Excel shows version “10.1.5 (030814)”. Pretty good bet this script will break under Excel 2004.
–I think LogMeLibrary depends on a locally-stored logging routine. Just kill all the “logMe” and “logMeLibrary” lines and you won’t cause any harm to the script…they’re for debugging and logging and don’t affect how it works. I’ve included it at the bottom of this post too, for the curious.
–Under Panther we had a problem with this script encountering an OS bug which caused it not to work properly every other time it was run. You will see some of that remnant code commented out and checks against gBugFlag still existing, which caused the scrip tto execute TWICE to overcome the problem. Tiger doesn’t seem to cause this problem so I started to comment-out those sections. Uncomment them to put the “bug check” safeties back in.
Probably other dependencies I might have missed. I’ll update this as I notice or have them brought to my attention.
THE BASICS
The script on my end is set to activate in the wee hours of the morning when no one is around (using CronniX). Diamond Design is a mounted volume on my local desktop. At execution it loads a couple external shared routines (provided after the main script below), then gets the free space and capacity from the server and converts them to gigabytes. The results are rounded, truncated, and converted out of exponential notation.
The bogus text file which “displays” the free space is deleted if it exists so a new one can be put in it’s place. It’s just a generic text file.
Excel data is updated, as is the chart. You will notice the “alert” file and the Excel data also have warning hightlights:
–Green or no coloring for over 20% free space.
–Yellow highlighting for 11-20% free space
–Red highlighting at 0-10%
SUPPORT/DISCLAIMER
Provided as-is. Originally it was suggested this go into ScriptBuilders but I don’t have the time to make it more generally useable “as is” (i.e. the boss isn’t going to let me…)…it’s up to y’all to customize it for yourselves. So it’s into Code Exchange instead.
However, I’ve subscribed to this thread and will answer e-mails if I can.
I also have no doubt there are more efficient ways to do this. I bet Nigel could turn this into a dozen lines of code and add a few features in the process.
MAIN SCRIPT
--Diamond Design Monitor
--by Kevin Quosig
--
--Started: 4/11/06
--Last Update: 4/11/06
--
--Use Excel to build table (and possibly graph) that tracks
--Diamond Design free space.
--
--
-- DECLARE VARIABLES
--
global gReturnChar
global gTabChar
global gFreeSpace
global gCapacity
global gEndRow
global gFreePercent
global gFreePercentColor
global gDateStamp
global gPrefaceExists
global gBugFlag
--
--SUBROUTINES, DEBUGGING
--
set targetLibrary to "Data: Automation:Script Library:logMe.scpt" as string
load script file targetLibrary
set logMeLibrary to result
--
--SUBROUTINES, UTILITY
--
set targetLibrary to "Data: Automation:Script Library:dateStamp.scpt" as string
load script file targetLibrary
set dateStampLibrary to result
--
-- SUBROUTINES, FINDER
--
--create reverse-order date stamp (YYMMDD)
tell dateStampLibrary
dateStamp()
end tell
--get basic volume information
on getVolumeInfo()
set serverName to "Diamond Design"
tell application "Finder"
set capacityRaw to (capacity of disk serverName)
set freeSpaceRaw to (free space of disk serverName)
--trap for free space "missing value" OS bug on every-other launch
if freeSpaceRaw is missing value then
set gBugFlag to "yes"
else
set gBugFlag to "no"
end if
if gBugFlag is "no" then
--convert to gigabytes (GB)
set capacityRawGB to (capacityRaw / 1024 / 1024 / 1024)
set freeSpaceRawGB to (freeSpaceRaw / 1024 / 1024 / 1024)
end if
--
end tell
if gBugFlag is "no" then
set gFreeSpace to round_truncate(freeSpaceRawGB, 2)
set gCapacity to round_truncate(capacityRawGB, 2)
end if
end getVolumeInfo
--round number and truncate
--(from Apple's web site, requires "text_to_number" script)
on round_truncate(this_number, decimal_places)
if gBugFlag is "no" then
if decimal_places is 0 then
set this_number to this_number + 0.5
return number_to_text(this_number div 1)
end if
set the rounding_value to "5"
repeat decimal_places times
set the rounding_value to "0" & the rounding_value
end repeat
set the rounding_value to ("." & the rounding_value) as number
set this_number to this_number + rounding_value
set the mod_value to "1"
repeat decimal_places - 1 times
set the mod_value to "0" & the mod_value
end repeat
set the mod_value to ("." & the mod_value) as number
set second_part to (this_number mod 1) div the mod_value
if the length of (the second_part as text) is less than the ¬
decimal_places then
repeat decimal_places - ¬
(the length of (the second_part as text)) times
set second_part to ("0" & second_part) as string
end repeat
end if
set first_part to this_number div 1
set first_part to number_to_text(first_part)
set this_number to (first_part & "." & second_part)
return this_number
end if
end round_truncate
--convert scientific notation
--(from Apple's web site)
on number_to_text(this_number)
set this_number to this_number as text
if this_number contains "E+" then
set x to the offset of "." in this_number
set y to the offset of "+" in this_number
set z to the offset of "E" in this_number
set the decimal_adjust to characters (y - (length of this_number)) thru ¬
-1 of this_number as string as number
if x is not 0 then
set the first_part to characters 1 thru (x - 1) of this_number as string
else
set the first_part to ""
end if
set the second_part to characters (x + 1) thru (z - 1) of this_number as string
set the converted_number to the first_part
repeat with i from 1 to the decimal_adjust
try
set the converted_number to ¬
the converted_number & character i of the second_part
on error
set the converted_number to the converted_number & "0"
end try
end repeat
return the converted_number
else
return this_number
end if
end number_to_text
-- check file name for supplied alert preface
on examineFileName(checkFileName, checkPreface)
--get preface of supplied file name
set nameLength to length of checkFileName
set prefaceLength to length of checkPreface
set currentPreface to (text items 1 thru prefaceLength) of checkFileName as string
--check preface of supplied file against sought preface and report
if currentPreface is equal to checkPreface then
set gPrefaceExists to "yes"
else
set gPrefaceExists to "no"
end if
end examineFileName
--post notice to Server Status Folder
on serverStatus(messagePrefix, messageString, messageColor)
if gBugFlag is "no" then
set messagePath to "Diamond Design: SERVER STATUS:"
set messageName to (messagePrefix & "--" & messageString & "--" & gDateStamp) as text
set messageAlias to (messagePath & messageName) as text
--get list of files
tell application "Finder"
set fileList to name of every file of (messagePath as alias)
set listCount to number of items in fileList
end tell
--check each file in list for existing preface, delete if found
repeat with listItem from 1 to listCount
examineFileName(item listItem of fileList, messagePrefix)
if gPrefaceExists = "yes" then
set deleteFilePath to (messagePath & (item listItem of fileList)) as text
tell application "Finder"
delete alias deleteFilePath
end tell
end if
end repeat
--create file and title with status message
tell application "Finder"
make new file at messagePath with properties {name:messageName}
set label index of (messageAlias as alias) to messageColor
open for access file messageAlias with write permission
write messageName to (messageAlias as alias) starting at eof
close access (messageAlias as alias)
end tell
end if
end serverStatus
--
-- SUBROUTINES, EXCEL
--
on appendSpreadsheet(currentCapacity, currentFreeSpace)
if gBugFlag is "no" then
set spreadSheetFile to "Diamond Server Free Space.xls"
set spreadSheetFolder to "Data: Automation:0100--Diamond Design Monitor:"
set spreadSheetLocation to (spreadSheetFolder & spreadSheetFile) as text
set tempFile to "Temp.xls"
set tempLocation to (spreadSheetFolder & tempFile) as text
tell application "Microsoft Excel"
Activate
Open spreadSheetLocation
--
--set correct sheet
Select Sheet "Data Capture"
--set starting row to begin search for empty cell to skip header
set currentRow to "2"
set currentColumn to "1"
set currentCell to ("R" & currentRow & "C" & currentColumn) as text
Select Range currentCell
--find next empty cell to begin data entry
repeat until Formula of ActiveCell is ""
set currentRow to (currentRow + 1)
set currentCell to ("R" & currentRow & "C" & currentColumn) as text
Select Range currentCell
end repeat
--store row for use in selecting data for charts
set gEndRow to (currentRow - 1)
--enter today's date
Select Range currentCell
set Formula of ActiveCell to current date
--move over and enter capacity data
set currentColumn to (currentColumn + 1)
set currentCell to ("R" & currentRow & "C" & currentColumn) as text
Select Range currentCell
set Formula of ActiveCell to currentCapacity
--move over one column and enter free space data
set currentColumn to (currentColumn + 1)
set currentCell to ("R" & currentRow & "C" & currentColumn) as text
Select Range currentCell
set Formula of ActiveCell to currentFreeSpace
--move over one column and enter percentage free calculation
set currentColumn to (currentColumn + 1)
set currentCell to ("R" & currentRow & "C" & currentColumn) as text
Select Range currentCell
set Formula of ActiveCell to ("=C" & currentRow & "/B" & currentRow) as text
--is server space low?
set gFreePercent to (Value of ActiveCell) * 100
set gFreePercentColor to "6"
if gFreePercent is less than or equal to 20 then
if gFreePercent is less than or equal to 10 then
--select current data, color red
Select Range ("R" & currentRow & "C1:R" & currentRow & "C4")
set ColorIndex of Interior of Selection to 38.0
set Pattern of Interior of Selection to xlSolid
set gFreePercentColor to "2"
else
--select latest data, color yellow
Select Range ("R" & currentRow & "C1:R" & currentRow & "C4")
set ColorIndex of Interior of Selection to 36.0
set Pattern of Interior of Selection to xlSolid
set gFreePercentColor to "3"
end if
end if
--update existing chart
--(requires pre-existing chart, but easier to implement)
--
Select Sheet "Data Capture"
set myRange to Range ("A" & (gEndRow + 1) & ",D" & (gEndRow + 1)) of ActiveSheet
Select Chart "Charting"
ExtendSeries of every Series of ActiveChart RowOrCol xlColumns Source myRange
--reset to data entry sheet
Select Sheet "Data Capture"
--
Save ActiveWorkbook In tempLocation As xlNormal --Excel can't overwrite existing, save in temp file
Close ActiveWorkbook
end tell
--delete old file, swap and rename temp file
tell application "Finder"
delete alias spreadSheetLocation
update alias spreadSheetFolder
set name of alias tempLocation to spreadSheetFile
end tell
end if
end appendSpreadsheet
--
-- EXECUTION
--
-- starting log entry
tell logMeLibrary
logMe("---", 1, "LOG--Overnight Automation.txt")
logMe("Diamond Design Monitor Begin--" & (current date), 1, "LOG--Overnight Automation.txt")
end tell
-- collect info
getVolumeInfo()
--update Excel spreadsheet
if gBugFlag is "no" then
appendSpreadsheet(gCapacity, gFreeSpace)
end if
--update Server Notice
if gBugFlag is "no" then
serverStatus("FREE SPACE", round_truncate(gFreePercent, 2) & "%", gFreePercentColor)
end if
-- ending log entry
tell logMeLibrary
--logMe("[Script run twice to trap for Free Space Bug]", 2, "LOG--Overnight Automation.txt")
if gBugFlag is "yes" then
logMe("Free Space Bug trapped", 2, "LOG--Overnight Automation.txt")
else
logMe("Free Space Bug not encountered", 2, "LOG--Overnight Automation.txt")
end if
logMe("Diamond Design Monitor End--" & (current date), 1, "LOG--Overnight Automation.txt")
logMe("---", 1, "LOG--Overnight Automation.txt")
end tell
dateStamp LIBRARY
--
--General Date Stamp Script (for use as library)
--by Kevin Quosig
--
--Created: 4/18/06
--Last Modified: 4/18/06
--
--This creates a date stamp in the format YYMMDD
--I prefer this format since it sorts better, especiall
--in the Finder, where I like to use it in file names
--for version control
--
global gDateStamp
on dateStamp()
-- Load date components from system
set dayStamp to day of (current date) as integer
set monthStamp to month of (current date) as text
set yearStamp to year of (current date) as text
--Coerce day component to two-digit form
if dayStamp < 10 then set dayStamp to "0" & dayStamp
--Coerce month component to two-digit form
if monthStamp is "January" then set monthStamp to "01"
if monthStamp is "February" then set monthStamp to "02"
if monthStamp is "March" then set monthStamp to "03"
if monthStamp is "April" then set monthStamp to "04"
if monthStamp is "May" then set monthStamp to "05"
if monthStamp is "June" then set monthStamp to "06"
if monthStamp is "July" then set monthStamp to "07"
if monthStamp is "August" then set monthStamp to "08"
if monthStamp is "September" then set monthStamp to "09"
if monthStamp is "October" then set monthStamp to "10"
if monthStamp is "November" then set monthStamp to "11"
if monthStamp is "December" then set monthStamp to "12"
-- Coerce year into two-digit form
set yearStamp to characters 3 thru 4 of yearStamp as text
--Assemble datestamp
set gDateStamp to yearStamp & monthStamp & dayStamp as text
end dateStamp
logMe LIBRARY
--
--General Logging Script (for use as library)
--by Kevin Quosig
--
--Created: 4/3/06
--Last Modified: 4/18/06
--
global gReturnChar
global gTabChar
on logMe(logString, indentLevel, logFileName)
set logFilePath to "Data: Automation:Logs:"
set logFile to (logFilePath & logFileName) as text
try
writeEntry(logString, indentLevel, logFile)
on error
tell application "Finder"
make new file at logFilePath with properties {name:logFileName}
close access (logFile as alias)
end tell
writeEntry(logString, indentLevel, logFile)
end try
end logMe
on writeEntry(logString, indentLevel, logFile)
set gReturnChar to "
"
set gTabChar to " "
tell application "Finder"
open for access file logFile with write permission
repeat indentLevel times
write gTabChar to (logFile as alias) starting at eof
end repeat
write (logString & gReturnChar) to (logFile as alias) starting at eof
close access (logFile as alias)
end tell
end writeEntry