I saw an interesting blog post from the Operations Manager product team about Ops Mgr db and reporting running at Windows Server 2008 (Longhorn). Of course I wanted to test that and what is a better occupy on a winter day then try some new technologies.

 

I already had a Windows Server 2008 RC1 ENT X64 box running so I simple added the Hyper-V role. I installed a new virtual machines in Hyper-V, a virtual machine running 2008 RC1 ENT X64. There was no real issue during the setup of Ops Mgr. The blog post from the product team is pretty accurate about the SQL setup. But I didn’t do any changes to my applicationHost.config files as the blog post tells you to. I first did, but after a lot of trouble with SQL reporting I restored my original file and SQL reporting started to work. I install all components of Ops Mgr and then installed SP1 RC too. The only issue was the AcsConfig.xml permission issue, there is a KB about that problem here.

After I had installed Ops Mgr Reporting and also applied SP1 RC I had to re-run Reporting Services Configuration Manager. I had some problem with the encryption key and also the web service identity. My ReportServer application pool also reconfigured it self during the setup, so I had to reconfigure that application pool identity too in the IIS console. My Reporting configuration now looks like this

-Report Server Virtual Directory

• Name: ReportServer
• Website: Default Web Site

-Report Manager Virtual Directory

• Name: Reports
• Website: Default Web Site

-Windows Service Identity

• Service Name: ReportServer
• Service Account: NT Authority\LocalService
• Built-in Account: Local Service

-Web Service Identity

• ASP -NET Service Account: NT Authority\SYSTEM
• Report Server: ReportServer
• Report Manager: ReportServer

-Database Setup

• Server Name: OpsMgr
• Database Name: ReportServer
• Credentials Type: Windows Credentials
• Account Name: CORP\svc_dra (Data Warehouse Read Account)

-Execution Account

• Account: CORP\svc_dra

Later I had a problem when running reports, I recived “Value cannot be null, parameter name: GroupList”. The problem seems to be with the “Data Warehouse Main” data source. You can edit it from http://servername/reports. It is in the root and it is hidden, but if you click “show details” you will se it. The problem was that the connection string was blank and that the security settings was incorrect.

-Connection string

• data source=servername;initial catalog=OperationsMnaagerDW;Integrated Security=SSPI
  

-Connect using

• Credentials are not required

I have seen that issue in news groups before so I am not sure that has to do with Windows Server 2008. I think it is because I reconfigure the application pool and interrupt the encrypted content in SQL Reporting. 
Some word about working with Hyper-V.

Last time I tried Microsoft Virtualization was in some early beta of Windows Server 2008. Virtualization was not very fast in that version. But this version is really nice to work with, installing OS don’t take many minutes, I have tried Windows Server 2003 and 2008 as guests, both X64. I recommend you all you try it in your lab.

As you all understand this setup of Windows Server 2008 and Ops Mgr 2007 is not supported at all, so dont try this in your production environment.

This weekend I have been tinker with SNMP in Ops Mgr 2007. It is fairly simple to create performance views and alerts based on SNMP traps. This is a great way to take control and get a overview of network devices like for example switches. You can create both alerts and performance views based on SNMP. If you are not sure which OID to build your monitor and rules on, you can use a tool named AdRem SNMP Manager. It includes a number of functions to handle SNMP, download a evaluation copy here. Getif is a free multi-functional Windows GUI based Network Tool that I also recommend when working with SNMP, link.

 

 

I saw a question about how to view all overrides. Boris Yanushpolsky in the Ops Mgr product team at Microsoft has made a tool to work with overrides in Ops Mgr 2007. With this tool you can easy browse overrides based on type or computer. Download the tool here. If you dont want to do it with a extra tool or if you want to include it in some kind of script, you can list overrides with command shell too,

get-managementpack | get-override

I saw a question from a engineer who recived a number of notifications about the same alert from different notification subscriptions. The questions was how to track down which subscription sending these notifications from the subscription ID. You can do that with Command Shell. Run

get-NotificationSubscription | where {$_.ID -like “subscription ID”}

 

