azdzn (1/12/2012)
Here it is :param ( [string]$ComputerName = "." )
Get-WmiObject -computername "$ComputerName" Win32_LogicalDisk -filter "DriveType=3" | foreach {
add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))
add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Size/1GB),2))
add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))
add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Size) * 100),2)) -passThru } |
Select UsageDT, SystemName, DeviceID, VolumeName, SizeGB, FreeGB, PercentFree
This comes from this article :
Yeah, but this article does not mention how do I insert the data into the table. I've got my script working from the PS command line, but, when run from a job, it complains about syntax error in the insert command.
$serverName = "."
$databaseName = "DBMonitor"
$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString ="Server=$serverName;Database=$databaseName;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
Get-WmiObject Win32_logicaldisk | %{
$Command.CommandText ="INSERT into DiskSpace ([Drive], [MediaType], [Size], [FreeSpace]) VALUES ('$($_.DeviceId)', '$($_.MediaType)', '$($_.Size)', '$($_.FreeSpace)')" >> c:\log.txt
$Command.ExecuteNonQuery() | out-null
}
$Connection.Close()