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.