· 9 years ago · Nov 22, 2016, 07:08 AM
1USE [DBADatabase]
2GO
3
4/****** Object: Table [Info].[Alerts] Script Date: 21/11/2016 16:50:57 ******/
5SET ANSI_NULLS ON
6GO
7
8SET QUOTED_IDENTIFIER ON
9GO
10
11CREATE TABLE [Info].[Alerts](
12 [AlertsID] [int] IDENTITY(1,1) NOT NULL,
13 [CheckDate] [datetime] NULL,
14 [InstanceID] [int] NOT NULL,
15 [Name] [nvarchar](128) NOT NULL,
16 [Category] [nvarchar](128) NULL,
17 [DatabaseID] [int] NULL,
18 [DelayBetweenResponses] [int] NOT NULL,
19 [EventDescriptionKeyword] [nvarchar](100) NULL,
20 [EventSource] [nvarchar](100) NULL,
21 [HasNotification] [int] NOT NULL,
22 [IncludeEventDescription] [nvarchar](128) NOT NULL,
23 [IsEnabled] [bit] NOT NULL,
24 [AgentJobDetailID] [int] NULL,
25 [LastOccurrenceDate] [datetime] NOT NULL,
26 [LastResponseDate] [datetime] NOT NULL,
27 [MessageID] [int] NOT NULL,
28 [NotificationMessage] [nvarchar](512) NULL,
29 [OccurrenceCount] [int] NOT NULL,
30 [PerformanceCondition] [nvarchar](512) NULL,
31 [Severity] [int] NOT NULL,
32 [WmiEventNamespace] [nvarchar](512) NULL,
33 [WmiEventQuery] [nvarchar](512) NULL
34) ON [PRIMARY]
35
36GO
37
38 <#
39.SYNOPSIS
40 This Script will check all of the instances in the InstanceList and gather the Alert Information to the Info.Alerts table
41
42.DESCRIPTION
43 This Script will check all of the instances in the InstanceList and gather the Alert Information to the Info.Alerts table
44.PARAMETER
45
46.EXAMPLE
47
48
49
50.NOTES
51 AUTHOR: Rob Sewell sqldbawithabeard.com
52 DATE: 21/11/2016 - Initial
53#>
54
55
56# Load SMO extension
57[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
58$CentralDBAServer = ''
59$CentralDatabaseName = ''
60$Date = Get-Date -Format ddMMyyyy_HHmmss
61$LogFile = "\DBADatabaseAlertUpdate_" + $Date + ".log"
62<#
63.Synopsis
64 Write-Log writes a message to a specified log file with the current time stamp.
65.DESCRIPTION
66 The Write-Log function is designed to add logging capability to other scripts.
67 In addition to writing output and/or verbose you can write to a log file for
68 later debugging.
69
70 By default the function will create the path and file if it does not
71 exist.
72.NOTES
73 Created by: Jason Wasser @wasserja
74 Modified: 4/3/2015 10:29:58 AM
75
76 Changelog:
77 * Renamed LogPath parameter to Path to keep it standard - thanks to @JeffHicks
78 * Revised the Force switch to work as it should - thanks to @JeffHicks
79
80 To Do:
81 * Add error handling if trying to create a log file in a inaccessible location.
82 * Add ability to write $Message to $Verbose or $Error pipelines to eliminate
83 duplicates.
84
85.EXAMPLE
86 Write-Log -Message "Log message"
87 Writes the message to c:\Logs\PowerShellLog.log
88.EXAMPLE
89 Write-Log -Message "Restarting Server" -Path c:\Logs\Scriptoutput.log
90 Writes the content to the specified log file and creates the path and file specified.
91.EXAMPLE
92 Write-Log -Message "Does not exist" -Path c:\Logs\Script.log -Level Error
93 Writes the message to the specified log file as an error message, and writes the message to the error pipeline.
94#>
95function Write-Log
96{
97 [CmdletBinding()]
98 #[Alias('wl')]
99 [OutputType([int])]
100 Param
101 (
102 # The string to be written to the log.
103 [Parameter(Mandatory=$true,
104 ValueFromPipelineByPropertyName=$true,
105 Position=0)]
106 [ValidateNotNullOrEmpty()]
107 [Alias("LogContent")]
108 [string]$Message,
109
110 # The path to the log file.
111 [Parameter(Mandatory=$false,
112 ValueFromPipelineByPropertyName=$true,
113 Position=1)]
114 [Alias('LogPath')]
115 [string]$Path="C:\Logs\PowerShellLog.log",
116
117 [Parameter(Mandatory=$false,
118 ValueFromPipelineByPropertyName=$true,
119 Position=3)]
120 [ValidateSet("Error","Warn","Info")]
121 [string]$Level="Info",
122
123 [Parameter(Mandatory=$false)]
124 [switch]$NoClobber
125 )
126
127 Begin
128 {
129 }
130 Process
131 {
132
133 if ((Test-Path $Path) -AND $NoClobber) {
134 Write-Warning "Log file $Path already exists, and you specified NoClobber. Either delete the file or specify a different name."
135 Return
136 }
137
138 # If attempting to write to a log file in a folder/path that doesn't exist
139 # to create the file include path.
140 elseif (!(Test-Path $Path)) {
141 Write-Verbose "Creating $Path."
142 $NewLogFile = New-Item $Path -Force -ItemType File
143 }
144
145 else {
146 # Nothing to see here yet.
147 }
148
149 # Now do the logging and additional output based on $Level
150 switch ($Level) {
151 'Error' {
152 Write-Error $Message
153 Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") ERROR: $Message" | Out-File -FilePath $Path -Append
154 }
155 'Warn' {
156 Write-Warning $Message
157 Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") WARNING: $Message" | Out-File -FilePath $Path -Append
158 }
159 'Info' {
160 Write-Verbose $Message
161 Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") INFO: $Message" | Out-File -FilePath $Path -Append
162 }
163 }
164 }
165 End
166 {
167 }
168}
169
170function Catch-Block
171{
172param ([string]$Additional)
173$ErrorMessage = " On $Connection " + $Additional + $_.Exception.Message + $_.Exception.InnerException.InnerException.message
174$Message = " This message came from the Automated Powershell script updating the DBA Database with SQL Information"
175$Msg = $Additional + $ErrorMessage + " " + $Message
176Write-Log -Path $LogFile -Message $ErrorMessage -Level Error
177#Write-EventLog -LogName Application -Source "SQLAUTOSCRIPT" -EventId 1 -EntryType Error -Message $Msg
178}
179
180
181# Create Log File
182
183try{
184New-Item -Path $LogFile -ItemType File
185$Msg = "New File Created"
186Write-Log -Path $LogFile -Message $Msg
187}
188catch
189{
190$ErrorMessage = $_.Exception.Message
191$FailedItem = $_.Exception.ItemName
192$Message = " This message came from the Automated Powershell script updating the DBA Database with SQL Information"
193
194$Msg = $ErrorMessage + " " + $FailedItem + " " + $Message
195#Write-EventLog -LogName Application -Source "SQLAUTOSCRIPT" -EventId 1 -EntryType Error -Message $Msg
196}
197
198
199Write-Log -Path $LogFile -Message " Script Started"
200
201 $Query = @"
202 SELECT [ServerName]
203 ,[InstanceName]
204 ,[Port]
205 FROM [DBADatabase].[dbo].[InstanceList]
206 Where Inactive = 0
207 AND NotContactable = 0
208 AND DatabaseEngine = 'Microsoft SQL Server'
209"@
210
211
212
213try{
214$AlltheServers= Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query
215$ServerNames = $AlltheServers| Select ServerName,InstanceName,Port
216}
217catch
218{
219Catch-Block " Failed to gather Server and Instance names from the DBA Database"
220}
221
222foreach ($ServerName in $ServerNames)
223{
224## $ServerName
225 $InstanceName = $ServerName|Select InstanceName -ExpandProperty InstanceName
226 $Port = $ServerName| Select Port -ExpandProperty Port
227$ServerName = $ServerName|Select ServerName -ExpandProperty ServerName
228 $Connection = $ServerName + '\' + $InstanceName + ',' + $Port
229 try
230 {
231 $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Connection
232 }
233catch
234{
235Catch-Block " Failed to connect to $Connection"
236}
237if (!( $srv.version)){
238 Catch-Block " Failed to Connect to $Connection"
239 continue
240 }
241
242 foreach($Alert in $srv.JobServer.Alerts)
243 {
244 $LastOccurrenceDate = $Alert.LastOccurrenceDate
245 if ($LastOccurrenceDate -eq '01/01/0001 00:00:00') { $LastOccurrenceDate = $null }
246 $LastResponseDate = $Alert.LastResponseDate
247 if ($LastResponseDate -eq '01/01/0001 00:00:00') { $LastResponseDate = $null }
248 if($Alert.WmiEventQuery)
249 {$WmiEventQuery = $Alert.WmiEventQuery.Replace("'","''")}
250$Date= Get-Date
251 # Check if Entry already exists
252 try{
253 $query = @"
254 SELECT [AlertsID]
255 FROM [DBADatabase].[Info].[Alerts] as A
256 JOIN
257[DBADatabase].[dbo].[InstanceList] as IL
258ON
259IL.[InstanceID] = A.InstanceID
260 WHERE IL.ServerName = '$ServerName'
261 AND A.Name = '$($Alert.Name)'
262"@
263# $Query
264$Exists = Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $Query
265}
266catch
267{Catch-Block " Failed to gather Alert Name for Exists check $ServerName $InstanceName "
268Break}
269
270if($Exists)
271{
272 $Query = @"
273 UPDATE [Info].[Alerts]
274 SET [CheckDate] = '$Date'
275 ,[InstanceID] = (SELECT InstanceID FROM dbo.InstanceList WHERE ServerName = '$servername')
276 ,[Name] = '$($Alert.Name)'
277 ,[Category] = '$($Alert.Category)'
278 ,[DatabaseID] = (SELECT d.DatabaseID from info.Databases d JOIN dbo.InstanceList IL ON d.InstanceID = IL.InstanceID WHERE IL.ServerName = '$Servername' AND d.Name = '$($Alert.DatabaseName)')
279 ,[DelayBetweenResponses] = '$($Alert.DelayBetweenResponses)'
280 ,[EventDescriptionKeyword] = '$($Alert.EventDescriptionKeyword)'
281 ,[EventSource] = '$($Alert.EventSource)'
282 ,[HasNotification] = '$($Alert.HasNotification)'
283 ,[IncludeEventDescription] = '$($Alert.IncludeEventDescription)'
284 ,[IsEnabled] = '$($Alert.IsEnabled)'
285 ,[AgentJobDetailID] = '$($Alert.AgentJobDetailID)'
286 ,[LastOccurrenceDate] = '$LastOccurrenceDate'
287 ,[LastResponseDate] = '$LastResponseDate'
288 ,[MessageID] = '$($Alert.MessageID)'
289 ,[NotificationMessage] = '$($Alert.NotificationMessage)'
290 ,[OccurrenceCount] = '$($Alert.OccurrenceCount)'
291 ,[PerformanceCondition] = '$($Alert.PerformanceCondition)'
292 ,[Severity] = '$($Alert.Severity)'
293 ,[WmiEventNamespace] = '$($Alert.WmiEventNamespace)'
294 ,[WmiEventQuery] = '$WmiEventQuery'
295 WHERE [AlertsID] = ( SELECT [AlertsID]
296 FROM [DBADatabase].[Info].[Alerts] as A
297 JOIN
298[DBADatabase].[dbo].[InstanceList] as IL
299ON
300IL.[InstanceID] = A.InstanceID
301 WHERE IL.ServerName = '$ServerName'
302 AND A.Name = '$($Alert.Name)')
303
304"@
305}
306else
307{
308$Query = @"
309INSERT INTO [Info].[Alerts]
310 ([CheckDate]
311 ,[InstanceID]
312 ,[Name]
313 ,[Category]
314 ,[DatabaseID]
315 ,[DelayBetweenResponses]
316 ,[EventDescriptionKeyword]
317 ,[EventSource]
318 ,[HasNotification]
319 ,[IncludeEventDescription]
320 ,[IsEnabled]
321 ,[AgentJobDetailID]
322 ,[LastOccurrenceDate]
323 ,[LastResponseDate]
324 ,[MessageID]
325 ,[NotificationMessage]
326 ,[OccurrenceCount]
327 ,[PerformanceCondition]
328 ,[Severity]
329 ,[WmiEventNamespace]
330 ,[WmiEventQuery])
331 VALUES
332 ('$Date'
333 ,(SELECT InstanceID FROM dbo.InstanceList WHERE ServerName = '$servername')
334 ,'$($Alert.Name)'
335 ,'$($Alert.Category)'
336 ,(SELECT d.DatabaseID from info.Databases d JOIN dbo.InstanceList IL ON d.InstanceID = IL.InstanceID WHERE IL.ServerName = '$Servername' AND d.Name = '$($Alert.DatabaseName)')
337 ,'$($Alert.DelayBetweenResponses)'
338 ,'$($Alert.EventDescriptionKeyword)'
339 ,'$($Alert.EventSource)'
340 ,'$($Alert.HasNotification)'
341 ,'$($Alert.IncludeEventDescription)'
342 ,'$($Alert.IsEnabled)'
343 ,'$($Alert.AgentJobDetailID)'
344 ,'$LastOccurrenceDate'
345 ,'$LastResponseDate'
346 ,'$($Alert.MessageID)'
347 ,'$($Alert.NotificationMessage)'
348 ,'$($Alert.OccurrenceCount)'
349 ,'$($Alert.PerformanceCondition)'
350 ,'$($Alert.Severity)'
351 ,'$($Alert.WmiEventNamespace)'
352 ,'$($WmiEventQuery)')
353"@
354}
355try{
356# $Query
357Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query -ErrorAction Stop
358}
359catch
360{
361Catch-Block " Failed to insert information for $Name on $Connection $query"
362}
363}
364
365 $Msg = " Info added for $Connection"
366Write-Log -Path $LogFile -Message $Msg
367}
368 Write-Log -Path $LogFile -Message "Script Finished"