# encoding: ascii
# api: powershell
# title: OpsMgr State Changes
# version: 0.1
# type: script
# license: CC0
# x-poshcode-id: 1518
# x-archived: 2009-12-12T18:34:04
# Date: 09-12-2009
# Author: Stefan Stranger
# Explanation: If you want to know the State Changes for a specific Monitor you can run the next PowerShell script
# You need to have db permissions to the OperationsManagerdw database to run this Script.
# # # You need to run the script from the OpsMgr Command Shell because it uses the Get-Monitor Cmdlet.
#
# Description: State Changes for specified OpsMgr Monitor
# Date: 09-12-2009
# Author: Stefan Stranger
# Explanation: If you want to know the State Changes for a specific Monitor you can run the next PowerShell script
# You need to have db permissions to the OperationsManagerdw database to run this Script.
# You need to run the script from the OpsMgr Command Shell because it uses the Get-Monitor Cmdlet.
param ([string]$monitor = $(read-host "Please enter Monitor Name"), [string]$server = $(read-host "Please enter servername hosting OperationsManagerdw database"))
$MonitorDefaultName = "'" + (get-monitor | where {$_.DisplayName -like $monitor}).DisplayName + "'"
if ($MonitorDefaultName -eq "''") {Write-host "No Monitor found"} else
{
$sqlConnString = "Server=$server;DataBase=OperationsManagerdw;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($sqlConnString)
$cmd = New-Object System.Data.SqlClient.SqlCommand
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$connection.Open() | Out-Null
$cmd.CommandTimeout = 30
$cmd.Connection = $connection
$startsql = @'
SELECT COUNT(*) AS StateChanges, vMonitor.MonitorDefaultName, vManagementPack.ManagementPackDefaultName
FROM State.vStateRaw INNER JOIN
vManagedEntityMonitor ON State.vStateRaw.ManagedEntityMonitorRowId = vManagedEntityMonitor.ManagedEntityMonitorRowId INNER JOIN
vMonitor ON vManagedEntityMonitor.MonitorRowId = vMonitor.MonitorRowId INNER JOIN
vMonitorManagementPackVersion ON vManagedEntityMonitor.MonitorRowId = vMonitorManagementPackVersion.MonitorRowId INNER JOIN
vManagementPack ON vMonitor.ManagementPackRowId = vManagementPack.ManagementPackRowId
WHERE (MonitorDefaultName =
'@
$endsql = @'
)
AND (vMonitorManagementPackVersion.UnitMonitorInd = 1)
GROUP BY vMonitor.MonitorDefaultName, vManagementPack.ManagementPackDefaultName
ORDER BY StateChanges DESC
'@
$totalsqlquery = $startsql + $MonitorDefaultName + $endsql
#Write-host $totalsqlquery
$cmd.CommandText = $totalsqlquery
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$sqlAdapter.SelectCommand = $cmd
$ds = New-Object System.Data.DataSet
$sqlAdapter.Fill($ds) | Out-Null
foreach ($table in $ds.Tables)
{
$table | Format-Table -auto;
}
$connection.Close()
}