[OPsMgr 2007 R2][OpsMgr2012] SQL Management pack new version 6.6.2.0 #ManagementPack #OpsMgr #SCOM

New SQL MPs has been delivered by Microsoft yesterday (Oct. the 14th)

MP Name: 'SQL Server 2014'  has been released in version '6.6.2.0' : http://www.microsoft.com/en-us/download/details.aspx?id=42573

Release Date

Changes

October, 2015 (version 6.6.2.0)

·         Fixed performance ·         Added a support for disabled TCP/IP protocol ·         Fixed performance metrics error that may occur on some localized versions of Windows ·         Fixed incorrect performance of Transaction log free space monitor ·         Added new type of events from failed discoveries; added a new reporting rule that collects such events ·         Added overrides to prevent various scripts timeout failure ·         Removed some 1X1 tiles from Summary Dashboards ·         Filestream filegroups are excluded from discovery for now ·         2008/2012 Summary Dashboards tiles were reorganized ·         Added KB for Microsoft.SQLServer.2014.Mirroring.MirroringCommonGroupDiscovery ·         Summary Dashboard: added monitor/performance tiles to class "SQL Server 2014 Mirroring Groups" ·         Some minor fixes

MP Name: 'SQL Server 2005 2008 2012'  has been released in version '6.6.2.0' : https://www.microsoft.com/en-us/download/details.aspx?id=10631

 

Release Date

Changes

October, 2015 (version 6.6.2.0)

·         Added a support for disabled TCP/IP protocol ·         Fixed performance metrics error that may occur on some localized versions of Windows ·         Fixed bugs in monitor tiles on SQL Server Summary Dashboard ·         Fixed incorrect performance of Transaction log free space monitor ·         Added new type of events from failed discoveries; added a new rule that collects such events ·         Added overrides to prevent various scripts timeout failure ·         Removed some 1X1 tiles from Summary Dashboards ·         Filestream filegroups are excluded from discovery for now ·         2008/2012 Summary Dashboards tiles were reorganized

MP Name: 'SQL Server 2014 Replication'  has been released in version '6.6.2.0' : http://www.microsoft.com/en-us/download/details.aspx?id=47720

 

Release Date	Changes
October, 2015 (version 6.6.2.0)	Preset dashboards are updated

MP Name: 'SQL Server 2012 Replication'  has been released in version '6.6.2.0'  : http://www.microsoft.com/en-us/download/details.aspx?id=47721

Release Date	Changes
October, 2015 (version 6.6.2.0)	Preset dashboards are updated

 

 

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2] [OpsMgr 2012] Microsoft System Center Management Pack for SQL Server #SQL #OpsMgr

We just have in place the last version of the SQL Monitoring in our productions environment and Microsoft has released a new one. New version is now 6.6.0.0 and please find here the different link on the Microsoft site.  

 

 

- 'SQL Server 2005 2008 2012'  Version: '6.6.0.0'  Released on: '12-15-2014'

- 'SQL Server 2014'  Version: '6.6.0.0'  Released on: '12-15-2014'
- 'SQL Server 2008 Reporting Services (Native Mode)'  Version: '6.6.0.0'  Released on: '11-4-2014'
- 'SQL Server 2012 Reporting Services (Native Mode)'  Version: '6.6.0.0'  Released on: '11-4-2014'
- 'SQL Server 2012 Analysis Services'  Version: '6.6.0.0'  Released on: '11-4-2014'
- 'SQL Server 2008 Analysis Services'  Version: '6.6.0.0'  Released on: '11-4-2014'
- 'SQL Server 2014 Reporting Services (Native Mode)'  Version: '6.6.0.0'  Released on: '11-4-2014'
- 'SQL Server 2014 Analysis Services'  Version: '6.6.0.0'  Released on: '11-4-2014'

 

The Management pack for SQL Server provides the capabilities for Operations Manager 2007 R2 and Operations Manager 2012 to discover SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

The monitoring provided by this management pack includes performance, availability, and configuration monitoring, performance data collection, and default thresholds. You can integrate the monitoring of SQL Server components into your service-oriented monitoring scenarios.

In addition to health monitoring capabilities, this management pack includes dashboard views, diagram views and extensive knowledge with embedded inline tasks, and views that enable near real-time diagnosis and resolution of detected issues.

Important Prerequisite Notes:
Clusters: In order to ensure that all monitoring works correctly for clustered instances of SQL Server ensure that your OpsMgr agents on the physical nodes of the cluster are running either OpsMgr 2007 R2 or OpsMgr 2007 SP1 with the most recent cumulative update for OpsMgr 2007 SP1 applied or OpsMgr 2012. 

Changes in June 2015 Update of the SQL 2005,2008,2012 and 2014 Management pack

SQL Server Management Pack version 6.6.0.0 includes the following changes:

·         Dashboards were replaced with the new ones
·         Components of replication functionality are deprecated and disabled by default
·        SPN monitor now correctly handles disjoined namespaces
·         Added support for filegroups containing filestreams and partition schemes
·         Memory Consumption monitor has been fixed
·         Upgradeability from 6.4.1.0 version is supported
·         Added CPU Usage monitor and rule for SQL Server 2005
·         Added ConsecutiveSamples Condition to the Buffer Cache Hit Ratio and Page Life Expectancy monitors
·         AlwaysOn discovery was reworked
·         Minor fixes.


For more information, read the détails on the different Microsoft download pages.
 

This posting is provided "AS IS" with no warranties.

[OpsMgr 2012] System Center Management Pack for SQL Server 2008, 2012 and 2014 Analysis Services #OpsMgr2012 #ManagementPack #SQLServer

This week, Microsoft released Management Packs for SQL Server 2008, SQL Server 2012 and SQL Server 2014  Analysis Services.

The corresponding downloads are available at:

• System  Center Management Pack for SQL Server 2008 Analysis Services
• System  Center Management Pack for SQL Server 2012 Analysis Services
• System  Center Management Pack for SQL Server 2014 Analysis Services

The System Center Management Pack for SQL Server Analysis Services enables the discovery and monitoring of SQL Server Analysis Services Engines, Databases and Partitions.

These Management Pack are designed to be run by Operations Manager 2012 RTM or higher.

 

The monitoring provided by these management pack includes performance, availability and configuration scenarios, as well as performance data collection. All monitoring workflows have predefined thresholds and associated knowledge base articles.

In addition to the health monitoring capabilities, these management pack includes dashboards, diagram, state, performance and alert views.

 

 

Feature Summary for SQL Server 20xx Analysis Services

The following list gives an overview of features introduced by System Center Operations Manager Management Pack for SQL Server 20xx Analysis Services. Please refer to the SQL Server 20xx Analysis Services Management Pack Guide for more details.

SSAS Instance level monitoring

• Service state
• Memory Configuration Conflict with SQL Server
• TotalMemoryLimit Configuration
• Memory Usage
• Memory Usage on the Server
• Processing Pool Job Queue length
• Query Pool Queue length
• Default Storage Free Space
• CPU utilization

SSAS Database level monitoring

• Database Free Space
• Blocking Duration
• Blocking Session Count

SSAS Partition level monitoring

• Partition Storage Free Space

A number of performance collection rules

New features

New version of Generic Presentation Management Pack has been introduced

 

Summary dashboard for SQL Server Analysis Services 20xx Instances with drill-down to SQL

 

Services Analysis Services 20xx Database Dashboard

 

Summary dashboard for SQL Server Analysis Services 20xx Databases

 

Cross-version views

 

CPU utilization monitoring scenario on SSAS Instance level

New performance collection rules are introduced:

• SSAS 20xx: Database Disk Free Space (GB)
• SSAS 20xx: Database Drive Space Used By Others (GB)
• SSAS 20xx: Cache Evictions/sec
• SSAS 20xx: Cache Inserts/sec
• SSAS 20xx: Cache KB added/sec
• SSAS 20xx: CPU utilization (%)
• SSAS 20xx: Processing Rows read/sec
• SSAS 20xx: Storage Engine Query Rows sent/sec

Improvements:

• Icons for various classes are updated
• Performance counters object names are updated

Fixes:

• An issue related to the discovery of a non-cluster instance on a cluster virtual node is fixed
• Some other minor fixes

Other changes:

• Old dashboards are deprecated and removed

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012][OpsMgr 2012 R2] System Center Management Pack for SQL Server 2014

The System Center Management Pack for SQL Server 2014 enables the discovery and monitoring of SQL Server 2014 Database Engines, Databases, SQL Server Agents and other related components.

Direct link to Microsoft Article

 

The System Center Management Pack for SQL Server 2014 enables the discovery and monitoring of SQL Server 2014 Database Engines, Databases, SQL Server Agents and other related components. This Management Pack is designed to run by Operations Manager 2007 R2 (except dashboards), Operations Manager 2012 or Operations Manager 2012 R2.

The monitoring provided by this management pack includes performance, availability, and configuration monitoring, as well as performance and events data collection. All monitoring workflows have predefined thresholds and complimentary knowledge base articles. You can integrate the monitoring of SQL Server 2014 components into your service-oriented monitoring scenarios.
In addition to health monitoring capabilities, this management pack includes dashboards, diagram views, state views, performance views, alert views and diagnostic tasks that enable near real-time diagnostics and remediation of detected issues.

Note: This management pack does not depend on SQL Server Library management pack. You do not need to import it to enable the monitoring of SQL Server 2014.

Note: This management pack is only for SQL Server 2014. Please use this link to download the Operations Manager Management Pack for SQL Server 2005/2008/2012.

Supported Operating System

• Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2
• Windows Server 2008 R2 SP1
• Windows Server 2012
• Windows Server 2012 R2

Other Software:

• System Center Operations Manager 2007 R2, System Center Operations Manager 2012 or System Center Operations Manager 2012 R2
• SQL Server 2014

Note: This MP is designed to monitor the SQL Server 2014 in the following configurations:

• 64-bit SQL Server on 64-bit operating system.
• Clustered configurations are supported.

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] System Center Management Pack for SQL Server

The Management Pack for SQL Server provides the capabilities for Operations Manager 2007 R2 and Operations Manager 2012 to discover SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

 

Direct link to Microsoft page

The Management pack for SQL Server provides the capabilities for Operations Manager 2007 R2 and Operations Manager 2012 to discover SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

The monitoring provided by this management pack includes performance, availability, and configuration monitoring, performance data collection, and default thresholds. You can integrate the monitoring of SQL Server components into your service-oriented monitoring scenarios.
In addition to health monitoring capabilities, this management pack includes dashboard views, diagram views and extensive knowledge with embedded inline tasks, and views that enable near real-time diagnosis and resolution of detected issues.

Important Prerequisite Notes:
Clusters: In order to ensure that all monitoring works correctly for clustered instances of SQL Server ensure that your OpsMgr agents on the physical nodes of the cluster are running either OpsMgr 2007 R2 or OpsMgr 2007 SP1 with the most recent cumulative update for OpsMgr 2007 SP1 applied or OpsMgr 2012.

Supported Operating System

Windows Server 2003, Windows Server 2008 R2 SP1, Windows Server 2008 Service Pack 2, Windows Server 2012

System Requirements:

Other Software:

• System Center Operations Manager 2007 R2 and System Center Operations Manager 2012
• SQL Server 2005, 2008, 2008 R2 or 2012

Clusters: In order to ensure that all monitoring works correctly for clustered instances of SQL Server ensure that your OpsMgr agents on the physical nodes of the cluster are running either OpsMgr 2007 R2 with the most recent cumulative update for OpsMgr 2007 SP1 applied.

This MP is designed to monitor the SQL Server 2005, 2008, 2008 R2, and 2012 in the following configuration:

• 32-bit SQL Server on 32-bit operating system
• 64-bit SQL Server on 64-bit operating system. For SQL Server 2005, only SP2 or later is supported
• 32-bit SQL Server on 64-bit operating system

 

The SQL Server management packs also provide monitoring for clustered installations of all SQL roles that can be installed on a cluster. The following limitations apply to support for monitoring clustered installations:

• Installations of SQL Server on cluster quorum drives are not supported, and this configuration is not suggested, as documented in Knowledge Base article 280345.
• Support for SQL Server cluster resource groups, which contain more than one network name resource requires additional configuration and the agents must be running Operations Manager 2007 R2 or Operations Manager 2007 SP1 with the most recent cumulative update applied. Refer to Enabling multiple entries for network name resources in for instructions on the additional configuration.
• Low-privilege discovery and monitoring is not supported on clustered configurations in this release, except SQL 2012 Cluster.

This posting is provided "AS IS" with no warranties.

[OpsMgr 2012] How to find a specific rule/monitor/discovery in the console - SQL queries / Powershell command ( #SQL #Powershell #OpsMgr #OpsMgr2012 )

Sometimes in the Operations Manager Event log, we have some event/alert  for script error or WMI query error and all the time, the description give your the exact name of the rule/monitor/discovery that cause the issue. In SCOM console, you cannot find the rule/monitor/discovery by their exact name, only by the display name.

For example, here is an event 5500 we have on one server due to an invalid configuration for a monitor: Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold

Log Name:      Operations Manager

Source:        HealthService

Date:          11/28/2013 3:10:43 PM

Event ID:      5500

Task Category: Health Service

Level:         Information

Keywords:      Classic

User:          N/A

Computer:      MyServer.MyDom.Dom

Description:

Frequent state change requests caused the incoming state change request to be dropped due to it being older than the currently recorded state change for this monitor. This could also be due to an invalid configuration for this monitor. 

Affected monitor: Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold

Instance: MyServer.MyDom.Dom

Instance ID: 293C0099-290F-C53C-340E-A5E710CA5B9F

Management Group: MyMgtGroup

Request generated time: 2013-11-28T15:10:43.1658766+01:00

Requested state: Success

Recorded time: 2013-11-28T15:20:35.7827611+01:00

Recorded state Success

To start investigation, you need to retrieve the display name of the rule/monitor/discovery. You can also execute SQL queries on views in the OpsMgrDB or PowerShell commands connected to your management group :

Rules:

SQL:

select DisplayName from ruleview where name = 'Rule.Name'

Command Shell:

(Get-SCOMRule | Where {$_.name -match 'Rule.Name'}).DisplayName

Monitors:

SQL:

select DisplayName from monitorview where Name = 'Monitor.Name'

Command Shell:

(Get-SCOMMonitor| Where {$_.name -match 'Monitor.Name'}).DisplayName

Discoveries:

SQL:

select DisplayName from DiscoveryView where name = 'Discovery.Name'

Command Shell:

(Get-SCOMDiscovery| Where {$_.name -match 'Discovery.Name'}).DisplayName

In our case, powershell command or SQL query give a display name (replace Monitor.Name by Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold in the line) : Health Service Private Bytes Threshold for monitor Microsoft.SystemCenter.Agent.HealthService.PrivateBytesThreshold

We can now retrieve the monitor in the SCOM console and try to investigate the issue  ! :)

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] System Center Management Pack for SQL Server - Updated release 6.4.0.0 ( #SQL #SCOM #OpsMgr )

The Management Pack for SQL Server provides the capabilities for Operations Manager 2007 R2 and Operations Manager 2012 to discover SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

Direct link to Microsoft site

Version:	Date Published:
6.4.0.0	9/10/2013
File name:	File size:
SQLServerMP.msi	2.5 MB
SQLServerMPGuide.doc	2.9 MB

The Management pack for SQL Server provides the capabilities for Operations Manager 2007 R2 and Operations Manager 2012 to discover SQL Server 2005, 2008, 2008 R2, and SQL Server 2012. It monitors SQL Server components such as database engine instances, databases, and SQL Server agents.

The monitoring provided by this management pack includes performance, availability, and configuration monitoring, performance data collection, and default thresholds. You can integrate the monitoring of SQL Server components into your service-oriented monitoring scenarios.

In addition to health monitoring capabilities, this management pack includes dashboard views, diagram views and extensive knowledge with embedded inline tasks, and views that enable near real-time diagnosis and resolution of detected issues.

Important Prerequisite Notes:
Clusters: In order to ensure that all monitoring works correctly for clustered instances of SQL Server ensure that your OpsMgr agents on the physical nodes of the cluster are running either OpsMgr 2007 R2 or OpsMgr 2007 SP1 with the most recent cumulative update for OpsMgr 2007 SP1 applied or OpsMgr 2012.

Feature Summary: 
The following list gives an overview of the features of the SQL Server management pack. Refer to the SQL Server management pack guide for more detail.

New features: 

• New Dashboard for SQL Server 2012 DB
• New Monitors and Rules – only for SQL 2008 and SQL 2012
• Collect DB Active Connections count
• Collect DB Active Requests count
• Collect DB Active Sessions count
• Collect DB Active Transactions count
• Collect DB Engine Thread count
• Thread Count monitor
• Transaction Log Free Space (%) monitor
• Transaction Log Free Space (%) collection
• Collect DB Engine CPU Utilization (%)
• CPU Utilization (%) monitor for DB engine
• Buffer Cache Hit Ratio monitor
• Collect DB Engine Page Life Expectancy (s)
• Page Life Expectancy monitor
• Collect DB Disk Read Latency (ms)
• Collect DB Disk Write Latency (ms)
• Disk Read Latency monitor
• Disk Write Latency monitor
• Collect DB Transactions per second count
• Collect DB Engine Average Wait Time (ms)
• Average Wait Time monitor
• Collect DB Engine Stolen Server Memory (MB)
• Stolen Server Memory monitor
• Collect DB Allocated Free Space (MB)
• Collect DB Used Space (MB)
• Collect DB Disk Free Space (MB)
• SQL Re-Compilation monitor
• Run As configuration changes to support Low privilege for SQL Server 2012 Cluster

Additional features:

• AlwaysOn Monitoring
• Automatically discover and monitor availability groups, availability replicas, and availability databases for hundreds of computers.
• Health roll-up from availability database to availability replicas.
• Detailed knowledge with every critical health state to enable faster resolution to a problem.
• Seamless integration with Policy based management (PBM)
• Auto-discover custom PBM polices targeting AlwaysOn and database components.
• Rollup of health of policy execution within the SQL monitoring pack under extended health.
• Support for Mirroring and Replication Monitoring (only applicable to SQL Server 2008 and 2008 R2 version of management pack)
• Discover mirroring databases, witness, and mirroring group.
• Monitor database mirror state, database mirror witness state, and mirroring partners’ state.
• Custom diagram view to visually represent the primary and the mirrored databases.
• Approximately twenty rules to detect replication events.
• Improved Freespace monitoring with mount point support
• Support for Enterprise, Standard and Express editions of SQL Server 2005, 2008, 2008 R2, and 2012 and 32bit, 64bit and ia64 architectures.
• Support for both simple and complex SQL Server configurations such as clustered installations, multiple instances and 32bit roles running on a 64bit OS. For full details on supported configurations refer to the guide included with the management pack.
• Discovery and monitoring of SQL Server roles such as DB Engine, Reporting Services, Analysis Services, Integrations Services.
• Discovery of SQL Server components such as databases, the SQL Agent and SQL jobs.
• Views covering areas such as database free space, SQL Server related performance, SQL Server related alerts, and lists of the various SQL Server roles and components which are discovered and their related state.
• Discovery and basic monitoring for SQL Server Reporting Services and Integration Services.
• Reports for longer-term analysis of common problem areas related to SQL Server such as SQL Server lock analysis and top deadlocked databases, SQL Server service pack levels across discovered roles, user connection activity. Likewise the generic reports from the Microsoft Generic Report Library can be used against the roles and components discovered by the SQL MPs to review availability and performance across many systems and over longer periods of time.
• Role and component specific tasks which provide access to common tools, triage information, or corrective actions without needing to leave the Operations Console in most cases.
• Monitoring of databases covers database status, database free space, log shipping monitoring for both the source and destination, and transaction log free space.
• Monitoring of key SQL Server related services.
• Monitoring for persistent SPID blocking.
• Monitoring of numerous SQL Server events and performance data points. Alerts bring the issue to your attention and provide knowledge on the impact and possible resolutions.
• A low-privilege configuration for discovery and monitoring that eliminates the need for SQL Server sysadmin, dbo, db_owner, and box admin privileges
Release History 

• 9/3/2013 - Updated release, version 6.4.0.0
• 8/21/2012 - Updated release, version 6.3.173.1
• 4/2/2012 - Updated release, version 6.3.173.0
• 5/6/2011 - Updated to version 6.1.400.0. Minor bug fixes. Fixed sql injection security issues
• 8/17/2010 - Added SQL 2000 management pack as an independent mp, see below downloadable SQL Server 2000 Management Pack.msi for SQL 2000 Server monitoring.
• 8/5/2010 - Updated to version 6.1.314.36. Removed DMO dependency, improved health model, improved free space monitoring to support file groups and autogrow, added knowledge and added detailed low-privilege configuration information in the MP guide. Refer to the management pack guide for a more complete list of additions and improvements in this release.
• 11/24/2009 - MP guide update only, same version 6.0.6648.0
• 7/22/2009 - Updated version 6.0.6648.0, which fixes an issue with some monitoring not working on clustered instances of SQL Server. Refer to the MP guide for more details.
• 3/31/2009 - Updated version 6.0.6569.0, which provided a number of fixes. Refer to the MP guide for more details.
• 12/3/2008 - Updated version 6.0.6460.0, fixed an issue with the database discovery script to prevent overflows
• 10/29/2008 - Updated version 6.0.6441.0, which introduced SQL Server 2008 support in addition to other new features and fixes. Refer to the MP guide for a complete list of changes.
• 3/31/2008 - Updated version 6.0.6278.8
• 12/21/2007 - updated version 6.0.6247.5
• 3/23/2007 - Initial Release, version 6.0.5000.0

This posting is provided "AS IS" with no warranties.

[Orchestrator] Orchestrator does not reconnect when the SQL server is restarted

Runbooks stop executing and the Orchestrator Runbook Service and Management Server do not function as expected when the SQL Server instance that hosts the Orchestrator database is restarted. Errors are expected to occur during the downtime of the SQL Server, however proper connectivity and behavior does not restore once the SQL Server instance has restarted.

KB Article

When the problem is occurring, the following events are logged to Orchestrator's Event window:

The Orchestrator Runbook Service on <server name> is no longer running. Verify the status of the Runbook Server and try to restart Orchestrator Runbook Service. If the problem persists, contact Technical Support.

and

The Runbook Server is suspended because the product license expired.

Cause

The Orchestrator services must be restarted in order for normal behavior to be restored. Microsoft has determined this is an issue in System Center 2012 Orchestrator.

 

Resolution

A fix is scheduled to be available in an upcoming update for System Center. Please contact Microsoft Technical Support for more details. 

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use (http://go.microsoft.com/fwlink/?LinkId=151500) for other considerations.

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] Report to list all occurences of a specific alert in a given interval

The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, Jean-Sébastien DUCHÊNE offers a report to list all occurrences of a specific alert in a given interval.

 

Beware: the proposed data depend on the retention time of the Data WareHouse.

Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager and SQL Server 2008 RTM/R2.

Download Alert - List all occurrences of an alert Report

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] 2 reports to work on references between MPs

