Microsoft has released a new version of several SQL Management Packs, they are now on version 6.5.2.0 and are available for download at the links below:
SQL Server Reporting Services
SQL Server 2008 Reporting Services – Available here
SQL Server 2012 Reporting Services – Available here
SQL Server 2014 Reporting Services – Available here
SQL Server Analysis Services
SQL Server 2012 Analysis Services – Available here
SQL Server 2014 Analysis Services – Available here
This version of the SQL Server Analysis Services management packs contain several new features:
New features
New version of Generic Presentation Management Pack has been introduced
Summary dashboard for SQL Server Analysis Services 2012 Instances with drill-down to SQL Services Analysis Services 2012 Database dashboard
Summary dashboard for SQL Server Analysis Services 2012 Databases
Cross-version views
CPU utilization monitoring scenario on SSAS Instance level
New performance collection rules are introduced:
SSAS 2012: Database Disk Free Space (GB)
SSAS 2012: Database Drive Space Used By Others (GB)
SSAS 2012: Cache Evictions/sec
SSAS 2012: Cache Inserts/sec
SSAS 2012: Cache KB added/sec
SSAS 2012: CPU utilization (%)
SSAS 2012: Processing Rows read/sec
SSAS 2012: 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
Microsoft recently released KB3002249 which details an issue whereby when performance collection rules are disabled in SCOM, performance views still show counters even after all the data is groomed out.
This effects all versions of SCOM and can make the PerformanceDataAllView difficult to read due to clutter.
“This issue is by design. The Operations Manager grooming processes does not groom the PerformanceSource table.”
The below query can be used to see which performance counters will be deleted for what objects before you run the delete script:
Use OperationsManager select PS.PerformanceSourceInternalId, BME.BaseManagedEntityId, BME.DisplayName, PC.CounterName, PC.ObjectName, PS.TimeAdded, PS.LastModified, PDA.PerformanceSourceInternalId from PerformanceSource PS left join PerformanceDataAllView PDA on PDA.PerformanceSourceInternalID = PS.PerformanceSourceInternalId join PerformanceCounter PC on PC.PerformanceCounterId = PS.PerformanceCounterId join BaseManagedEntity BME on BME.BaseManagedEntityId = PS.BaseManagedEntityId where PDA.PerformanceSourceInternalId IS NULL
The following is a small SQL script that will remove the entries from PerformanceDataAllView for which no data is recorded.
Note Stop all the Operations Manager services on all Management Servers before you run the script. Always back up your OperationsManager Database before you run this script.
Use OperationsManager
delete from PerformanceSource where PerformanceSourceInternalId in
(
select PS.PerformanceSourceInternalId from PerformanceSource PS
left join PerformanceDataAllView PDA on PDA.PerformanceSourceInternalID = PS.PerformanceSourceInternalId
where PDA.PerformanceSourceInternalId IS NULL
)
With the release of SCOM 2012 R2 Microsoft has official amended the supported SQL collation settings. In the past you had to use SQL_Latin1_General_CP1_CI_AS, this was due to the data warehouse being installed with this setting by default regardless of the SQL server collation setting. This was a nice addition which has been due for a long time, having collation mismatches between your OpsDB and DW can cause a whole host of issues which are well documented.
Of course the new settings only apply to fresh installations, when upgrading the previous collation settings will still be used.
Click here for the complete supported configuration.
Sometimes you might have a situation where all of your agents are showing as healthy in the console but when you try and draw a performance report data is missing.
The below SQL query which has been developed by my colleague Gerrie Louw will identify any agent that has not submitted performance data in the past 4 hours. It does so by checking the following performance counters:
Processor > % Processor Time
LogicalDisk > % Free Space > C:
Memory > Available MBytes
Note: You will probably have to change the DisplayName_ and IsVirtualNode for your OperationsManager database.
if object_id(‘tempdb..#temptable’) IS NOT NULL
DROP TABLE #temptable
SELECT distinct bmetarget.Name into #temptable
FROM OperationsManager.dbo.BaseManagedEntity AS BMESource WITH (nolock) INNER JOIN
OperationsManager.dbo.Relationship AS R WITH (nolock) ON
R.SourceEntityId = BMESource.BaseManagedEntityId INNER JOIN
OperationsManager.dbo.BaseManagedEntity AS BMETarget WITH (nolock) ON
R.TargetEntityId = BMETarget.BaseManagedEntityId inner join mtv_computer d on bmetarget.name=d.[DisplayName_55270A70_AC47_C853_C617_236B0CFF9B4C]
and d.IsVirtualNode_E817D034_02E8_294C_3509_01CA25481689 is null
WHERE (bmetarget.fullname like ‘Microsoft.Windows.Computer%’)
if object_id(‘tempdb..#healthstate’) IS NOT NULL
DROP TABLE #healthstate
select megv.path, megv.ismanaged, megv.isavailable, megv.healthstate into #healthstate
from managedentitygenericview as megv with (nolock) inner join managedtypeview as mtv with (nolock)
on megv.monitoringclassid=mtv.id
where mtv.name =’microsoft.systemcenter.agent’
if object_id(‘tempdb..#perfcpudata’) IS NOT NULL
DROP TABLE #perfcpudata
select Path, ‘CPU’ as ‘Cat’ into #perfcpudata
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and objectname =’Processor’ and countername=’% Processor Time’
if object_id(‘tempdb..#perfmemdata’) IS NOT NULL
DROP TABLE #perfmemdata
select Path,’Memory’ as ‘Cat’ into #perfmemdata
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and objectname =’Memory’ and countername=’Available MBytes’
if object_id(‘tempdb..#perfdiskdata’) IS NOT NULL
DROP TABLE #perfdiskdata
select Path,’Disk’ as ‘Cat’ into #perfdiskdata
from PerformanceDataAllView pdv with (NOLOCK)
inner join PerformanceCounterView pcv on pdv.performancesourceinternalid = pcv.performancesourceinternalid
inner join BaseManagedEntity bme on pcv.ManagedEntityId = bme.BaseManagedEntityId
where (TimeSampled < GETUTCDATE() AND TimeSampled > DATEADD(MINUTE,-240, GETUTCDATE()))
and objectname =’LogicalDisk’ and countername=’% Free Space’ and instancename=’C:’
if object_id(‘tempdb..#temptable1′) IS NOT NULL
DROP TABLE #temptable1
create table #temptable1 (
name nvarchar(250),
cat nvarchar(20),
val nvarchar(2)
)
insert into #temptable1
select name, ‘CPU’ as ‘cat’, ’1′ as ‘val’
from #temptable where name not in
(select path from #perfcpudata)
insert into #temptable1
select name, ‘Memory’ as ‘cat’, ’1′ as ‘val’
from #temptable where name not in
(select path from #perfmemdata)
insert into #temptable1
select name, ‘Disk’ as ‘cat’, ’1′ as ‘val’
from #temptable where name not in
(select path from #perfdiskdata)
if object_id(‘tempdb..#output’) IS NOT NULL
DROP TABLE #output
create table #output (
name nvarchar(250),
cpu nvarchar(2),
memory nvarchar(2),
disk nvarchar(2)
)
insert into #output
select distinct tt.name ,’0′,’0′,’0′
from #temptable1 as tt, #healthstate as hs
where tt.name=hs.path collate SQL_Latin1_General_CP1_CI_AS
and hs.isavailable=1
and hs.ismanaged=1
and hs.healthstate is not null
update #output set cpu=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat=’CPU’)
update #output set memory=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat=’Memory’)
update #output set disk=1 where #output.name in (select name from #temptable1 where #temptable1.name=#output.name and #temptable1.cat=’Disk’)
select * from #output
You can use this query to build a report such as the one sampled below:
An oldie but a goodie, the below SQL script will help with investigating if a server was placed into maintenance mode and if so by whom. Just change %computer:% to the name of the specific device you are investigating and the StartDateTime as required.
USE OperationsManagerDW go SELECT DISTINCT MngE.DisplayName AS ServerName, MM.StartDateTime, MM.EndDateTime, MM.PlannedMaintenanceInd AS [PlannedMaintenance_1Yes-0No], MM.DWLastModifiedDateTime, MMH.Comment, MMH.UserId FROM OperationsManagerDW.dbo.vMaintenanceMode AS MM INNER JOIN OperationsManagerDW.dbo.vMaintenanceModeHistory AS MMH ON MMH.MaintenanceModeRowId = MM.MaintenanceModeRowId INNER JOIN OperationsManagerDW.dbo.vManagedEntity AS MngE ON MngE.ManagedEntityRowId = MM.ManagedEntityRowId WHERE MngE.FullName LIKE ‘%computer:%’ AND (MM.StartDateTime > ‘2014-03-01’) ORDER BY MM.DWLastModifiedDateTime
Brian McDermott highlighted an issue to watch out for when upgrading to SCOM 2012 R2 where you may get Data Warehouse synchronization failure errors after the upgrade.
The article can be found here with solid reasoning as to the cause and solution:
Please note that the Event ID 31565 noted above is a very generic error and you should only run the SQL below if the description identifies that it is the problem with the TFSWorkItemID column.
Error below:
Log Name: Operations Manager
Source: Health Service Modules
Date:
Event ID: 31565
Task Category: Data Warehouse
Level: Error
Keywords: Classic
User: N/A
Computer: OMMS.domain.com
Description:
Failed to deploy Data Warehouse component. The operation will be retried.
Exception ‘DeploymentException’: Failed to perform Data Warehouse component deployment operation: Install; Component: DataSet, Id: ‘0d698dff-9b7e-24d1-8a74-4657b86a59f8′, Management Pack Version-dependent Id: ’29a3dd22-8645-bae5-e255-9b56bf0b12a8′; Target: DataSet, Id: ’23ee52b1-51fb-469b-ab18-e6b4be37ab35’. Batch ordinal: 3; Exception: Sql execution failed. Error 207, Level 16, State 1, Procedure vAlertDetail, Line 18, Message: Invalid column name ‘TfsWorkItemId’.
This issue can be fixed with the below SQL query, as always BACKUP your databases and proceed at your own risk:
USE OperationsManagerDW
DECLARE @GuidString NVARCHAR(50)
SELECT @GuidString = DatasetId FROM StandardDataset
WHERE SchemaName = ‘Alert’
— update all tables that were already created
DECLARE
@StandardDatasetTableMapRowId int
,@Statement nvarchar(max)
,@SchemaName sysname
,@TableNameSuffix sysname
,@BaseTableName sysname
,@FullTableName sysname
SET @StandardDatasetTableMapRowId = 0
WHILE EXISTS (SELECT *
FROM StandardDatasetTableMap tm
WHERE (tm.StandardDatasetTableMapRowId > @StandardDatasetTableMapRowId)
Now here’s something you certainly don;t see every day. It all started when I was asked to investigate a flood of memory alerts for a particular server at one of my customers. When I opened Health Explorer I noticed the following:
The server was running the monitors for Windows 2008 and Windows 2003.
As it turns out the server had been recently re-installed from 2003 to 2008 with the same name, without the agent being uninstalled or being removed from the console. This caused a bit of confusion in the back-end. A quick look at the Windows Server 2003 Operating System Inventory showed another server which was “Upgraded” i in the same fashion:
What’s happened here is the class for Windows Server 2003 Operating System is still being loaded by the agent, and this is causing all of the related rules and monitors to load as well. In the past when I’ve come across this particular issue I’ve been able to solve it with the remove-disabledmonitoringobject powershell cmdlet.
All that you need to do is override the discovery rule in question to false for your object (In this case “Discover Windows Server 2003 Operating System) and then open OpsMgr Shell and run remove-disabledmonitoringobject. After a short delay the offending objects are removed.
However in this case the above did not work, eventually I deleted the agent from the console, waited for grooming to run (you can force it if you are in a hurry), cleared the local agent cache and then approved the agent. Now only the correct objects are being discovered.