SCOM: Small edit for SCC Logical Disk Extension Management Pack

This blog post is about the SCC Logical Disk Extension Management Pack.

This MP is a real nice example of a combined forces from the System Center community. It contains some extra Logical Disk collection rules and two Logical Disk reports.

Using the MP is pretty simple, but i noticed two small things.
The parameter list to select a Computer Group is not sorted. Finding the right group between more that 300 groups can be a hassle. And after generating a report the Computer Group name is shown. But as many groups are created through the Operations Console, a Display Name would be nicer.

To change this I modified the RDL file directly and notified the creators of this MP. Remember that this is not the standard method for editing SCOM reports.

For sorting the computer groups...

I changed:

SELECT FullName, DisplayName
FROM OperationsManagerDW.dbo.vManagedEntity with (nolock) where Path is null and FullName not like '%:%' and DisplayName like '%Computer%'

To:

SELECT FullName, DisplayName
FROM OperationsManagerDW.dbo.vManagedEntity with (nolock) where Path is null and FullName not like '%:%' and DisplayName like '%Computer%'
ORDER BY DisplayName

And for the table footer...

I changed:

<value>=Parameters!Group.Value</value></textbox>

To:

<value>=Parameters!Group.Label</value></textbox>

I you want, you can download the RDL file here: https://sites.google.com/site/systemcentertechblogresources/scripts/Windows.LogicalDrives.FreeSpace.Report_MichielWouters_1.0.rdl?attredirects=0&d=1

SCOM: Deliver Reports to Sharepoint

Today I focussed on delivering SCOM reports to a Sharepoint document library with the File Share and e-mail delivery methods.

Most teams and departments have used file shares and network mappings to store their documents. These days more and more companies are using portal products for that purpose, like Microsoft Sharepoint.

In this blog I'll show you how you can deliver a report to a Sharepoint 2007 library. Although this is not difficult to setup, there are some things you need to know to get it working. In this setup SQL SRS 2005 and Sharepoint 2007 are used.

Note: SQL Reporting Services 2008 supports publishing reports directly in Sharepoint libraries, but SCOM 2007 does not support that. Atleast as far as I know. :)

Operations Manager supports two kinds of delivery, file and e-mail.
Both flavours can work, but it fully depends on how your Sharepoint implementation is setup.
There's a third but that's the Null Delivery Provider to cache reports.


E-mail delivery
For Operations Manager Reporting to deliver e-mail to a library the following has to be in place and configured.

Configure SQL Reporting Services

• Use the Reporting Services Configuration Manager
• Make sure the the Windows Service identity is configured with a domain user account and not with a local account (Data Warehouse Reader account).
• Configure the E-mail Setttings
• Make sure your SMTP server allows you to send e-mail

Configure you Sharepoint document library

• Make sure you have administrative rights on the document library
• Configure incoming e-mail settings on the library in which you want to deliver your reports
• Unfortunately you cannot change the name of the file SQL Reporting Services sends along with the e-mail message.
• As i want to archive all reports, I don't want them to be overwritten. In this case Sharepoint will create unique numbers as a postfix in the file name.
• If you don't want everyone to be able to send documents in to your library restrict access to specific users. In this case configure the SCOM Reporting Windows Service account to be allowed to send e-mail (Data Warehouse Reader account).
• Group items on the e-mail subject. This is the only configurable item when you schedule a SCOM report.

Schedule your reports

• Choose a naming convention for delivering your reports.
• Use that in naming your e-mail subjects, so they are neatly grouped in your Sharepoint library.
• Choose the right document format, e.g. PDF.

And the deliver!

Windows File Share (Sharepoint Library UNC path)

The other option is deliver the reports in to a file share. This is rather easy to setup when you're using ordinary File Shares, but as I sead earlier, more and more companies are using centralised documentation solutions like Sharepoint.
What I like about this delivery method is the possibility to give your reports a more understandable name. And more, SQL Reporting Services auto increments the new files which is more readable than the generated unique numbers when using incoming email on the Sharepoint library.

