Disk drive report connectivity problems.

Question

Disk drive report connectivity problems.

RayMan007

SSChasing Mays

Points: 639
More actions
March 18, 2015 at 8:53 am

#305774

Hello all,
I have this script that works great to get disk drive information from all my SQL servers which are in the trusted network. However I would like to modify this script to also get disk information from all my SQL server in the DMZ.
I would like to know where I would need to modify this script to connect to my SQL servers which are not in the trusted network. 🙂
set-location F:\SQL_DB_SIZES
$isodate=Get-Date -format s
$isodate=$isodate -replace(":","")
$basepath=(Get-Location -PSProvider FileSystem).ProviderPath
$instancepath=$basepath + "\config\instances.txt"
$outputfile="\logs\sql_server_db_sizes_" + $isodate + ".html"
$outputfilefull = $basepath + $outputfile
$filePath = ""
$freeSpaceFileName = $outputfilefull
# $serverlist = "sl.txt"
$warning = 30
$critical = 10
New-Item -ItemType file $freeSpaceFileName -Force
# Getting the freespace info using WMI
#Get-WmiObject win32_logicaldisk | Where-Object {$_.drivetype -eq 3} | format-table DeviceID, VolumeName,status,Size,FreeSpace | Out-File FreeSpace.txt
# Function to write the HTML Header to the file
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString('MM/dd/yyyy')
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>DiskSpace Report</title>'
add-content $fileName '<STYLE TYPE="text/css">'
add-content $fileName ""
add-content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
add-content $fileName "<table width='100%'>"
add-content $fileName "<tr bgcolor='#CCCCCC'>"
add-content $fileName "<td colspan='7' height='25' align='center'>"
add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>DiskSpace Report - $date</strong></font>"
add-content $fileName "</td>"
add-content $fileName "</tr>"
add-content $fileName "</table>"
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName "<tr bgcolor=#CCCCCC>"
Add-Content $fileName "<td width='10%' align='center'>Drive</td>"
Add-Content $fileName "<td width='50%' align='center'>Drive Label</td>"
Add-Content $fileName "<td width='10%' align='center'>Total Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Used Capacity(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Free Space(GB)</td>"
Add-Content $fileName "<td width='10%' align='center'>Freespace %</td>"
Add-Content $fileName "</tr>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function writeDiskInfo
{
param($fileName,$devId,$volName,$frSpace,$totSpace)
$totSpace=[math]::Round(($totSpace/1073741824),2)
$frSpace=[Math]::Round(($frSpace/1073741824),2)
$usedSpace = $totSpace - $frspace
$usedSpace=[Math]::Round($usedSpace,2)
$freePercent = ($frspace/$totSpace)*100
$freePercent = [Math]::Round($freePercent,0)
if ($freePercent -gt $warning)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td>$freePercent</td>"
Add-Content $fileName "</tr>"
}
elseif ($freePercent -le $critical)
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#e60000' align=center>$freePercent</td>"
#<td bgcolor='#e60000' align=center>
Add-Content $fileName "</tr>"
}
else
{
Add-Content $fileName "<tr>"
Add-Content $fileName "<td>$devid</td>"
Add-Content $fileName "<td>$volName</td>"
Add-Content $fileName "<td>$totSpace</td>"
Add-Content $fileName "<td>$usedSpace</td>"
Add-Content $fileName "<td>$frSpace</td>"
Add-Content $fileName "<td bgcolor='#ffff00' align=center>$freePercent</td>"
# #ffff00
Add-Content $fileName "</tr>"
}
}
writeHtmlHeader $freeSpaceFileName
foreach ($server in Get-Content $instancepath)
{
Add-Content $freeSpaceFileName "<table width='100%'><tbody>"
Add-Content $freeSpaceFileName "<tr bgcolor='#CCCCCC'>"
Add-Content $freeSpaceFileName "<td width='100%' align='center' colSpan=6><font face='tahoma' color='#003399' size='2'><strong> $server </strong></font></td>"
Add-Content $freeSpaceFileName "</tr>"
writeTableHeader $freeSpaceFileName
$dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3}
foreach ($item in $dp)
{
Write-Host $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
writeDiskInfo $freeSpaceFileName $item.DeviceID $item.VolumeName $item.FreeSpace $item.Size
}
}

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Eric M Russell SSC Guru Points: 125562 More actions · Answer 1

You can use the following stored procedure and DMV queries to get information about server disk space. Because it runs within context of SQL Server, you only need connect to the server and have sysadmin membership.

EXEC master.sys.xp_fixeddrives;

SELECT

@@SERVERNAME AS Server_Name,

@@SERVICENAME AS Service_Name,

cast((select max(local_net_address) from sys.dm_exec_connections) as char(15))Server_IP,

cast(type_desc as char(10)) as Data_Desc,

cast( cast((sum(cast(size as bigint))*8060)/(1024*1024*1024*1.0) as numeric(9,1)) as char(12))Data_GB,

cast(volume_mount_point as char(12)) as Volume_Name,

cast(cast(max(cast(total_bytes as bigint))/(1024*1024*1024*1.0) as numeric(9,1)) as char(18))Drive_Total_GB,

cast(cast(max(cast(available_bytes as bigint))/(1024*1024*1024*1.0) as numeric(9,1)) as char(18))Drive_Free_GB,

cast(cast(max(available_bytes*100.0) / max(total_bytes) as numeric(4,1)) as char(10))Drive_Free_PCT

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.FILE_ID)

group by volume_mount_point, type_desc

order by Volume_Name, Data_Desc;

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho