-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathSCOM-SQLDashboard.ps1
167 lines (119 loc) · 6.43 KB
/
SCOM-SQLDashboard.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
162
163
164
165
166
167
<#
This script is a base script for a sql dashboard based on Powersell Universall Dashboard.
#>
Import-Module universaldashboard.community
if(Get-UDDashboard | where {$_.Port -eq 10030}) {Stop-UDDashboard -port 10030}
$Color="#00FF00","#FFA500","#000080","#FFFF00","#FF0000","#4169E1","#9ACD32","#FF6347","#EE82EE","#40E0D0","#F0FFFF","#00FFFF"
$ms=$env:computername
$ProblemServerCount={
New-SCOMManagementGroupConnection -ComputerName $ms
$MonitorColl = @()
$MonitorColl = New-Object "System.Collections.Generic.List[Microsoft.EnterpriseManagement.Configuration.ManagementPackMonitor]"
$objects = get-scomclass -DisplayName "Health Service Watcher" | Get-SCOMClassInstance
$monitor = Get-SCOMMonitor -DisplayName 'Computer Not Reachable'
$Servers= @()
#Add this monitor to a collection
#$MonitorColl.Add($Monitor)
#Get the state associated with this specific monitor
#$State=$Object.getmonitoringstates($MonitorColl)
ForEach ($object in $objects)
{
#Set the monitor collection to empty and create the collection to contain monitors
$MonitorColl = @()
$MonitorColl = New-Object "System.Collections.Generic.List[Microsoft.EnterpriseManagement.Configuration.ManagementPackMonitor]"
#Get specific monitors matching a displayname for this instance of URLtest ONLY
#$Monitor = Get-SCOMMonitor -Instance $object -Recurse| where {$_.DisplayName -eq "Computer Not Reachable"}
#Add this monitor to a collection
$MonitorColl.Add($Monitor)
#Get the state associated with this specific monitor
$State=$object.getmonitoringstates($MonitorColl)
if ($state.HealthState -eq 'Error') {
$Props=@{}
$Props.ServerName=$Object.DisplayName
$Props.Reachable=$state.HealthState
$Server=New-Object -TypeName PSCustomObject -Property $props
$Servers+=$Server
}
}
$Servers.Count
}
$PageLifeExpactency = {
New-SCOMManagementGroupConnection -ComputerName $ms
$MonitorColl = @()
$MonitorColl = New-Object "System.Collections.Generic.List[Microsoft.EnterpriseManagement.Configuration.ManagementPackMonitor]"
$objects = get-scomclass -DisplayName "SQL DB Engine" | Get-SCOMClassInstance
$monitors = Get-SCOMMonitor -DisplayName 'Page Life Expectancy'
$Servers= @()
#Add this monitor to a collection
#$MonitorColl.Add($Monitor)
#Get the state associated with this specific monitor
#$State=$Object.getmonitoringstates($MonitorColl)
ForEach ($object in $objects)
{
#Set the monitor collection to empty and create the collection to contain monitors
$MonitorColl = @()
$MonitorColl = New-Object "System.Collections.Generic.List[Microsoft.EnterpriseManagement.Configuration.ManagementPackMonitor]"
#Get specific monitors matching a displayname for this instance of URLtest ONLY
#$Monitor = Get-SCOMMonitor -Instance $object -Recurse| where {$_.DisplayName -eq "Computer Not Reachable"}
#Add this monitor to a collection
$monitor = $monitors | where {$_.Target.Identifier.Path -eq $object.GetMostDerivedClasses().Identifier.Path}
<#if ($monitor.Target.Identifier.Path -contains $object.GetMostDerivedClasses().Identifier.Path){
$MonitorColl.Add($Monitor)
}#>
$MonitorColl.Add($Monitor)
#Get the state associated with this specific monitor
$State=$object.getmonitoringstates($MonitorColl)
if ($state.HealthState -eq 'Error') {
$Props=@{}
$Props.ServerName=$Object.DisplayName
$Props.Reachable=$state.HealthState
$Server=New-Object -TypeName PSCustomObject -Property $props
$Servers+=$Server
}
}
$Servers.Count
}
$TopServerRestarts={
New-SCOMManagementGroupConnection -ComputerName $ms
$rule=get-scomrule -DisplayName "Collection Rule for Windows Restarted Events"
$restarts=Get-SCOMEvent -Rule $rule | where {$_.TimeGenerated -gt (Get-Date).AddDays(-7) } | Group-Object -Property LoggingComputer |Sort-Object -Property Count -Descending | Select-Object -first 10 -Property Count,@{Name='Name';Expression={$_.Name -split "\."|select-object -first 1}}
$restarts | Out-UDChartData -LabelProperty "Name" -Dataset @(New-UdChartDataset -DataProperty "Count" -BackgroundColor ($Color|get-random -Count 10) -HoverBorderColor red)
}
$ServerGenaratingAlerts={
New-SCOMManagementGroupConnection -ComputerName $ms
$alerts=Get-SCOMAlert | where {$_.ResolutionState -eq 0 -and $_.NetbiosComputerName -ne $null} | Group-Object -Property NetbiosComputerName | Sort-Object -Property Count -Descending |Select-Object -Property Name,Count -first 10
$alerts| Out-UDChartData -LabelProperty "Name" -Dataset @(New-UdChartDataset -DataProperty "Count" -BackgroundColor ($Color|get-random -Count 10) -HoverBorderColor red)
}
$OSVersions={
New-SCOMManagementGroupConnection -ComputerName $ms
$OS=get-scomclass -Name "SQL.DB.Engine" | get-scomclassInstance | Group-Object -Property `[Microsoft.SQLServer.DBEngine].Edition | Sort-Object -Property Count -Descending | Select-Object Count,Name
$os| Out-UDChartData -LabelProperty "Name" -Dataset @(New-UdChartDataset -DataProperty "Count" -BackgroundColor ($Color|get-random -Count 10) -HoverBorderColor red)
}
$SQLEditions={
New-SCOMManagementGroupConnection -ComputerName $ms
$editions=get-scomclass -Name "Microsoft.SQLServer.DBEngine" | get-scomclassinstance |Select-Object -ExpandProperty *.Edition | Group-Object -Property Value | Select-Object -Property Count,Name
$editions | Out-UDChartData -LabelProperty "Name" -Dataset @(New-UdChartDataset -DataProperty "Count" -BackgroundColor ($Color|get-random -Count 10) -HoverBorderColor red)
}
$PHome=New-UDPage -Name "Home" -icon dashboard -Content {
New-UDRow {
New-UDColumn -Size 2 -Content {
New-UDCounter -Format "0000" -Title "Unreachable Servers" -AutoRefresh -RefreshInterval 300 -Endpoint $ProblemServerCount -FontColor "White" -TextSize Small -TextAlignment center -BackgroundColor "black"
}
}
New-UDRow {
New-UdColumn -Size 3 -Content{
New-UdChart -Title "Top Server Restarts" -Type HorizontalBar -Endpoint $TopServerRestarts -AutoRefresh -RefreshInterval 600
}
New-UdColumn -Size 3 -Content{
New-UdChart -Title "Top Alerting Servers" -Type HorizontalBar -Endpoint $ServerGenaratingAlerts -AutoRefresh -RefreshInterval 600
}
New-UdColumn -Size 6 -Content{
New-UdChart -Title "Server OS" -Type Pie -Endpoint $OSVersions -AutoRefresh -RefreshInterval 600
}
}
}
$Theme = Get-UDTheme -Name Azure
$Dashboard=New-UDDashboard -Title "Server Information" -Footer $footer -Pages @($PHome) -Theme $Theme
Start-UDDashboard -Dashboard $Dashboard -Port 10018
#@{Name='Name';Expression={$_.Name -split "\."|select-object -first 1}}
'`[Microsoft.SQLServer.DBEngine].Edition'