dbareports agent info table and code

gistfile1.txt

USE [DBADatabase]
GO

/****** Object:  Table [Info].[Alerts]    Script Date: 21/11/2016 16:50:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Info].[Alerts](
	[AlertsID] [int] IDENTITY(1,1) NOT NULL,
	[CheckDate] [datetime] NULL,
	[InstanceID] [int] NOT NULL,
	[Name] [nvarchar](128) NOT NULL,
	[Category] [nvarchar](128) NULL,
	[DatabaseID] [int] NULL,
	[DelayBetweenResponses] [int] NOT NULL,
	[EventDescriptionKeyword] [nvarchar](100) NULL,
	[EventSource] [nvarchar](100) NULL,
	[HasNotification] [int] NOT NULL,
	[IncludeEventDescription] [nvarchar](128) NOT NULL,
	[IsEnabled] [bit] NOT NULL,
	[AgentJobDetailID] [int] NULL,
	[LastOccurrenceDate] [datetime] NOT NULL,
	[LastResponseDate] [datetime] NOT NULL,
	[MessageID] [int] NOT NULL,
	[NotificationMessage] [nvarchar](512) NULL,
	[OccurrenceCount] [int] NOT NULL,
	[PerformanceCondition] [nvarchar](512) NULL,
	[Severity] [int] NOT NULL,
	[WmiEventNamespace] [nvarchar](512) NULL,
	[WmiEventQuery] [nvarchar](512) NULL
) ON [PRIMARY]

GO

 <# 
.SYNOPSIS  
     This Script will check all of the instances in the InstanceList and gather the Alert Information to the Info.Alerts table

.DESCRIPTION 
     This Script will check all of the instances in the InstanceList and gather the Alert Information to the Info.Alerts table
.PARAMETER 

.EXAMPLE 



.NOTES 
    AUTHOR: Rob Sewell sqldbawithabeard.com 
    DATE: 21/11/2016 - Initial
#> 


# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$CentralDBAServer = ''
$CentralDatabaseName = ''
$Date = Get-Date -Format ddMMyyyy_HHmmss
$LogFile = "\DBADatabaseAlertUpdate_" + $Date + ".log"
<#
.Synopsis
   Write-Log writes a message to a specified log file with the current time stamp.
.DESCRIPTION
   The Write-Log function is designed to add logging capability to other scripts.
   In addition to writing output and/or verbose you can write to a log file for
   later debugging.

   By default the function will create the path and file if it does not 
   exist. 
.NOTES
   Created by: Jason Wasser @wasserja
   Modified: 4/3/2015 10:29:58 AM 

   Changelog:
    * Renamed LogPath parameter to Path to keep it standard - thanks to @JeffHicks
    * Revised the Force switch to work as it should - thanks to @JeffHicks

   To Do:
    * Add error handling if trying to create a log file in a inaccessible location.
    * Add ability to write $Message to $Verbose or $Error pipelines to eliminate
      duplicates.

.EXAMPLE
   Write-Log -Message "Log message" 
   Writes the message to c:\Logs\PowerShellLog.log
.EXAMPLE
   Write-Log -Message "Restarting Server" -Path c:\Logs\Scriptoutput.log
   Writes the content to the specified log file and creates the path and file specified. 
.EXAMPLE
   Write-Log -Message "Does not exist" -Path c:\Logs\Script.log -Level Error
   Writes the message to the specified log file as an error message, and writes the message to the error pipeline.
#>
function Write-Log
{
    [CmdletBinding()]
    #[Alias('wl')]
    [OutputType([int])]
    Param
    (
        # The string to be written to the log.
        [Parameter(Mandatory=$true,
                   ValueFromPipelineByPropertyName=$true,
                   Position=0)]
        [ValidateNotNullOrEmpty()]
        [Alias("LogContent")]
        [string]$Message,

        # The path to the log file.
        [Parameter(Mandatory=$false,
                   ValueFromPipelineByPropertyName=$true,
                   Position=1)]
        [Alias('LogPath')]
        [string]$Path="C:\Logs\PowerShellLog.log",

        [Parameter(Mandatory=$false,
                    ValueFromPipelineByPropertyName=$true,
                    Position=3)]
        [ValidateSet("Error","Warn","Info")]
        [string]$Level="Info",

        [Parameter(Mandatory=$false)]
        [switch]$NoClobber
    )

    Begin
    {
    }
    Process
    {
        
        if ((Test-Path $Path) -AND $NoClobber) {
            Write-Warning "Log file $Path already exists, and you specified NoClobber. Either delete the file or specify a different name."
            Return
            }

        # If attempting to write to a log file in a folder/path that doesn't exist
        # to create the file include path.
        elseif (!(Test-Path $Path)) {
            Write-Verbose "Creating $Path."
            $NewLogFile = New-Item $Path -Force -ItemType File
            }

        else {
            # Nothing to see here yet.
            }

        # Now do the logging and additional output based on $Level
        switch ($Level) {
            'Error' {
                Write-Error $Message
                Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") ERROR: $Message" | Out-File -FilePath $Path -Append
                }
            'Warn' {
                Write-Warning $Message
                Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") WARNING: $Message" | Out-File -FilePath $Path -Append
                }
            'Info' {
                Write-Verbose $Message
                Write-Output "$(Get-Date -Format "yyyy-MM-dd HH:mm:ss") INFO: $Message" | Out-File -FilePath $Path -Append
                }
            }
    }
    End
    {
    }
}

function Catch-Block
{
param ([string]$Additional)
$ErrorMessage = " On $Connection " + $Additional + $_.Exception.Message + $_.Exception.InnerException.InnerException.message
$Message = " This message came from the Automated Powershell script updating the DBA Database with SQL Information"
$Msg = $Additional + $ErrorMessage + " " + $Message
Write-Log -Path $LogFile -Message $ErrorMessage -Level Error
#Write-EventLog -LogName Application -Source "SQLAUTOSCRIPT" -EventId 1 -EntryType Error -Message $Msg
}


# Create Log File

try{
New-Item -Path $LogFile -ItemType File
$Msg = "New File Created"
Write-Log -Path $LogFile -Message $Msg
}
catch
{
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
$Message = " This message came from the Automated Powershell script updating the DBA Database with SQL Information"

$Msg = $ErrorMessage + " " + $FailedItem + " " + $Message
#Write-EventLog -LogName Application -Source "SQLAUTOSCRIPT" -EventId 1 -EntryType Error -Message $Msg
}


Write-Log -Path $LogFile -Message " Script Started"

 $Query = @"
 SELECT [ServerName]
      ,[InstanceName]
      ,[Port]
  FROM [DBADatabase].[dbo].[InstanceList]
  Where Inactive = 0 
  AND NotContactable = 0
  AND DatabaseEngine = 'Microsoft SQL Server'
"@



try{
$AlltheServers= Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query
$ServerNames = $AlltheServers| Select ServerName,InstanceName,Port
}
catch
{
Catch-Block " Failed to gather Server and Instance names from the DBA Database"
}

foreach ($ServerName in $ServerNames)
{
## $ServerName
 $InstanceName =  $ServerName|Select InstanceName -ExpandProperty InstanceName
 $Port = $ServerName| Select Port -ExpandProperty Port
$ServerName = $ServerName|Select ServerName -ExpandProperty ServerName 
 $Connection = $ServerName + '\' + $InstanceName + ',' + $Port
 try
 {
 $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $Connection
 }
catch
{
Catch-Block " Failed to connect to $Connection"
}
if (!( $srv.version)){
 Catch-Block " Failed to Connect to $Connection"
 continue
 }

 foreach($Alert in $srv.JobServer.Alerts)
 {
    $LastOccurrenceDate = $Alert.LastOccurrenceDate
    if ($LastOccurrenceDate -eq '01/01/0001 00:00:00') { $LastOccurrenceDate = $null } 
    $LastResponseDate =  $Alert.LastResponseDate
    if ($LastResponseDate -eq '01/01/0001 00:00:00') { $LastResponseDate = $null } 
    if($Alert.WmiEventQuery)
    {$WmiEventQuery = $Alert.WmiEventQuery.Replace("'","''")}
$Date= Get-Date
 # Check if Entry already exists
 try{
  $query = @"
 SELECT  [AlertsID]
    FROM [DBADatabase].[Info].[Alerts] as A
  JOIN
[DBADatabase].[dbo].[InstanceList] as IL
ON
IL.[InstanceID] = A.InstanceID
   WHERE IL.ServerName = '$ServerName' 
 AND A.Name = '$($Alert.Name)'
"@
# $Query
$Exists = Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $Query
}
catch
{Catch-Block " Failed to gather Alert Name for Exists check $ServerName $InstanceName "
Break}

if($Exists)
{
 $Query = @"
 UPDATE [Info].[Alerts]
   SET [CheckDate] = '$Date'
      ,[InstanceID] = (SELECT InstanceID FROM dbo.InstanceList WHERE ServerName = '$servername')
      ,[Name] = '$($Alert.Name)'
      ,[Category] = '$($Alert.Category)'
      ,[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)')
      ,[DelayBetweenResponses] = '$($Alert.DelayBetweenResponses)'
      ,[EventDescriptionKeyword] = '$($Alert.EventDescriptionKeyword)'
      ,[EventSource] = '$($Alert.EventSource)'
      ,[HasNotification] = '$($Alert.HasNotification)'
      ,[IncludeEventDescription] = '$($Alert.IncludeEventDescription)'
      ,[IsEnabled] = '$($Alert.IsEnabled)'
      ,[AgentJobDetailID] = '$($Alert.AgentJobDetailID)'
      ,[LastOccurrenceDate] = '$LastOccurrenceDate'
      ,[LastResponseDate] = '$LastResponseDate'
      ,[MessageID] = '$($Alert.MessageID)'
      ,[NotificationMessage] = '$($Alert.NotificationMessage)'
      ,[OccurrenceCount] = '$($Alert.OccurrenceCount)'
      ,[PerformanceCondition] = '$($Alert.PerformanceCondition)'
      ,[Severity] = '$($Alert.Severity)'
      ,[WmiEventNamespace] = '$($Alert.WmiEventNamespace)'
      ,[WmiEventQuery] = '$WmiEventQuery'
      WHERE [AlertsID] = ( SELECT  [AlertsID]
    FROM [DBADatabase].[Info].[Alerts] as A
  JOIN
[DBADatabase].[dbo].[InstanceList] as IL
ON
IL.[InstanceID] = A.InstanceID
   WHERE IL.ServerName = '$ServerName' 
 AND A.Name = '$($Alert.Name)')
            
"@
}
else
{
$Query = @"
INSERT INTO [Info].[Alerts]
           ([CheckDate]
           ,[InstanceID]
           ,[Name]
           ,[Category]
           ,[DatabaseID]
           ,[DelayBetweenResponses]
           ,[EventDescriptionKeyword]
           ,[EventSource]
           ,[HasNotification]
           ,[IncludeEventDescription]
           ,[IsEnabled]
           ,[AgentJobDetailID]
           ,[LastOccurrenceDate]
           ,[LastResponseDate]
           ,[MessageID]
           ,[NotificationMessage]
           ,[OccurrenceCount]
           ,[PerformanceCondition]
           ,[Severity]
           ,[WmiEventNamespace]
           ,[WmiEventQuery])
     VALUES
           ('$Date'
           ,(SELECT InstanceID FROM dbo.InstanceList WHERE ServerName = '$servername')
           ,'$($Alert.Name)'
           ,'$($Alert.Category)'
           ,(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)')
           ,'$($Alert.DelayBetweenResponses)'
           ,'$($Alert.EventDescriptionKeyword)'
           ,'$($Alert.EventSource)'
           ,'$($Alert.HasNotification)'
           ,'$($Alert.IncludeEventDescription)'
           ,'$($Alert.IsEnabled)'
           ,'$($Alert.AgentJobDetailID)'
           ,'$LastOccurrenceDate'
           ,'$LastResponseDate'
           ,'$($Alert.MessageID)'
           ,'$($Alert.NotificationMessage)'
           ,'$($Alert.OccurrenceCount)'
           ,'$($Alert.PerformanceCondition)'
           ,'$($Alert.Severity)'
           ,'$($Alert.WmiEventNamespace)'
           ,'$($WmiEventQuery)')
"@
}
try{
# $Query
Invoke-Sqlcmd -ServerInstance $CentralDBAServer -Database $CentralDatabaseName -Query $query -ErrorAction Stop
}
catch
{
Catch-Block " Failed to insert information for $Name on $Connection $query"
}
}

 $Msg = " Info added for $Connection"
Write-Log -Path $LogFile -Message $Msg
}
 Write-Log -Path $LogFile -Message "Script Finished"