powershel连接数据库监控数据库状态并发报警邮件

function Get-DatabaseData {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose ‘in SQL Server mode‘
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose ‘in OleDB mode‘
        $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    if ($isSQLServer) {
        $adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
    } else {
        $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
    }
    $dataset = New-Object -TypeName System.Data.DataSet
    $adapter.Fill($dataset)
    $dataset.Tables[0]

function Invoke-DatabaseQuery {
    [CmdletBinding()]
    param (
        [string]$connectionString,
        [string]$query,
        [switch]$isSQLServer
    )
    if ($isSQLServer) {
        Write-Verbose ‘in SQL Server mode‘
        $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
    } else {
        Write-Verbose ‘in OleDB mode‘
        $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
    }
    $connection.ConnectionString = $connectionString
    $command = $connection.CreateCommand()
    $command.CommandText = $query
    $connection.Open()
    $command.ExecuteNonQuery()
    $connection.close()
}
#‘Server=localhost;Database=test;Trusted_Connection=True;‘
Get-DatabaseData -verbose -connectionString ‘Server = localhost; Database = master;User Id = test; Password = test‘ -isSQLServer -query "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT TOP 20 
  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) 
                                     AS [总运行时间 (s)] 
  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 
                               AS DECIMAL(28, 2)) AS [% CPU] 
  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 
        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 
  , qs.execution_count AS 总执行次数
  , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count 
                AS DECIMAL(28, 2)) AS [平均运行时间] 
  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      
    ((CASE WHEN qs.statement_end_offset = -1 
      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
      ELSE qs.statement_end_offset 
      END - qs.statement_start_offset)/2) + 1) AS [sql语句] 
   
  , DB_NAME(qt.dbid) AS 数据库名 
  
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 
WHERE qs.total_elapsed_time > 0 
ORDER BY qs.total_elapsed_time DESC  " | Select-object "平均运行时间","% CPU", "% Waiting","总执行次数","平均运行时间 (s)","sql语句","数据库名" | Export-Csv  C:\test\执行时间最长的语句.csv –encoding "UTF8"


Get-DatabaseData -verbose -connectionString ‘Server = localhost; Database = master;User Id = test; Password = test‘ -isSQLServer -query "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT t4.name,t1.[statement],t1.object_id, t2.user_seeks, t2.user_scans,
       t1.equality_columns, t1.inequality_columns,t1.included_columns       
      FROM sys.dm_db_missing_index_groups AS t3
      join sys.dm_db_missing_index_details AS t1
       on  t1.index_handle = t3.index_handle
          join sys.dm_db_missing_index_group_stats AS t2
            on t2.group_handle = t3.index_group_handle
              join sys.databases AS t4 
                on t1.database_id = t4.database_id
      where --t4.name in (‘qzycenter‘,‘pbccenter‘,‘PosCenter‘) and 
      user_seeks > 1000
      order by t2.user_seeks desc  " | select-object -Property name,statement,object_id,user_seeks,user_scans,equality_columns,inequality_columns,included_columns | Export-Csv  C:\test\数据库中缺失的索引.csv –encoding "UTF8"

function send-mail{ 
         param( [string[]] $toAddress ,
        [string] $body,
                        [string] $filename,
                        [string] $filename1)
For($i=0;$i-lt$toAddress.Count; $i++)
{
$message = New-Object System.Net.Mail.MailMessage -ArgumentList ‘[email protected]‘,$toAddress[$i],‘数据库运行情况报告‘,$body
$attachment = New-Object System.Net.Mail.Attachment -ArgumentList $filename,‘Application/Octet’
$message.Attachments.Add($attachment)
$attachment1 = New-Object System.Net.Mail.Attachment -ArgumentList $filename1,‘Application/Octet’
$message.Attachments.Add($attachment1)
$smtpServer = "smtp.gmail.com"
$smtpUser = "[email protected]"
$smtpPassword = "time.work.9818"
$smtp = New-Object System.Net.Mail.SmtpClient -argumentList $smtpServer
$smtp.Credentials = New-Object System.Net.NetworkCredential -argumentList $smtpUser,$smtpPassword
$smtp.Enablessl=$true
$smtp.Send($message)
}
}
send-mail ‘[email protected]‘,‘[email protected]‘     ‘每周发送qzycenter数据库运行情况‘    ‘c:\test\数据库中缺失的索引.csv‘  ‘c:\test\执行时间最长的语句.csv‘