Here is a new link to an article from Jean-Sebastien DUCHENE who provide 2 new reports to work with references between management packs. Please find more report on his blog >>> Here <<<

The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report to track references between Unsealed MP and Sealed MP.

Here are the reports:

• Management Pack - References by Override MP
• Management Pack - References by Sealed MP

Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager and SQL Server 2008 RTM/R2.

Here is the query:

SELECT distinct Tgt.ManagementPackSystemName As 'Sealed MP', Src.ManagementPackSystemName As 'Override MP'
FROM vManagementPackVersionReference
INNER JOIN vManagementPack As Tgt on ReferencedManagementPackRowId = Tgt.ManagementPackRowId
INNER join vManagementPackVersion on ReferencingManagementPackVersionRowId = vManagementPackVersion.ManagementPackVersionRowId
INNER JOIN vManagementPack As Src on vManagementPackVersion.ManagementPackRowId = Src.ManagementPackRowId
WHERE Src.SealedInd = 0
AND Tgt.ManagementPackSystemName NOT IN
('Microsoft.SystemCenter.Library',
'Microsoft.SystemCenter.InstanceGroup.Library',
'Microsoft.Windows.Library',
'System.Library',
'System.Health.Library')
ORDER BY Tgt.ManagementPackSystemName

Download Management packs references Reports

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] Alerts associated to one MP Report