What you need:

• A domain account for authentication on the Sharepoint library. You could use "RDA" in the account name, as for Report Delivery Account.
• Configure the account to have enought privileges to add new items to that library
• Write down the Network UNC path of that library

Configure your Report Server

• On Windows Server 2003: Make sure the Web Client service is running and is started automatically. You need this to connect to a Sharepoint library with UNC.
• On Windows Server 2008: There's no Web Client in Windows Server 2008. For the UNC path to work you need to install a feature called, Desktop Experience. You can add this feature with the Server Manager > Features > Add Feature > Desktop Experience. Notice that you'll need to reboot the Reporting Server after the installation.
• Test the UNC path of the library from your Report Server

Schedule your report

• Select the File Delivery Method
• Choose a naming convention for delivering your reports.
• Use that in naming your report files (remember that file names of new items are autoincremented)
• Fill-in the UNC path of the library that you wrote down earlier
• Use the domain account with the necessary privileges (a.k.a. Report Delivery Account)

And then you can deliver your report.

SCOM: Upgrading to SCOM 2007 R2

Last week i was busy upgrading multiple SCOM 2007 SP1 environments to R2.

The upgrade process to R2 is actually very straigt forward. That is, if your environment is also straight forward.

A collegue of my was presented with an upgrade error when he tried to upgrade the Reporting Server and the Data Warehouse Database in the background.

Eventually it was caused because the user account the SCOM Reporting Server upgrade was started with, was configured as a user on the database without SQL roles.

This takes some time to figure out, especially when you have to work with different teams. A team for SCOM, a team for SQL and for example a team for IIS.

That's why here is a R2 upgrade info list for your convenience:

• What's new in SCOM R2
• R2 Webcasts
• SCOM R2 Product Documentation

But most important:

• SCOM 2007 Upgrade Guide (Notice the Improving Upgrade Performance article)
• SCOM 2007 Deployment Scenarios

And remember, always create a backup of your database before installing a upgrade.

SCOM: Reporting queries for performance counters

When you want to start making reports in your SCOM environment, you'd first have to learn how the data is stored in the SCOM DataWarehouse and which tables, views or stored procedure you can use.

I use SQL Server Business Intelligence Development Studio. You can use Report Builder, but it is less featured.
After you create a Shared DataSource and a new report, you can create your query for your Data Set.

Below is a query to get performance data from the aggregated view 'PerfDaily' for a computer from counter name 'Working Set'.

-- BEGIN QUERY
SELECT Perf.vPerfDaily.DateTime, Perf.vPerfDaily.AverageValue, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName,
vManagedEntity.FullName, vPerformanceRuleInstance.InstanceName
FROM vPerformanceRuleInstance INNER JOIN
Perf.vPerfDaily ON vPerformanceRuleInstance.PerformanceRuleInstanceRowId = Perf.vPerfDaily.PerformanceRuleInstanceRowId INNER JOIN
vManagedEntity ON Perf.vPerfDaily.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE (vPerformanceRule.CounterName = 'Working Set') AND (vManagedEntity.Path = @ComputerName)
ORDER BY Perf.vPerfDaily.DateTime

--END QUERY

@ComputerName is a parameter which can be entered by a user. Another option is to fill this value with server names based on another query. Like "give me all servers from a specific group".

Such a query could be:

-- BEGIN QUERY
SELECT dbo.vManagedEntity.Name
FROM dbo.vManagedEntity INNER JOIN
dbo.vRelationship On dbo.vManagedEntity.ManagedEntityRowId = dbo.vRelationship.TargetManagedEntityRowId INNER JOIN
dbo.vManagedEntity As CompGroup On dbo.vRelationship.SourcemanagedEntityRowId = CompGroup.ManagedEntityRowId
WHERE CompGroup.DisplayName = 'MyServers'
-- END QUERY