PowerShell：获取每日数据库状态电子邮件

最新推荐文章于 2021-07-30 01:17:40 发布

culuo4781

最新推荐文章于 2021-07-30 01:17:40 发布

阅读量511

点赞数

文章标签：数据库 java python 编程语言 javascript ViewUI

原文链接：https://www.sqlshack.com/powershell-get-daily-database-status-email/

版权

The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weekend. Rather than log into each database server and check that the databases were online, I had a script do the work for me and shoot me an email.

当我担任DBA的新工作时，就需要使用此脚本。我的职责之一是确保在周末运行的维护时段之后所有数据库都可用。我没有登录每个数据库服务器并检查数据库是否在线，而是有一个脚本为我工作并向我发送电子邮件。

It evolved overtime, at first it was a simple PowerShell script that gave the output in the PowerShell console. Eventually I learned how to pipe the information into a table and then into HTML, and THEN send an email. After that I learned how to color code it so that if any database was offline, it would color the cell in red.

它是随着时间的推移而发展的，起初它是一个简单的PowerShell脚本，可在PowerShell控制台中提供输出。最终，我学习了如何将信息传递到表中，然后传递到HTML中，然后发送电子邮件。之后，我学习了如何对代码进行颜色编码，以便如果任何数据库处于脱机状态，它将使单元格变为红色。

I prefer to do my PowerShell development in the windows provided Windows PowerShell ISE. I especially like the fact that it has a scripting environment with easily accessible tools and autocomplete, as well as the PowerShellPowerShell console/output window.

我更喜欢在Windows PowerShell ISE提供的Windows中进行PowerShell开发。我特别喜欢它具有一个脚本环境，该环境具有易于访问的工具和自动完成功能，以及PowerShellPowerShell控制台/输出窗口。

In this post I’ll take the time to go over some key parts of the script so that you can manipulate as you wish in order to implement it into your environment. I will provide the finished script at the bottom of the post.

在本文中，我将花一些时间来研究脚本的一些关键部分，以便您可以根据需要进行操作，以将其实现到您的环境中。我将在文章底部提供完成的脚本。

First thing we have to do is import the sql PowerShell module in PowerShell.

我们要做的第一件事是在PowerShell中导入sql PowerShell模块。

 
#Import SQL Module
Import-Module SQLPS -DisableNameChecking

Next let’s quickly design the html output using a simple CSS style. Keep in mind I have very little experience with web development. I got this information from SQL Server 2012 with PowerShell V3 Cookbook. I highly suggest all Database Administrators and Database Developers pick this up. It contains so many useful tips. I also believe that a new one for 2014 has recently been released.

接下来，让我们使用简单CSS样式快速设计html输出。请记住，我对Web开发的经验很少。我从带有PowerShell V3 CookbookSQL Server 2012中获得了此信息。我强烈建议所有数据库管理员和数据库开发人员都来做。它包含许多有用的技巧。我也相信最近发布了2014年的新版本。