Source : Jean-Sébastien DUCHÊNE - in this article >>> HERE <<<


The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report to list all alerts raised by a Management Pack in a given interval.
Beware: the proposed data depend on the retention time of the Data WareHouse.
Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager  and SQL Server 2008 RTM/R2.


Here is the query:

SELECT     Distinct Alert.vAlert.AlertName AS 'Alert Name', Alert.vAlert.AlertDescription AS [Alert Description], Alert.vAlertDetail.TicketId AS 'Ticket ID',
                      CASE Alert.vAlert.Severity WHEN '0' THEN 'Information' WHEN '1' THEN 'Warning' WHEN '2' THEN 'Critical' END AS 'Severity',
                      CASE Alert.vAlert.Priority WHEN '0' THEN 'Low' WHEN '1' THEN 'Medium' WHEN '2' THEN 'High' END AS 'Priority', Alert.vAlert.RepeatCount AS 'Repeat Count',
                      Alert.vAlert.RaisedDateTime AS [Raised Date], COUNT(vAlert.AlertName)
FROM         Alert.vAlertDetail WITH (NOLOCK) INNER JOIN
                      Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
                      vRule WITH(NOLOCK) ON (vRule.RuleRowId = vAlert.WorkflowRowId AND vAlert.MonitorAlertInd = 0) INNER JOIN
                      vManagementPack WITH (NOLOCK) ON (vRule.ManagementPackRowId = vManagementPack.ManagementPackRowId)
WHERE     (vManagementPack.ManagementPackRowId = @MPiD)
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, Alert.vAlertDetail.TicketId, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime, Alert.vAlert.RepeatCount
UNION
 SELECT     Distinct Alert.vAlert.AlertName AS 'Alert Name', Alert.vAlert.AlertDescription AS [Alert Description], Alert.vAlertDetail.TicketId AS 'Ticket ID',
                      CASE Alert.vAlert.Severity WHEN '0' THEN 'Information' WHEN '1' THEN 'Warning' WHEN '2' THEN 'Critical' END AS 'Severity',
                      CASE Alert.vAlert.Priority WHEN '0' THEN 'Low' WHEN '1' THEN 'Medium' WHEN '2' THEN 'High' END AS 'Priority', Alert.vAlert.RepeatCount AS 'Repeat Count',
                      Alert.vAlert.RaisedDateTime AS [Raised Date], COUNT(vAlert.AlertName)
FROM         Alert.vAlertDetail WITH (NOLOCK) INNER JOIN
                      Alert.vAlert WITH (NOLOCK) ON Alert.vAlertDetail.AlertGuid = Alert.vAlert.AlertGuid INNER JOIN
                      vMonitor WITH(NOLOCK) ON (vMonitor.MonitorRowId = vAlert.WorkflowRowId AND vAlert.MonitorAlertInd = 1) INNER JOIN
                      vManagementPack WITH (NOLOCK) ON (vMonitor.ManagementPackRowId = vManagementPack.ManagementPackRowId)