In this post I will show how to setup SQL logon auditing. You will have to complete a number of steps before you have a complete auditing. Beware that logging all logon events can fill up your database. This post includes the following steps:

1. Configure SQL to audit logon events
2. Configure Ops Mgr to collect logon events
3. Create a report to show the collected data

Configure SQL to audit logon events

Login auditing can be configured to write to the error log on the following events.

• Failed logins
• Successful logins
• Both failed and successful logins

To configure login auditing

1. In SQL Server Management Studio, connect to an instance of the SQL Server Database Engine with Object Explorer.
2. In Object Explorer, right-click the server name, and then click Properties.
3. On the Security page, under Login auditing, click the desired option.
4. After you have applied this setting you might need to restart the SQL service before it take effect. After that you should see logon events in the local application log, in event viewer.

Configure Ops Mgr to collect logon events

You will need some suitable class to target your rule to. If you import the SQL MP you will get a number of SQL classes.

1. In the Ops Mgr Console click Authoring
2. Right-click Rules and choose to create a new rule
3. Create Rule Wizard – Rule Type: Choose to create a Collection Rules/Event Based/NT Event log. Choose a suitable management pack and click next
4. Create Rule Wizard – General: Input a rule name for example “SQL Auditing – Success Logon”. Choose a target, for example “SQL 2005 DB Engine”. Click Next
5. Create Rule Wizard – Event Log Type: Log Name should be Application then click Next
6. Create Rule Wizard – Build Event Expression: input
   Event ID equals 18453
   Event Source equals MSSQLSERVER
   Click Create

To collect also failed logon you need to create a rule to collect event ID 18456.

You can use the Effective Configuration Viewer from the Ops Mgr 2007 resource kit to verify if your new rule affect a SQL machine. You can also create a new event view, to show these events, after a couple of minutes you should see them.

Create a report to show the collected data

There is no report model in Ops Mgr by default, so if you want to create a brand new report, you must start with create a report model. You can do that with SQL Server Business Intelligence Development Studio. Take a look at Jonathan Hamb step by step guide how to create a report model here.

What you can do, with default reports, is a linked report. A linked report is like a shortcut to a program, it is a link that provide settings that inputs in a existing report. A linked report always inherits report layout and data source properties of the original report. All other properties and settings can be different from those of the original report, including security, parameters, location, subscriptions, and schedules.

To create a new report, for example a report showing all success logon events,

1. Start the Ops Mgr console and click Reporting
2. Click Microsoft Generic Report Library
3. Click Event Analysis and then Open
4. In the report select suitable FROM and TO for example
5. Click Add Group and select SQL 2005 DB Engine Group
6. Select MSSQLSERVER as SOURCE, 4 as Type, 18453 as Event ID and Success Audit as Event Type
7. Click Run

Now you can see a report with all Sucess Audit. You can now click the File menu and choose Publish. This report will now be stored as a linked report under Authored Reports. The next time you want to see success logons to SQL you can click this report direct in the console, and all the parameters will be there.

If you select MSSQLSERVER as source and 4 as event type, you will get both Failure and Success audit events, but you must first make sure you have rules to collect them both.

Ops Mgr SP1 RC0 is here. You can download it from MS Connect, here. This version will support upgrade to the final version of SP1. The final version will be public around mid febuari 2008.

Some of the news in SP1 RC0

• Improved performance when working in the console
• Improved advanced search in the console
• Support for both SNMP v1 and SNMP v2 network devices
• Support for exporting diagrams to Visio VDX format
• Support for copy/paste from the alert details pane (Ctrl+C and Ctrl+V)
• RMS encryption key backup wizard
• Support to copy views between management packs
• Both the repeatcount and override summary function are improved
• Scripts can noe be used for diagnostic tasks
• Enable to publish a report to for example sharepoint services web sites
• ACS is now supported on the Management and Gateway server roles
• ACS forwarding can be enable with command shell script

Read more about What’s New in Operations Manager 2007 Service Pack 1, here

If you want to discuss the service pack please join the SP1 news group at microsoft.public.opsmgr.sp1 at news.microsoft.com. I recommend JetBrains Omea Readers as news group reader, you can download it here. More information about Microsoft news groups here.