SQL Server自动化运维系列——监控磁盘剩余空间及SQL Server错误日志（Power Shell）

<computernames>
        <computername>
                wuxuelei-pc
        </computername>
</computernames>

<Counters>
        <Counter alter = "10" operator = "gt" >C:</Counter>
        <Counter alter = "10" operator = "gt" >D:</Counter>
        <Counter alter = "10" operator = "gt" >E:</Counter>
        <Counter alter = "10" operator = "gt" >F:</Counter>
</Counters>

$server = "(local)"
$uid = "sa"
$db="master"
$pwd="password"
$mailprfname = "TestMail"
$recipients = "787449667@qq.com"
$subject = "老大，快去看看这个服务器的磁盘空间马上就不够了！"
$computernamexml = "F:\PowerShell\发送邮件\computername.xml"
$alter_xml = "F:\PowerShell\发送邮件\alter_disk.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 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)
foreach($cp in $names)
{
    $p = New-Object Collections.Generic.List[string]
    $report = ""
    foreach ($pfc in $pfcounters)
    {
        $b = ""
        $counter ="\\"+$cp+"\LogicalDisk("+$pfc.get_InnerText().Trim()+")\% Free Space"
        $p.Add($counter)        
    }    
    $count = Get-Counter $p
    #Write-Host $count.CounterSamples.Count;
    for ($i = 0; $i -lt $count.CounterSamples.Count; $i++)
    {
        $v = $count.CounterSamples.Get($i).CookedValue
        $pfc = $pfcounters[$i]
        #$pfc.get_InnerText()
        $b = ""
        $lg = ""
       if ($pfc.operator -eq "gt")
        {
            if( $v -le [double]$pfc.alter)
                {
                    $b = "alter"
                    $lg = "Less Than"
                }
                if($b -eq "alter")
                {
                    $path = "机器名："+$cp+"; 盘符："+$pfc.get_InnerText()
                    $diskFree="; 当前剩余空间为："+[math]::truncate($v).ToString()+"G;"
                    $aler=" 超过了你预定的阀值："+$pfc.alter.Trim()+"G;速度去处理吧！"            
                    $item = "{0} {1} {2}" -f $path,$diskFree,$aler
                    $report += $item + "`n"
                }
        }           
    }
    if($report -ne "")
    {
        #生产警告 参数 计数器，阀值，当前值
        CreateAlter $report
    }
}

$server = "(local)"
$uid = "sa"
$db="master"
$pwd="password"
$mailprfname = "TestMail"
$recipients = "787449667@qq.com"
$subject = "老大，快去看看这个服务器的SQL Server出问题了！"
$Info="附件为SQL Server错误日志....."
$computernamexml = "F:\PowerShell\发送邮件\computername.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 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 = '$Info'
            ,@subject = '$subject'
            ,@file_attachments='$message'
            "
    $cc.ExecuteNonQuery()|out-null 
    $SqlConnection.Close();
}

$names = GetServerName($computernamexml)
foreach($cp in $names)
{
        #输出系统日志中某个特定程序的日志到文件，比如SQL Server,然后选择错误类型为Error
        Get-EventLog -ComputerName  $cp application | where-object{$_.source-like "MSSQLSERVER"} | where-object{$_.entryType -eq "error"} >F:\PowerShell\$cp+"SQLErrLog.txt"
          
        #生产警告 参数 计数器，阀值，当前值
        CreateAlter F:\PowerShell\$cp+"SQLErrLog.txt"
}

        #输出系统日志中某个特定程序的日志到文件，比如SQL Server,然后选择错误类型为Error
        #Get-EventLog -ComputerName  $cp application | where-object{$_.source-like "MSSQLSERVER"} | where-object{$_.entryType -eq "error"} >F:\PowerShell\$cp+"SQLErrLog.txt"

        #输出系统日志中某个特定程序的日志到文件，比如SQL Server,然后选择错误类型为Error,支持Excel打开
        Get-EventLog -ComputerName  $cp application | where-object{$_.source-like "MSSQLSERVER"} | where-object{$_.entryType -eq "error"} | Export-Clixml –Path F:\PowerShell\$cp+"SQLErrLog.xml" –Depth 2 
          
        #生产警告 参数 计数器，阀值，当前值
        CreateAlter F:\PowerShell\$cp+"SQLErrLog.xml"