Righto. I've got it. I've created a db in SQL Server containing 3 tables (servers, partitions and diskspace). I then get the diskspace info and write it to a CSV file in the following format
SERVERNAME,PARTITION,SIZE,USED
SERVER1,C:,100,50
SERVER1,D:,500,350
SERVER2,C:,100,25
SERVER2,D:,1000,500
SERVER2,E:,1000,100
And then, the wonderful IMPORT-CSV cmdlet lets me read in the data. God, I love Powershell!
#Open a connection to the database
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=127.0.0.1\SQLEXPRESS; Initial Catalog=DISKSPACE; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()
#Read the diskspace data
$data=import-csv c:\temp\diskspace.csv
#Iterate the data and define variables for the required fields
foreach ($line in $data) {
$server=$line.server
$partition=$line.partition
$size=$line.Size
$used=$line.Used
$date=get-date -format "yyyy-MM-dd"
#Update the servers table with any new servers (i.e. ones that don't exist already)
$cmd.CommandText ="INSERT Servers (Servername) SELECT '$server' WHERE NOT EXISTS (SELECT id FROM Servers WHERE Servername = '$server')"
$cmd.ExecuteNonQuery()
#Update the partitions table with the partitions
$cmd.CommandText="Insert Partitions (ServerID,Partition) Select id, '$partition' from Servers Where ServerName='$server'"
$cmd.ExecuteNonQuery()
#Add the diskspace data
$cmd.CommandText="Insert Diskspace (PartitionID,Size,Used,ImportDate) Select id, '$size','$used', '$date' from Partitions Where (Partition = '$partition' AND ServerID=(Select id from Servers Where ServerName='$server'))"
$result=$cmd.ExecuteNonQuery()
}
I can use calculated columns in DB to work out the free space and any percentages.
The tables are defined as follows
Server table Column1 = id (Primary Key) - integer value set to IDENTITY
Column2 = ServerName (nvarchar) Unique constraint set on this column (we don't need the same server added more than once!)
Partitions Table Column1 = id (Primary Key) - integer value set to IDENTITY
Column2 = ServerID (int) - related to Server.id
Column3 = Partition (nvarchar)
UNIQUE constraint on Column2 + Column3 so that we don't add the same partition for each server more than once
Diskspace table Column1 = id (Primary Key) - integer value set to IDENTITY
Column2 = PartitionId (int) = related to Partitions.id
Column2 = Size (decimal 18,2)
Column3 = Used (decimal 18,2)
Column4 = ImportDate (date)
Column5 = Free (computed to Size-Used)
Column6 = %Free (computed to ((Size-Used)/Size)*100
Column7 = %Used (computed to (Used/Size) * 100
Strictly speaking, I don't need to use that complicated INSERT statement for adding the servers (i.e. with the WHERE EXISTS clause) because of the UNIQUE constraint on the ServerName column. I could just attempt to add all servers found and let the DB reject any duplicate records but I prefer the "better safe than sorry" approach ;)
<message edited by ginolard on Thursday, June 03, 2010 6:12 PM>