Script to monitor space for all drives via Powershell

 Script to monitor space for all drives via Powershell 

 

$query = "SELECT DISTINCT vs.volume_mount_point

    ,vs.file_system_type

    ,vs.logical_volume_name

    ,CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)]

    ,CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)]

    ,CAST(CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18, 2)) * 100 AS [Space Free %]

FROM sys.master_files AS f WITH (NOLOCK)

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs

OPTION (RECOMPILE)"

 

 

$csvFilePath = "U:\msatest\test3_$(get-date -format "yyyy MMM d").csv"

#$excelFilePath = "C:\Users\TEMP.FDNET.005\Desktop\PowerShell\results.xls"

 

 

 

$instanceNameList = get-content U:\msatest\ServerInstances.txt

 $results=@()

 

foreach($instanceName in $instanceNameList)

{

        write-host "Executing query against server: " $instanceName

        $results += Invoke-Sqlcmd -Query $query -ServerInstance $instanceName

}

 

# Output to CSV

 

write-host "Saving Query Results in CSV format..."

$results  | export-csv   $csvFilePath   -NoTypeInformation

#Creating connection with DB

$sqlsvr = 'RGPIDBA092\IBMMONSVR'

$database = 'ibmdba'

$table = 'capacity'

#Create SQL Connection

Write-Verbose "Creating SQL Connection"

$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlsvr;

Initial Catalog=$database; Integrated Security=SSPI")

$conn.Open()

$cmd = $conn.CreateCommand()

Import-Csv (Get-ChildItem U:\msatest\*.csv | sort LastWriteTime | select -last 1 )| %{

  #Create query string

  #Must matching the table layout (SoftwareName, Version)

  Write-Host -Fore Green "Updateing Table"

  $cmd.CommandText =  "INSERT INTO $table (Name,total_size,free_size,percentage) VALUES ('$($_.logical_volume_name)','$($_.'Total Size (GB)')','$($_.'Available Size (GB)')','$($_.'Space Free %')')"  

  #Execute Query

  $cmd.ExecuteNonQuery() | Out-Null

  } 

#write-host "Saving Query Results in CSV format..."

#$results  | export-csv   $csvFilePath   -NoTypeInformation

## END OF SCRIPT ###