WHERE     (vManagementPack.ManagementPackRowId = @MPiD)
AND (DATEADD(hh,DATEDIFF(hh,getutcdate(),getdate()),vAlert.RaisedDateTime)) between @startdate and @enddate
GROUP BY Alert.vAlert.AlertName, Alert.vAlert.AlertDescription, Alert.vAlertDetail.TicketId, Alert.vAlert.Severity, Alert.vAlert.Priority, Alert.vAlert.RaisedDateTime, Alert.vAlert.RepeatCount
ORDER BY  Alert.vAlert.RaisedDateTime DESC

Download Management Pack - Alerts Raised Report

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] Hourly aggregated report on Performance Data for an agent

Source : Jean-Sébastien DUCHÊNE - in this article >>> HERE <<<

The reports provided with System Center Operations Manager (SCOM) may sometimes seem particularly poor in terms of daily operations on the product. Today, I offer a report to select a performance rule of an agent to display a chart on a given interval. It will use hourly aggregated data.
Beware: the proposed data depend on the retention time of the Data WareHouse.
Do not forget to create a datasource targeting to the data warehouse with security rights necessary to generate the report. This reports works with System Center Operations Manager 2007 R2, System Center 2012 Operations Manager  and SQL Server 2008 RTM/R2.

Here is the query:

SELECT Distinct vPerformanceRuleInstance.InstanceName, vPerformanceRuleInstance.PerformanceRuleInstanceRowId FROM vRule
INNER JOIN vPerformanceRule WITH(NOLOCK) ON vRule.RuleRowId = vPerformanceRule.RuleRowId
INNER JOIN vPerformanceRuleInstance WITH(NOLOCK) ON vPerformanceRule.RuleRowId = vPerformanceRuleInstance.RuleRowId
INNER JOIN Perf.vPerfDaily WITH(NOLOCK) ON  vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId
INNER JOIN vManagedEntity WITH(NOLOCK) ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId
WHERE  Path like '%' + @ManagedEntityId + '%' And vRule.RuleDefaultName = @RuleDefaultName
ORDER BY vPerformanceRuleInstance.InstanceName ASC 

 

Download Agent - Performance Hourly Aggregation Report

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] Correspondence between categories (PerformanceCollection, Alert, Custom...) and IDs

Jean-Sebastien DUCHENE has made a correspondence table that could be useful to create reports where you may need to make the match between IDs and categories of rules, monitors, or discoveries.
Table in Operations Manager DB or in the DW sometimes contains IDs and sometimes contains the Category (string). When category is given by it's ID, you have to convert it in a string.

If you have a doubt on what I'm speaking about - here is a screen shot in the authoring console :

These parameters in SCOM DBs are sometimes hard coded with an ID, and sometimes "in clear" in a string.

No-one before has done this correspondence - thanks to him to provide this table !

ID	Category
0	PerformanceCollection
1	Operations
2	EventCollection
3	StateCollection
4	SoftwareAndUpdates
5	Alert
6	System
7	Custom
8	AvailabilityHealth
9	PerformanceHealth
10	ConfigurationHealth
11	SecurityHealth
12	Discovery
13	Notification
14	DSIntegration
15	ConnectorFramework
16	ExceptionMonitoring
17	Maintenance

This posting is provided "AS IS" with no warranties.

[OpsMgr 2007 R2][OpsMgr 2012] Usefull Query / report to list all powershell sripts in your MPs

If you are working on an OpsMgr 2012 migration, this posting should be very interesting.
Once again Jean-Sebastien DUCHENE as worked on a very usefull report / SQL Query to help us to manage our migration. This time, we are able to list all discoveries, monitors, rules, tasks and recovery tasks that contain PowerShell scripts in your OpsMgr 2007 R2 environment. You'll also be able to retrieve MPs to modify to use the news SCOM 2012 cmdlet.

Here is the query provided by Jean-Sebastien DUCHENE to be executed on your Operations Manager DB :

SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Monitor' As 'Type'
   ,MonitorView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Category
      ,Priority
      ,'Null' As 'Module Name'
   ,SUBSTRING(SubString(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)), CharIndex('<ScriptName>',SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))),LEN(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)))), CharIndex('>',SubString(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)), CharIndex('<ScriptName>',SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))),LEN(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))))) + 1,LEN(SubString(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml)), CharIndex('<ScriptName>',SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))),LEN(SubString(ConfigurationXml,0,CharIndex('</ScriptName>',ConfigurationXml))))))  As 'Script Name'
   ,ConfigurationXml As 'Module Configuration'
      ,MonitorView.LastModified
 FROM MonitorView
  INNER JOIN ManagementPackView WITH(NOLOCK) ON MonitorView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON MonitorView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE  MonitorView.ConfigurationXml like '%.PS1%'
