-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path\Insert-SCOMAlertsToSQL.ps1
161 lines (104 loc) · 5.63 KB
/
\Insert-SCOMAlertsToSQL.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
<#
.Synopsis
Inserts selected scom alerts to custom database
.DESCRIPTION
Inserts selected scom alerts to custom database
.EXAMPLE
.\Insert-SCOMAlertsToSQL.ps1 -AlertTablePath .\SCOMAlerts.psd1 -ManagementServer 'scomms1' -SQLServer 'scomdb1' -Instance 'default,1981' -Database 'SCOMDashboard' -Verbose
VERBOSE: [5.03.2020 18:27:50] Script Started.
VERBOSE: [5.03.2020 18:27:50] Already connected to a Management Group.
VERBOSE: [5.03.2020 18:27:50] Returned 17 number of alert names
VERBOSE: [5.03.2020 18:28:04]Found Alerts Table dropping.
WARNING: Using provider context. Server = opwscomdb1\default,1977, Database = [SCOMDashboard].
VERBOSE: [5.03.2020 18:28:05] Inserted 682 number of alerts
VERBOSE: [5.03.2020 18:28:05] Script ended.Script dutation is 15.1515768
#>
#requires -version 5.1 -Modules SqlServer,OperationsManager
[CmdletBinding()]
Param(
[Parameter(Mandatory= $true)]
[ValidateScript({test-path $_})]
[string]$AlertTablePath,
[Parameter(Mandatory= $true)]
[string]$ManagementServer='Ovwscommng1',
[Parameter(Mandatory= $true)]
[string]$SQLServer,
[Parameter(Mandatory= $true)]
[string]$Instance,
[Parameter(Mandatory= $true)]
[string]$Database
)
Function Connect-ToSCOM {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[string]$ManagementServer
)
if (!(Get-SCOMManagementGroup)) {
try {
New-SCOMManagementGroupConnection -ComputerName $ManagementServer -ErrorAction Stop
}
Catch {
throw "Could not connect to $ManagementServer . Error: $($_.Exception.Message)"
}
} else {Write-Verbose "[$(Get-date -Format G)] Already connected to a Management Group."}
}
Function Get-AlertNames {
[CmdletBinding()]
Param(
[Parameter(Mandatory=$true)]
[Hashtable]$AlertTable
)
$Result = @()
$AlertTable.Values | ForEach-Object {$Result += $_}
$Result
Write-Verbose "[$(Get-date -Format G)] Returned $(($Result).Count) number of alert names"
}
$ScriptStart = Get-date
$SelectAlertTable = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='$database' and TABLE_NAME = 'Alerts'"
Write-Verbose "[$(Get-Date -Format G)] Script Started."
try {
Connect-ToSCOM -ManagementServer $ManagementServer -ErrorAction Stop
$AlertTable = Import-PowerShellDataFile -Path $AlertTablePath -Verbose:$false
$AlertNames = Get-AlertNames -AlertTable $AlertTable
$ClassId = @{Name = 'ClassId'; Expression = {$_.ClassId.Guid}}
$ManagementGroup = @{Name = 'ManagementGroup'; Expression = {$_.ManagementGroup.Tostring()}}
$ManagementGroupId = @{Name = 'ManagementGroupId'; Expression = {$_.ManagementGroupId.Guid}}
$MonitoringClassId = @{Name = 'MonitoringClassId'; Expression = {$_.MonitoringClassId.Guid}}
$MonitoringObjectId = @{Name = 'MonitoringObjectId'; Expression = {$_.MonitoringObjectId.Guid}}
$ProblemId = @{Name = 'ProblemId'; Expression = {$_.ProblemId.Guid}}
$RuleId = @{Name = 'RuleId'; Expression = {$_.RuleId.Guid}}
$Id = @{Name = 'Id'; Expression = {$_.Id.Guid}}
$Category = @{Name = 'Category'; Expression = {$_.Category.Tostring()}}
$MonitoringRuleId = @{Name = 'MonitoringRuleId'; Expression = {$_.MonitoringRuleId.Guid}}
$MonitoringObjectHealthState = @{Name = 'MonitoringObjectHealthState'; Expression = {$_.MonitoringObjectHealthState.ToString()}}
$Priority = @{Name = 'Priority'; Expression = {$_.Priority.ToString()}}
$ResolutionState = @{'Name' = 'ResolutionState';Expression = {$_.ResolutionState.ToString()}}
$Severity = @{Name = 'Severity'; Expression = {$_.Severity.ToString()}}
$Alerts = (Get-SCOMAlert).Where({$_.Name -in $AlertNames}) | Select-Object -Property $ClassId,Context,CustomField1,CustomField2,CustomField3,CustomField4,CustomField5,CustomField6,CustomField7,CustomField8,CustomField9,CustomField10,Description,$Id,IsmonitorAlert,LastModified, LastModifiedBy,LastModifiedByNonConnector,MaintenanceModeLastModified,$ManagementGroup,$ManagementGroupId,$MonitoringClassId,MonitoringObjectPath,MonitoringObjectDisplayName,MonitoringObjectFullName,MonitoringObjectName,$MonitoringObjectHealthState,$MonitoringObjectId,$MonitoringRuleId,MonitoringObjectInMaintenanceMode,Name,NetbiosComputerName,NetbiosDomainName,Owner,PrincipalName,$Priority,$ProblemId,RepeatCount,$ResolutionState,ResolvedBy,$RuleId,$Severity,SiteName,StateLastModified,TfsWorkItemID,TfsWorkItemOwner,TicketID,TimeAdded,TimeRaised,TimeResolutionStateLastModified,TimeResolved, UnformattedDescription
}
catch {
Throw "Could not connect and get alerts from the scom server $ManagementServer.`nError: $($_.Exception.Message)"
}
Try {
if((Invoke-Sqlcmd -ServerInstance "$SQLServer\$Instance" -Database $Database -Query $SelectAlertTable -ErrorAction stop)) {
Write-Verbose "[$(Get-date -Format G)]Found Alerts Table dropping."
Invoke-Sqlcmd -ServerInstance "$SQLServer\$Instance" -Database $Database -Query 'DROP TABLE [dbo].[Alerts]' -ErrorAction Stop
}
} catch {
Throw "[$(Get-Date -Format G)] Select or delete Alert Table`nError: $($_.Exception.Message)"
}
try {
New-PSDrive -Name SCOMDashboard -PSProvider 'SQLServer' -root "SQLSERVER:\SQL\$SQLServer\$Instance\Databases\$Database" -ErrorAction stop | Out-Null
cd 'SCOMDashboard:\Tables'
Write-SqlTableData -TableName Alerts -InputData $Alerts -Force -SchemaName dbo -ErrorAction Stop
Write-Verbose "[$(Get-Date -Format G)] Inserted $($Alerts.Count) number of alerts"
}
Catch {
Throw "[$(Get-Date -Format G)] Couldnt Insert to SQL.`nError: $($_.Exception.Message)"
}
Finally {
cd c:\
Remove-PSDrive SCOMDashboard
}
Write-verbose "[$(Get-date -Format G)] Script ended.Script dutation is $(((Get-date) - $ScriptStart).TotalSeconds)"