The part of this that defines .offline is a property that will set the cell color red (#E01B1B) for any database that is seen as offline.

其中定义.offline的部分是一个属性，它将为被视为脱机的任何数据库将单元格颜色设置为红色（＃E01B1B）。

 
#simple CSS Style
$style = @"
<style type='text/css'>
td {border:1px solid gray;}
.offline{background-color: #E01B1B;}
</style>
"@

Also, for a more in depth beginner’s tutorial on CSS please visit this link.

另外，有关CSS的更深入的初学者教程，请访问此链接。

Now let’s define the function. I like to use functions to encompass my PowerShell script. It allows for easier parameterization and you can also import it as a module into your personal PowerShell profile and just call it from the console whenever necessary. I called it “Get-DbStatus” as it made sense to me and would be easy to remember.

现在让我们定义函数。我喜欢使用函数来包含我的PowerShell脚本。它可以简化参数设置，您还可以将其作为模块导入到您的个人PowerShell配置文件中，并在必要时从控制台调用它。我称它为“ Get-DbStatus”，因为它对我来说很有意义，而且很容易记住。

 
function Get-DBStatus {

Now we need to import a previously created list of servers we’re pulling this information from. It’s just a simple text file with a list of servernames and ports. I store the contents of that list into a variable called $ServerList and use the Get-Content command to pull the data out of the list.

现在，我们需要导入先前创建的服务器列表，以从中获取此信息。这只是一个简单的文本文件，其中包含服务器名称和端口列表。我将该列表的内容存储到名为$ ServerList的变量中，并使用Get-Content命令将数据从列表中拉出。

 
$ServerList = Get-Content C:\PowerShell\PD.txt
 
    Foreach ($serverName in $ServerList){
    
    #Create SMO Object
    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName

Then I use Foreach to loop through each item in $serverlist and store it in $servername. I then use the data stored in $servername to create an SMO object for each server. This is so we can use PowerShell commands to get the information we need.

然后，我使用Foreach遍历$ serverlist中的每个项目并将其存储在$ servername中。然后，我使用$ servername中存储的数据为每个服务器创建一个SMO对象。这样我们就可以使用PowerShell命令来获取所需的信息。

Next, I use a nested foreach loop in order to loop through each database in each sql server (SMO object).

接下来，我使用嵌套的foreach循环来循环遍历每个sql服务器（SMO对象）中的每个数据库。

 
foreach ($db in $SQLServer.Databases){
     
     #Change text to read Online/Offline as opposed to "True" or "False"
    Switch ($db.IsAccessible) {
    "True" {$dbstatus = "Online"}
    "False" {$dbstatus = "Offline"}
    }

The “IsAccessible” property will determine whether the database is available or not. The default output is “True/False” however I used the switch command to return “online/offline”. You can leave it, but I much prefer this method.

“ IsAccessible”属性将确定数据库是否可用。默认输出为“ True / False”，但是我使用switch命令返回“ online / offline”。您可以保留它，但是我更喜欢这种方法。

Next I define properties of the function. This will make it easy to call the function with specific information.

接下来，我定义函数的属性。这将使调用带有特定信息的函数变得容易。

 
#Properties of function
        $props = @{ 'Server' = $ServerName
                    'DbName' = $db.Name
                    'Status' = $dbstatus}
        New-Object -TypeName PSObject -Property $props

You will see me use the “Server, DBName, and Status” properties in the next portion of the script.

您将在脚本的下一部分中看到我使用“服务器，DBName和状态”属性。

We would then close out the function with the necessary closing brackets (“}”) and then call the function within the script.

然后，我们将使用必要的右括号（“}”）关闭该函数，然后在脚本中调用该函数。

Follow this next step closely. I’m taking the output of the function and piping it into an html, and piping that into a foreach loop so I can color code the cells if it’s offline by referencing the css style sheet we created at the beginning of the script. This should give you an idea of how powerful the piping feature in PowerShell is.

请密切注意下一步。我正在获取函数的输出，并将其输出到html中，然后将其输出到foreach循环中，以便可以通过引用我们在脚本开头创建的css样式表来对单元格（如果处于离线状态）进行颜色编码。这应该使您了解PowerShell中管道功能的强大程度。

 
#let's get content from Get-Service and output this to styled HTML
Get-DBStatus | ConvertTo-Html -Property Server, DBName, Status -Title "Database Status" -Head $style |
 
Foreach {
#if database is offline use red background
if ($_ -like "*<td>Offline</td>*")
{
$_ -replace "<tr>", "<tr class='offline'>"
}
else
{
#display normally
$_
}
} |
Out-File "C:\PowerShell\db_status.html" -force

Finally, we can take this output and email an embedded HTML file to us every morning using the Send-MailMessage feature in PowerShell. This will make your daily tasks easier. I’ve provided a generic template below, be sure to confirm the proper settings for the SMTP server, and the “to” and “from” addresses. You may have to work with your mail administrator in order to get this information.

最后，我们可以获取此输出，并每天早晨使用PowerShell中的Send-MailMessage功能通过电子邮件将嵌入式HTML文件发送给我们。这将使您的日常工作更加轻松。我在下面提供了一个通用模板，请确保确认SMTP服务器的正确设置以及“收件人”和“发件人”地址。您可能必须与邮件管理员合作才能获取此信息。

 
Send-MailMessage -to "<recipient@mail.com” -from "SQL Admin <Sqladmin@mail.com>" 
-Subject "DB Status" -SmtpServer smtp.mail.com -Attachments "C:\powershell\db_status.html" -BodyAsHtml (Get-Content C:\PowerShell\db_status.html | Out-String)

As promised, here is the script in its entirety. Remember that you must modify and customize it so that it will work in your environment. You can get really creative with using functions and modular programming. You can even turn this into a module, import it into your profile and call it as a regular command. I suggest modifying it so that the output comes out in the PowerShell console. Otherwise you’d have to dig through your file system in order to open the html file. But this is entirely up to you. PowerShell is so versatile that it can automate most of your day-to-day tasks. Or use a simple command to return a plethora of information almost instantly. Saving a lot of people a lot of time. You can pass on the scripts to your juniors or team and increase productivity.

如所承诺的，这是完整的脚本。请记住，您必须对其进行修改和自定义，以便它可以在您的环境中工作。通过使用函数和模块化编程，您可以真正发挥创意。您甚至可以将其转换为模块，将其导入您的配置文件，然后将其作为常规命令调用。我建议对其进行修改，以便输出在PowerShell控制台中显示。否则，您必须浏览文件系统才能打开html文件。但这完全取决于您。 PowerShell用途广泛，可以自动执行大多数日常任务。或使用简单的命令几乎立即返回大量信息。节省了很多人很多时间。您可以将脚本传递给下级或团队，以提高工作效率。

 
#simple CSS Style
$style = @"
<style type='text/css'>
td {border:1px solid gray;}
.offline{background-color: #E01B1B;}
</style>
"@
 
#Import SQL Module
Import-Module SQLPS -DisableNameChecking
 
function Get-DBStatus {
   <# [CmdletBinding()]
    param (
        [Parameter(Mandatory=$True)][string]$Servername
    )#>
 
    $ServerList = Get-Content C:\PowerShell\PD.txt
 
    Foreach ($serverName in $ServerList){
    
    #Create SMO Object
    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName
 
   
 
    foreach ($db in $SQLServer.Databases){
     
     #Change text to read Online/Offline as opposed to "True" or "False"
    Switch ($db.IsAccessible) {
    "True" {$dbstatus = "Online"}
    "False" {$dbstatus = "Offline"}
    }
        #Properties of function
        $props = @{ 'Server' = $ServerName
                    'DbName' = $db.Name
                    'Status' = $dbstatus}
        New-Object -TypeName PSObject -Property $props
    }
 
 
    }
 
}
 
#let's get content from Get-Service and output this to styled HTML
Get-DBStatus | ConvertTo-Html -Property Server, DBName, Status -Title "Database Status" -PreContent "<h1>Database Status</h1>" -Head $style |
 
Foreach {
#if db is offline, use red background
if ($_ -like "*<td>Offline</td>*")
{
$_ -replace "<tr>", "<tr class='offline'>"
}
else
{
#display normally
$_
}
} |
Out-File "C:\PowerShell\db_status.html" -force
#Set-Alias ie "$env:programfiles\Internet Explorer\iexplore.exe"
#ie "C:\PowerShell\db_status.html"
Send-MailMessage -to "<insert address here>" -from "<insert address here>" -Subject "DB Status" -SmtpServer mail.smtp.com -Attachments "C:\powershell\db_status.html" -BodyAsHtml (Get-Content C:\PowerShell\db_status.html | Out-String)

Here is an example of the output. (I’ve obscured server and database names for obvious reasons, but you should get the idea).

这是输出示例。（出于明显的原因，我已经模糊了服务器和数据库的名称，但是您应该明白这一点）。

And what the offline databases would look like:

脱机数据库的外观如下：

I hope some of you find this script useful. And highly encourage everyone who works in a windows environment to learn PowerShell. It’s such a powerful and versatile tool.

我希望你们中的一些人觉得这个脚本有用。并强烈鼓励在Windows环境中工作的每个人学习PowerShell。这是一个功能强大且用途广泛的工具。

If you’re looking for a place to start, this is a great book to get you primed. Learn Windows PowerShell in a Month of Lunches

如果您正在寻找一个起点，这是一本很好的书，可以帮助您入门。在一个月的午餐中学习Windows PowerShell

Also be sure to checkout Microsoft’s own PowerShell Script Center

另外，请务必签出Microsoft自己的PowerShell脚本中心

Also, please visit a more in depth beginner’s tutorial on CSS

另外，请访问更深入的CSS初学者教程

I highly suggest all Database Administrators and Database Developers pick this up: SQL Server 2012 with PowerShell V3 Cookbook

我强烈建议所有数据库管理员和数据库开发人员都注意这一点： SQL Server 2012 with PowerShell V3 Cookbook

翻译自: https://www.sqlshack.com/powershell-get-daily-database-status-email/

culuo4781

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PowerShell：获取每日数据库状态电子邮件

The need for this script came about when I took a new job as a DBA. One of my responsibilities was to make sure all databases were available after the maintenance window which ran during the weeke...
复制链接

扫一扫