文章摘要
这篇文章主要介绍了如何在Windows Server 2019环境中通过PowerShell脚本导出磁盘空间信息，并通过电子邮件通知接收方。文章详细说明了配置服务器和数据库（Master）、用户ID和密码，接收邮件的信息（如接收邮箱地址），以及使用`msdb..sp_send_dbmail`存储过程发送邮件的配置。同时，文章定义了三个函数：`GetServerName`、`GetAlterCounter`和`Getpwd`，用于获取服务器名称、更改计数器和获取密码等信息。主脚本通过遍历每个存储设备，计算剩余空间，并生成包含磁盘名、剩余磁盘空间和总磁盘空间的报告。最后，脚本会通过`CreateAlter`函数发送邮件通知。
$server=”.”
$uid=”sa”
$db=”master”
$pwd=”数据库sa密码”
$mailprfname=”test” —需要跟select name FROM msdb.dbo .sysmail_profile一致
$recipients=”接收邮箱，多个用；隔开”
$subject=”邮件标题”
$computernamexml=”E:\powershell\computername.xml”
$alter_xml=”E:\powershell\cpdisk.xml”
$pwd_xml=”E:\powershell\pwd.xml”
function GetServerName($xmlpath)
{
$xml=[xml] (Get-Content $xmlpath)
$return=New-Object Collections.Generic.List[string]
for($i=0;$i -lt $xml.computernames.ChildNodes.Count;$i++)
{
if ( $xml.computernames.ChildNodes.Count -eq 1)
{
$cp=[string]$xml.computernames.computername
}
else
{
$cp=[string]$xml.computernames.computername[$i]
}
$return.Add($cp.Trim())
}
$return
}
function GetAlterCounter($xmlpath)
{
$xml=[xml] (Get-Content $xmlpath)
$return=New-Object Collections.Generic.List[string]
$list=$xml.counters.Counter
$list
}
function Getpwd($xmlpath)
{
$xml=[xml] (Get-Content $xmlpath)
$returnpwd=New-Object Collections.Generic.List[string]
for($i=0;$i -lt $xml.pwd.ChildNodes.Count;$i++)
{
if ( $xml.pwds.ChildNodes.Count -eq 1)
{
$pw=[string]$xml.pwd.password
}
else
{
$pw=[string]$xml.pwd.password[$i]
}
$returnpwd.Add($pw.Trim())
}
$returnpwd
}
function CreateAlter($message)
{
$SqlConnection=New-Object System.Data.SqlClient.SqlConnection
$CnnString=”Server=$server; Database=$db;User Id=$uid; Password=$pwd”
$SqlConnection.ConnectionString=$CnnString
$CC=$SqlConnection.CreateCommand();
if (-not ($SqlConnection.State -like “Open”)) { $SqlConnection.Open() }
$cc.CommandText=” EXEC msdb..sp_send_dbmail
@profile_name=’$mailprfname’
,@recipients=’$recipients’
,@body=’$message’
,@subject=’$subject’
”
$cc.ExecuteNonQuery()|out-null
$SqlConnection.Close();
}
$names=GetServerName($computernamexml)
$pfcounters=GetAlterCounter($alter_xml)
$upwd=Getpwd($pwd_xml)
$report=””
for($m=0;$m -lt $names.count;$m++)
{
$cp=$names[$m]
$p=New-Object -TypeName System.Collections.ArrayList
$uname=”administrator”–因为取的服务器用户名都是administrator，如果每台机器不一样，可以放在XML等文件中读取
$pw=$upwd[$m]
$upassword=convertto-securestring $pw -AsplainText -force;
foreach ($pfc in $pfcounters)
{
$filter=”deviceID='”+$pfc.get_InnerText().Trim()+”‘”
#$Disk=get-wmiobject win32_logicaldisk -computername $cp -Filter $filter
#$counter=$Disk.Freespace/1024MB
$cred=new-object system.management.automation.PSCredential($uname,$upassword);
$counter=(get-wmiobject -credential $cred -class win32_logicaldisk -computername $cp -filter $filter).Freespace/1024MB
$total=(get-wmiobject -credential $cred -class win32_logicaldisk -computername $cp -filter $filter).Size/1024MB
#$pfc=$pfcounters[$i]
$path=”机器名：”+$cp+”; 盘符：”+$pfc.get_InnerText()
$diskFree=”;总磁盘空间大小为:”+[math]::truncate($total).ToString()+”G;当前剩余空间大小为：”+[math]::truncate($counter).ToString()+”G!”
$item=”{0} {1} ” -f $path,$diskFree
$report +=$item + “`n”
}
}
$report
if($report -ne “”)
{
CreateAlter $report
}