UNION
SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Rule' As 'Type'
   ,RuleView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case RuleView.Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Category
      ,Priority
      ,RuleModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)), CharIndex('<ScriptName>',SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)))), CharIndex('>',SubString(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)), CharIndex('<ScriptName>',SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))))) + 1,LEN(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)), CharIndex('<ScriptName>',SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</ScriptName>',RuleModuleConfiguration)))))) + SUBSTRING(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)), CharIndex('<CommandLine>',SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)))), CharIndex('>',SubString(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)), CharIndex('<CommandLine>',SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))))) + 1,LEN(SubString(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)), CharIndex('<CommandLine>',SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration))),LEN(SubString(RuleModuleConfiguration,0,CharIndex('</CommandLine>',RuleModuleConfiguration)))))) As 'Script Name'
   ,RuleModuleConfiguration As 'Module Configuration'
      ,RuleView.LastModified
  FROM RuleModule
  INNER JOIN RuleView WITH(NOLOCK) ON RuleModule.RuleId = RuleView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON RuleView.TargetMonitoringClassId = ManagedTypeView.Id
  INNER JOIN ManagementPackView WITH(NOLOCK) ON RuleView.ManagementPackId = ManagementPackView.Id
  WHERE RuleModuleConfiguration like '%.PS1%'
  UNION
  SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Discovery' As 'Type'
   ,DiscoveryView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Case Category When '12' Then 'Discovery' Else 'Unknown' END 'Category'
      ,Priority
      ,Module.ModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))))) + SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))))) As 'Script Name'
      ,Module.ModuleConfiguration As 'Module Configuration'
      ,DiscoveryView.LastModified
  FROM DiscoveryView
  INNER JOIN Module WITH(NOLOCK) ON DiscoveryView.Id = Module.ParentId
  INNER JOIN ManagementPackView WITH(NOLOCK) ON DiscoveryView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON DiscoveryView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE Module.ModuleConfiguration like '%.PS1%'
  UNION
  SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Recovery Task' As 'Type'
   ,RecoveryView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Case Category When '7' Then 'Custom' Else 'Unknown' END AS 'Category'
      ,'0' As 'Priority'
      ,Module.ModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))))  As 'Script Name'
      ,Module.ModuleConfiguration As 'Module Configuration'
      ,RecoveryView.LastModified
  FROM RecoveryView
  INNER JOIN Module WITH(NOLOCK) ON RecoveryView.Id = Module.ParentId
  INNER JOIN ManagementPackView WITH(NOLOCK) ON RecoveryView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON RecoveryView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE Module.ModuleConfiguration like '%.PS1%'
  UNION
  SELECT ManagementPackView.FriendlyName As 'Management Pack'
   ,'Task' As 'Type'
   ,TaskView.Name As 'Name'
      ,ManagedTypeView.Name As 'Class Targeted'
      ,Case Enabled When '4' Then 'Enabled'
      When '0' Then 'Disabled' END As 'Enabled'
      ,Case Category When '7' Then 'Custom' Else 'Unknown' END AS 'Category'
      ,'0' As 'Priority'
      ,Module.ModuleName As 'Module Name'
   ,SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)), CharIndex('<ScriptName>',SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</ScriptName>',Module.ModuleConfiguration)))))) + SUBSTRING(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))), CharIndex('>',SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))))) + 1,LEN(SubString(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)), CharIndex('<CommandLine>',SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration))),LEN(SubString(Module.ModuleConfiguration,0,CharIndex('</CommandLine>',Module.ModuleConfiguration)))))) As 'Script Name'
      ,Module.ModuleConfiguration As 'Module Configuration'
      ,TaskView.LastModified
  FROM TaskView
  INNER JOIN Module WITH(NOLOCK) ON TaskView.Id = Module.ParentId
  INNER JOIN ManagementPackView WITH(NOLOCK) ON TaskView.ManagementPackId = ManagementPackView.Id
  INNER JOIN ManagedTypeView WITH(NOLOCK) ON TaskView.TargetMonitoringClassId = ManagedTypeView.Id
  WHERE Module.ModuleConfiguration like '%.PS1%'
  ORDER BY ManagementPackView.FriendlyName   



Download the Management Pack that contains the report - PowerShell Scripts included in Management Packs

This posting is provided "AS IS" with no warranties.