Today I was investigating an issue with OpsMgr Agents who where stuck in Maintenance Mode. A big help was this article from David Dixon. We managed to solve the issue but we also wanted to know what caused the issue and I investigated if the RMS HealthService was put in Maintenance Mode accidentally. By the way is not a good thing to put the RMS in Maintenance Mode! I used the next SQL query to find out if the RMS HealthService was put in Maintenance Mode: -- Find if RMS is put in MM single query-- Author: Stefan Stranger-- Date: 19-05-2010USE OperationsManagerDWSELECT     ManagedEntity.DisplayName, MaintenanceModeHistory.*FROM         ManagedEntity INNER JOIN                      MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId INNER JOIN                      MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowIdWHERE     (ManagedEntity.FullName = N'Microsoft.SystemCenter.HealthService:rmsservername.contoso.com') Have fun pointing your finger ;-)  

Have you ever wanted to check if AD Integration is enabled for an Agent using a SQL query? Today I wanted to do that and I created the next SQL Query. You may find it usefull. /Find all Agent where AD Integration is configuredRun on OperationsManager DatabaseAuthor: Stefan Stranger/SELECT bme.DisplayName, mths.ActiveDirectoryManagedFROM MT_HealthService mths INNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityId WHERE ActiveDirectoryManaged = 1ORDER BY DisplayName /Find all Agent where AD Integration is not configuredRun on OperationsManager DatabaseAuthor: Stefan Stranger/SELECT bme.DisplayName, mths.ActiveDirectoryManagedFROM MT_HealthService mthsINNER JOIN BaseManagedEntity bme on bme.BaseManagedEntityId = mths.BaseManagedEntityIdWHERE ActiveDirectoryManaged = 0ORDER BY DisplayName

Source: Thomas LaRock Thomas LaRock has published a SQL query I had not seen before, about finding the OpsMgr Database Sizes. Could be handy to know. [Thomas LaRock] “I was asked last week if  knew a query that would return the size of the databases that have been discovered by Operations Manager. I went about digging through some old queries I put together and found the information thatw as requested. Inside the OperationsManager database look for a view named MTV_Database. All discovered databases will be there. There should be a column for DatabaseSize, but the column will have a GUID at the end of the name. Now, I forget where I found it, but a while back I came across one of the view definitions that helped me to return a lot of other information, such as SQL version. The view that has SQL version is named MTV_DBEngine, but in order to join the MTV_Database to the MTV_DBEngine I needed to add in a handful of relationship tables and filter on the BaseManagedTypeID. I was going mad trying to make everything work until I found that specific view definition and I don’t recall which one it is, but what I do have is the end result which I can share (your GUIDs will be different, I imagine):” Read more at source. Time to at this new query to your OneNote SQL database queries, just like I did ;-)

I helped a customer creating a OpsMgr Monitor for checking if the there has been a SQL Full or Differential Backup within a specified number of hours. David Scheltens created the SQL query. Remark: Please read information on System Center Central for more info on this article. This is how you could create this kind of monitor in your own environment. Steps:

Create a Timed Script Two State Monitor. Save Monitor in other MP than Default MP. Give the Monitor a Name, like “Custom – SQL Full or Differential Backup Check”. Target the Monitor to SQL Database. Select a suitable Parent Monitor like Availability. Disable Monitor (enable by an Override later). Configure how often this Monitor should run (default is 15 mins, but you could change this depending on the time your SQL backups run) Copy script from here. The script uses three parameters (or arguments) - Param 0: The SQL connection string for the server - Param 1: The Database to use - Param 2: The threshold (in hours) to use Configure the Parameters.    Configure Unhealthy Expression. Property[@Name='NumHours'] Greater Than 1 (threshold when your backup should have been run)
Configure Healthy Expression Property[@Name='NumHours'] Less than or equal to 1 (threshold when your backup should have been run)
Configure Health.
Configure Alerting I used the Reason, BackupType and Number of Hours in the Alert Description.
Open the MP XML file and change the data type in the xml configuration from string to integer. Since making this change, the alerts and state changes seems to occur when at the correct time.More info here: (thanks to Daniele Grandini)<ErrorExpression>             <SimpleExpression>               <ValueExpression>                 <XPathQuery Type="Integer">Property[@Name='NumHours'] </XPathQuery>               </ValueExpression>               <Operator>Greater</Operator>               <ValueExpression>                 <Value Type="Integer">20</Value>               </ValueExpression>             </SimpleExpression>           </ErrorExpression>           <SuccessExpression>             <SimpleExpression>               <ValueExpression>                 <XPathQuery Type="Integer">Property[@Name='NumHours'] </XPathQuery>               </ValueExpression>               <Operator>LessEqual</Operator>               <ValueExpression>                 <Value Type="Integer">20</Value>               </ValueExpression>             </SimpleExpression>           </SuccessExpression> 15. Enable the Monitor via an Override. Remark: You may need to Use a Run As Account with the right permission for this Monitor. Result:

Some time ago I created a Tutorial How to Associate a Run As Account to a Monitor.  

In Operations Manager 2007, Run As Profiles and Run As Accounts are used to select users with the privileges needed for running rules, tasks, and monitors. Management Pack authors create a rule, task, or monitor, and then associate it with a Run As Profile. The named Run As Profile is imported along with the Management Pack into Operations Manager 2007. The Operations Manager 2007 administrator creates a named Run As Account and specifies users and groups. The administrator then adds the Run As Account to the Run As Profile and specifies the target computers that the account should run on. The goal in this short tutorial is to create a Timed Script Two State Monitor and associate a run as account to this monitor and save it all in a Management Pack. The Timed Script Two State Monitor is a vbscript that uses WMI to check if BizTalk orchestrations are not started. I’ve got some great help from Jakub Oleksy (http://blogs.msdn.com/jakuboleksy/default.aspx) and Steve Wilson (http://www.authormps.com/dnn/) You can download the tutorial here.

Lately I’ve been busy creating some OpsMgr Custom Reports for customers and wanted to share some tips & tricks for creating custom OpsMgr Reports. Hope you can use them in your own custom OpsMgr Reports. First some background info on what to use for creating Custom OpsMgr Reports.

What do I need for creating Custom OpsMgr Reports?

Your first start needs to be, reading the Operations Manager 2007 Report Authoring Guide Secondly you need to install Visual Studio and SQL Server Business Intelligence Development Studio What next? How do I create my first OpsMgr Custom Report?

The highlevel steps for creating a Custom OpsMgr Report are:

Create a database query to get the data you need Use Visual Studio (SQL Server Business Intelligence Development Studio) to create the Report Deploy the Report to OpsMgr In the Operations Manager 2007 Report Authoring Guide are some examples to get you started. (starts on page 23) How do I make my Custom Report more beautiful? 

It depends on what you mean with more beautiful ;-) But here are some examples I use to make my custom OpsMgr Reports more beautiful;

Insert Charts Insert Pictures Show extra info, like;

who run the report how long did it take for the report to run extra parameters (Begin Date and End Date, Filter on ComputerGroup Members). Let’s start with the Custom Reports - Sample Scenario for a Simple Report 1 from Operations Manager 2007 Report Authoring Guide and try to make this report a little bit more beautiful ;-) Background info: This report collects all Events with an ID of 21025. I created this Custom Report on my OpsMgr 2007 R2 environment with SQL 2008 and Visual Studio 2008. Here are the steps taken from the Authoring Guide with some extra info added me: Procedures To create a report using Visual Studio (open SQL Server Business Intelligence Development Studio)

Open SQL Server Business Intelligence Development Studio and click on File and create a new Project   Select Report Server Wizard Project Wizard and give your Report a Name and click on OK.
Click Next on Report Wizard Screen
Create a New data source and give it a name like, DataWarehouseMain and click on Edit.
Enter the correct Connection Properties and click on OK.
Click on Next in Report Wizard screen
Now we need to use the SQL query from the Authoring Guide. Copy and past the SQL query to the Query string window.

SELECT  evt.eventnumber,Evt.EventOriginId,    Evt.EventPublisherRowId,    Pub.EventPublisherName,    Evt.DateTime,    Evt.EventChannelRowId,    Chl.EventChannelTitle,    Evt.EventCategoryRowId,    Chl.EventChannelTitle,    Evt.EventLevelId,    Lev.EventLevelTitle,    Evt.LoggingComputerRowId,    Cmp.ComputerName,    Evt.EventDisplayNumber,    Evt.UserNameRowId,    Usr.UserName,    Det.RawDescription,    Det.RenderedDescription,    Det.EventData FROM Event.vEvent(NoLock) Evt       Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId  = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId  = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId  = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId         = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join      EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join      Event.vEventDetail      (NoLock) Det      On Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025

TIP: Test this query first in SQL Management Studio.
Ok, we know now this query will give us some results back when we use this in our Custom Report. 8. Select the report type, Matrix or Table, and then click Next.      
9. Select the fields to be displayed on the different areas of the report, and then  click Next.    
10.Select the style of the layout.    
11. Select the Deployment Location and click on Next.    
12. Give the Report a Name and Review the Report Summary and select Preview Report and click on Finish

So this is the Result:

Maybe not completely what you wanted. Let’s change that. These are some things we are going to change:

Less columns (EventNumber, Event Publisher Name, Date Time, ComputerName, UserName and Rendered Descripition) Add a Start and End Data Parameter Add a select Computer Group Parameter Add who run the report and how long did it take for the report to run Less columns If we want less columns in our Report we can do two things; change the SQL query or remove the columns in the Report Designer. If you don’t need the columns leave them out the query, I would say. We can change the SQL query to:

SELECT     Pub.EventPublisherName,    Evt.DateTime,    Cmp.ComputerName,    Evt.EventDisplayNumber,    Usr.UserName,    Det.RenderedDescription FROM Event.vEvent(NoLock) Evt       Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId  = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId  = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId  = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId         = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join      EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join      Event.vEventDetail      (NoLock) Det      On Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025 Add a Start and End Data Parameter If we want to be able to select a Start and End Date parameter in the report we need to add some variables to our query.

DECLARE @StartDate DATETIME DECLARE @EndDate   DATETIME SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-21 00:00:00.000' SELECT     Pub.EventPublisherName,    Evt.DateTime,    Cmp.ComputerName,    Evt.EventDisplayNumber,    Usr.UserName,    Det.RenderedDescription FROM Event.vEvent(NoLock) Evt       Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId  = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId  = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId  = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId         = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join      EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join      Event.vEventDetail      (NoLock) Det      On Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025 AND       Evt.DateTime BETWEEN @StartDate AND @EndDate Remark: Use above query in SQL Management Studio for testing the results. In Visual Studio you don’t need to DECLARE and SET the StartDate and EndDate. Let’s test this first in Visual Studio The easiest way is to create a new Report using the already created Data Warehouse.   If you select Add New Report the same Report Wizard is shown. See for the steps above, but now use the new SQL query. There is no need to create a New Data Source

After clicking Next you see the Preview Report and there you see the two added parameters Start Date and End Date

Before we can use these parameters we need to configure them. Click on Design. Double click on Parameters in the Report Data screen (in Visual 2005 you go to tools I believe)

Change the Data Type from Text to Date/Time for both Parameters

Test the Report and select different Start and End Dates

Add  Computer Group Parameter Now we want to add a Computer Group parameter so we can select a Computer Group where we want to see the events from it’s members. We first need to create a SQL query that shows us all the OpsMgr Computer Groups in a dropdown list.

SELECT DisplayName FROM vManagedEntity WHERE ManagedEntityTypeRowID in (SELECT ManagedEntityTypeRowID from dbo.ManagedEntityDerivedTypeHierarchy ((SELECT ManagedEntityTypeRowId from vmanagedentitytype WHERE managedentitytypesystemname = 'system.group'),0)) ORDER BY DisplayName This will give us the next result.

Now we need to create a new DataSet for this query in Visual Studio. Select Add DataSet.

Give the Dataset a Name (ComputerGroup) insert the above SQL query.

And click on OK to save DataSet

But how do we use this ComputerGroup and it’s member to filter the eventids in our Report? First we need to retrieve the the members of the ComputerGroup and store them in a  Table variable so we can use this in a JOIN. A table variable is created in memory, and so performs slightly better than #temp tables (also because there is even less locking and logging in a table variable). The next SQL query retrieves all members of a given ComputerGroup.

DECLARE @GroupDisplayName NVARCHAR(250) SET @GroupDisplayName = 'Agent Managed Computer Group'; USE [OperationsManagerDW] Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName=@GroupDisplayName Result from SQL Management Studio:

If we place the results of this query in a temp table or table variable we can use this in a JOIN on ComputerName from the Event Table. TIP: Use the Design Query in Editor if you need to create a JOIN for your query.

TIP: Create the TEMP table first as a new table in your database, so you can use this table in the Design Query in Editor SQL Server Management Studio To create the table ComputerGroupMembers in the OperationsManagerDW database run the next query.

CREATE TABLE ComputerGroupMembers (Displayname NVARCHAR(250)) INSERT INTO ComputerGroupMembers Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName='Management Server Computer Group' -- 'Management Server Computer Group' of -- 'Agent Managed Computer Group'

                                                                  We now created the ComputerGroupMember table. We can use this table to only show the records for the members of our computer group. First we take a look at the base query and remove any unnecessary stuff.

SELECT     Pub.EventPublisherName,    Evt.DateTime,    Cmp.ComputerName,    Evt.EventDisplayNumber,    Usr.UserName,    Det.RenderedDescription FROM Event.vEvent(NoLock) Evt       Inner Join EventPublisher(NoLock) Pub On Evt.EventPublisherRowId  = Pub.EventPublisherRowId Inner Join EventChannel(NoLock) Chl On Evt.EventChannelRowId  = Chl.EventChannelRowId Inner Join EventCategory(NoLock) Cat On Evt.EventCategoryRowId  = Cat.EventCategoryRowId Inner Join EventLevel(NoLock) Lev On Evt.EventLevelId         = Lev.EventLevelId Inner Join EventLoggingComputer(NoLock) Cmp On Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId Inner Join      EventUserName(NoLock) Usr On Evt.UserNameRowId= Usr.EventUserNameRowId Inner Join      Event.vEventDetail      (NoLock) Det      On Evt.EventOriginId = Det.EventOriginId Let’s copy this to the Design Query in Editor tool.

Reorder the tables and have look at the JOINs in the original query.

Now we have a look at how we  could create a JOIN on the ComputerGroupMembers temp table we have created. Let’s add our table.

Then we need to create a JOIN for DisplayName with ComputerName in the Cmp table.

Remark: In some OpsMgr environments the vEventLoggingComputer View of the OperationsManagerDW database returns a NETBIOS name instead of the FQDN and your JOIN is not working. Please check this by running the next SQL query:

SELECT * FROM vEventLoggingComputer If the above results return FQDN you are ready to go on. If not contact me and I’ll help you solve this by using another query. Let’s assume you can create the JOIN  between the ComputerGroupMembers (FQDN members) and the vEventLoggingComputer View. We are only interested in the Rows from our ComputerGroupMembers.

When we test this query in the SQL Analyzer we get this:

SELECT     Pub.EventPublisherName, Evt.DateTime, Cmp.ComputerName, Evt.EventDisplayNumber, Usr.UserName, Det.RenderedDescription,                       ComputerGroupMembers.Displayname FROM         Event.vEvent AS Evt WITH (NoLock) INNER JOIN                       EventPublisher AS Pub WITH (NoLock) ON Evt.EventPublisherRowId = Pub.EventPublisherRowId INNER JOIN                       EventChannel AS Chl WITH (NoLock) ON Evt.EventChannelRowId = Chl.EventChannelRowId INNER JOIN                       EventCategory AS Cat WITH (NoLock) ON Evt.EventCategoryRowId = Cat.EventCategoryRowId INNER JOIN                       EventLevel AS Lev WITH (NoLock) ON Evt.EventLevelId = Lev.EventLevelId INNER JOIN                       EventLoggingComputer AS Cmp WITH (NoLock) ON Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId INNER JOIN                       EventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN                       Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId RIGHT OUTER JOIN                       ComputerGroupMembers ON Cmp.ComputerName = ComputerGroupMembers.Displayname           This is still not completely correct but this is a start to work from.

Ok, let’s try to fix this. First let us pick the Tables or Views we need for our query. I think we need the next table (ComputerGroupMembers) and views.

Let’s create the some JOINs.

So your query could look something like this:

SELECT     ComputerGroupMembers.Displayname, Evt.DateTime, Evt.EventDisplayNumber, Det.RenderedDescription, Usr.UserName FROM       ComputerGroupMembers WITH (NOLOCK) INNER JOIN            vEventLoggingComputer AS Cmp WITH (NOLOCK) ON Cmp.ComputerName = ComputerGroupMembers.Displayname INNER JOIN            Event.vEvent AS Evt WITH (NOLOCK) ON Cmp.EventLoggingComputerRowId = Evt.LoggingComputerRowId INNER JOIN            vEventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN            Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025 TIP: Use the NOLOCK option. SQL Server offers many hints that can be used to determine how a query executes and therefore the impact of the query on other processes.  One such hint that is offered in SQL Server is the NOLOCK hint.  This query hint allows SQL Server to ignore the normal locks that are placed and held for a transaction and allows the query to complete without having to wait for the first transaction to finish and therefore release the locks. Let’s test above query in SQL Management Studio by changing the Members of the Computer Group.

I changed the members of the ComputerGroupMembers table by changing the the Computer Group in above query. Now let’s look at our Report Query. Hopefully we only see events from OpsMgrR2DC1.opsmgrdemo.r2. Before changing the Computer Group:

After Changing the Computer Group:

It seems to work ;-) No we can remove the ComputerGroupMembers table from the OperationsManagerDW database because we are going to use a temp/variable table later on. The ComputerGroupMembers table was only to help us create the correct query. Run DROP TABLE ComputerGroupMembers to delete the table. We are going to use the next query to create a variable table in our last query.

DECLARE @StartDate DATETIME DECLARE @EndDate   DATETIME DECLARE @ComputerGroupMembers TABLE (DisplayName NVARCHAR(250)) DECLARE @GroupDisplayName NVARCHAR(250) SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-21 00:00:00.000' SET @GroupDisplayName = 'Management Server Computer Group'; --INSERT ComputerGroup Members in Variable table @ComputerGroupMembers --Use Variables if the table is not large so it can be hold in memory --Retrieves Members of selected OpsMgr ComputerGroup INSERT INTO @ComputerGroupMembers Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName=@GroupDisplayName SELECT     t1.Displayname, Evt.DateTime, Evt.EventDisplayNumber, Det.RenderedDescription, Usr.UserName FROM       @ComputerGroupMembers as t1 INNER JOIN            vEventLoggingComputer AS Cmp WITH (NOLOCK) ON Cmp.ComputerName = t1.Displayname INNER JOIN            Event.vEvent AS Evt WITH (NOLOCK) ON Cmp.EventLoggingComputerRowId = Evt.LoggingComputerRowId INNER JOIN            vEventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN            Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025 AND       Evt.DateTime BETWEEN @StartDate AND @EndDate We save the Members of the Computer Group ‘Management Server Computer Group’ in variable which we later use in the final SELECT statement. And now we can finally go back to Visual Studio and use the above query in our Simple Report 1. Again add a new report and start the Report Wizard. Use the next query in the Query Builder:

DECLARE @GroupDisplayName NVARCHAR(250) INSERT INTO @ComputerGroupMembers Select vManagedEntity.Displayname from vManagedEntity join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId where vme2.DisplayName=@GroupDisplayName SELECT     t1.Displayname, Evt.DateTime, Evt.EventDisplayNumber, Det.RenderedDescription, Usr.UserName FROM       @ComputerGroupMembers as t1 INNER JOIN            vEventLoggingComputer AS Cmp WITH (NOLOCK) ON Cmp.ComputerName = t1.Displayname INNER JOIN            Event.vEvent AS Evt WITH (NOLOCK) ON Cmp.EventLoggingComputerRowId = Evt.LoggingComputerRowId INNER JOIN            vEventUserName AS Usr WITH (NoLock) ON Evt.UserNameRowId = Usr.EventUserNameRowId INNER JOIN            Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE Evt.EventNumber = 21025 AND       Evt.DateTime BETWEEN @StartDate AND @EndDate

As you can see there are three parameters needed. Parameter GroupDisplayName is filled by the ComputerGroup DataSet.

Result:

As you see we need to configure the Group Display Name Therefore we need the ComputerGroup DataSet   Go to Parameters folder and right click and select Parameter Properties.

Select Get Values from a query, select the ComputerGroup Dataset and select the DisplayName for Value field and Label Field and click on OK.

Test the Report with the Preview Tab.

Result:

As you see selecting the ‘All Windows Computer’ Group returns all events with eventid 21025 from all members of the All Windows Computer Group! Who run the report and how long did it take for the report to run We can add extra info to the report by inserting two text boxes with who run the report and how long it took for the report to run. Add a TextBox from the Toolbox to your Report

Insert the next expression into you text box:

="Generated by " + User!UserID + " on " + Globals!ExecutionTime.ToString()

Add another TextBox for the How long the report has run. Use the next expression:

="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",    ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")) ) & " at server: " & Globals!ReportServerUrl                                     Result:

Final Tip: Change the parameter order. With up and down arrows you can change the order of your parameters.

So if you have done all this and more your Simple Report 1 could look like this.

  I’ve attached the Report.rdl file so you can have a look at the complete report. Have fun creating OpsMgr Custom Reports and have a great New Year!

A customer was asking for some information about the time it took for users to logon on their Terminal Server hosted Windows Desktops. Luckily there were two eventID’s created during the logon process.  And if you calculate the time between those eventID’s then you have some insight in the Logon time for a user. When a user logs on, Security Event 528 is created. Event 528 is logged whenever an account logs on to the local computer, except for in the event of network logons (see event 540). Event 528 is logged whether the account used for logon is a local SAM account or a domain account. On a Windows 7 client Security event 4624 is created. Example Screenshot: And then they used Event ID 865 — Software Restriction Policy Notification for the last Log on event. So the Log on Time could be calculated the time difference between those eventID’s. You should look for your own eventID if you want to create a simular report. So how can these Events be used to create a Logon Time Report? High Level steps: Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr. Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user. Create a Custom Logon Time OpsMgr Report with Visual Studio. Let’s start with a more detailed overview of the steps. For testing purposes I created a batch script, which you can use to create two dummy eventID’s in the Application Event Log. You can run this script using different credentials (users) with the SysInternals tool ShellRunAs. @echo off REM First EventID 528 LastEventID 865 Eventcreate /L Application /D "First EventID created" /T INFORMATION /ID 528 REM Create Random number between 1 -30 secs. Set max=30 Set min=1 :: Generate number between Min and Max Set /A rand=%random% %% (max - min + 1)+ min REM Wait for 1-30 secs to generate second EventID sleep %rand% Eventcreate /L Application /D "Last EventID created" /T INFORMATION /ID 865 The above script uses the sleep.exe command so you need to install it from the Reskit if you haven’t installed that yet. As you can see the time between the two events is a random number between 1 – 30 secs. Save above code to LogOndemo.cmd and run from command prompt. [Screenshots]   Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr Ok now we have the two dummy events are created we can create the Event Collection Rules. Make sure you created a new MP to store your Event Collection Rules. Don’t save them in the Default MP ;-) Disable Rule we enable the rules later for the servers we want to Collection Rules to be running on. Keep in mind these example Collection Rules are created for the dummy eventID’s. You should select your own EventID’s. Enable Rules for server(s) you need to have to Collection Rule running on. For EventID 865 follow the same steps as shown above. Don’t forget to enable the Rule with an Override!   Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user. And now the most difficult part of creating a Custom Report, creating the right Database SQL queries. I got some help from some SQL guru’s at the office, so you may want to talk to your local dba for some pointers about creating the right SQL queries. First we need to determine what we want to show in our Reports. Right? The customer wanted to see the Minimum, Maximum and Average LogOn time per user on a server. Something like this:   But first some steps I took to create the SQL queries. First I wanted to collect all the 528 and 865 events collected by the two Collection Rules (FirstLogOn Event 528 and LastLogOn Event 865). And I used the next SQL query from Anders Bengtsson for that and changed that a little. It collects all the Collected Events 528 and 865 within the selected period ('2009-12-01 00:00:00.000' and '2009-12-12 00:00:00.000') DECLARE @StartDate datetime DECLARE @EndDate   datetime SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-12 00:00:00.000'; USE OPERATIONSMANAGERDW SELECT     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription' FROM Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId WHERE vEventLevel.EventLevelTitle = 'Information' AND vEvent.Datetime between @StartDate and @EndDate AND (vEvent.EventDisplayNumber = 528 OR vEvent.EventDisplayNumber = 865)    Result Screenshot: It’s a start but not completely what we want ;-) As you can see to LogOn time for user OpsMgrDemo\OM_Admin is 22 seconds. So which query do we need to create to calculate the time difference between those two events per UserName? DECLARE @StartDate datetime DECLARE @EndDate   datetime SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-12 00:00:00.000'; WITH CTE AS     (SELECT     ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription'     FROM     Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId     WHERE vEventLevel.EventLevelTitle = 'Information'     AND vEvent.Datetime between @StartDate and @EndDate     AND (vEvent.EventDisplayNumber = 528     OR vEvent.EventDisplayNumber = 865)        ) SELECT * , (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime , DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime FROM CTE WHERE RowNum%2 = 1 Result Screenshot: As you can see, per UserName is calculated what the LogOn Time is. Again you see that it took 22 seconds for the LogOn for User OM_Admin. You could already start using this SQL query as Database SQL Query in your Custom OpsMgr Report in Visual Studio. You could for instance, Group the data on Computer or User to generate a Report on LogOn time for users on a specific Computer or User. Example Screenshot: But we wanted to create a Report with MIN, MAX and AVG LogOn time per user, right? For that we need the next SQL query. DECLARE @StartDate datetime DECLARE @EndDate   datetime SET @StartDate = '2009-12-01 00:00:00.000' SET @EndDate = '2009-12-12 00:00:00.000'; -- Create TEMP table --(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LoggOffTime, LogTime) DECLARE @temp TABLE(     RowNum INTEGER,     DateTime DATETIME,     EventSource NVARCHAR(250),     Computer NVARCHAR(250),     Type NVARCHAR(250),     EventID INTEGER,     EventChannelTitle NVARCHAR(250),     UserName NVARCHAR(250),     EventDescription NVARCHAR(250),     LogOffTime DATETIME,     LogTime INTEGER ); WITH CTE AS     (SELECT        ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription'     FROM     Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId     WHERE vEventLevel.EventLevelTitle = 'Information'     AND vEvent.Datetime between @StartDate and @EndDate     AND (vEvent.EventDisplayNumber = 528     OR vEvent.EventDisplayNumber = 865) ) INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime) SELECT * , (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime , DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime FROM CTE WHERE RowNum%2 = 1 SELECT     Computer,     UserName,     COUNT(t1.LogTime) AS [NUMBEROFLOGINS],     MAX(t1.LogTime) AS [MAXLOGTIME],     MIN(t1.LogTime) AS [MINLOGTIME],     AVG(t1.LogTime) AS [AVGLOGTIME]     FROM @temp t1     GROUP BY Computer,UserName Screenshot Result: Yes! This is what we were looking for. Now we have the right Data SQL query, we can Open Visual Studio to create our Custom User LogOn Time Report. Create a Custom Logon Time OpsMgr Report with Visual Studio Let’s open SQL Server Business Intelligence Development Studio. Create a New Project and select Report Server Project Wizard and give your project a name. Select Next in the Welcome to the Report Wizard Screen Create a Data Source Click Edit button to enter the SQL Server information. And Test Connection if you want. Click Next in the Report Wizard Screen.   Enter the previously created SQL query with some changes (remove the Declare statements). DECLARE @temp TABLE(     RowNum INTEGER,     DateTime DATETIME,     EventSource NVARCHAR(250),     Computer NVARCHAR(250),     Type NVARCHAR(250),     EventID INTEGER,     EventChannelTitle NVARCHAR(250),     UserName NVARCHAR(250),     EventDescription NVARCHAR(250),     LogOffTime DATETIME,     LogTime INTEGER ); WITH CTE AS     (SELECT        ROW_NUMBER() OVER(ORDER BY vEvent.DateTime) AS RowNum,     vEvent.DateTime,     vEventPublisher.EventPublisherName as 'EventSource',     vEventLoggingComputer.ComputerName as 'Computer',     vEventLevel.EventLevelTitle as 'Type',     vEvent.EventDisplayNumber as 'EventID',     vEventChannel.EventChannelTitle,     vEventUserName.UserName,     vEventDetail.RenderedDescription as 'EventDescription'     FROM     Event.vEvent LEFT OUTER JOIN     vEventUserName ON vEvent.UserNameRowId =     vEventUserName.EventUserNameRowId LEFT OUTER JOIN     vEventCategory ON vEvent.EventCategoryRowId =     vEventCategory.EventCategoryRowId LEFT OUTER JOIN     vEventPublisher ON vEvent.EventPublisherRowId =     vEventPublisher.EventPublisherRowId LEFT OUTER JOIN     vEventLoggingComputer ON vEvent.LoggingComputerRowId =     vEventLoggingComputer.EventLoggingComputerRowId LEFT OUTER JOIN     vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFT OUTER JOIN     vEventChannel ON vEvent.EventChannelRowId =     vEventChannel.EventChannelRowId LEFT OUTER JOIN     Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId     WHERE vEventLevel.EventLevelTitle = 'Information'     AND vEvent.Datetime between @StartDate and @EndDate     AND (vEvent.EventDisplayNumber = 528     OR vEvent.EventDisplayNumber = 865) ) INSERT INTO @temp(RowNum, DateTime, EventSource, Computer,Type,EventID, EventChannelTitle, UserName, EventDescription, LogOffTime, LogTime) SELECT * , (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) AS LogOffTime , DATEDIFF(s, CTE.DateTime, (SELECT T2.DateTime     FROM CTE AS T2     WHERE (CTE.RowNum + 1)= T2.RowNum) ) AS LogTime FROM CTE WHERE RowNum%2 = 1 SELECT     Computer,     UserName,     COUNT(t1.LogTime) AS [NUMBEROFLOGINS],     MAX(t1.LogTime) AS [MAXLOGTIME],     MIN(t1.LogTime) AS [MINLOGTIME],     AVG(t1.LogTime) AS [AVGLOGTIME]     FROM @temp t1     GROUP BY Computer,UserName Copy and paste above (or your own query) to the Query Builder window and click on Next. Select your Report Type and click on Next Select how you want to group your report. (I just kept the default settings) Select the Table Style of the Report and click on Next Enter Report server and Deployment folder info and click on Next. Give your Report a name and select Preview Report and click on Finish Change Report Parameters from Text to Date/Time Date Type. You need to change this for both parameters! Let’s check the Report. Go to Preview, select the Start Date and End Date and click on View Report. Ok it’s not exactly the way we would like it to be, but the results are there! Let’s Pimp this Report a little. So this could be the end result. And you can pimp it even more if you want…   Have fun creating Custom OpsMgr Reports!

This week a got a question from a customer about ‘flapping’ taking place from a Monitor. I found out that they meant the State Changes that took place for a newly created Monitor. They found out that for a certain machine the State changed quite often and this was caused by a Recovery Task that was part of this monitor. So they wanted to know if there was a way to see quickly if a Monitor caused a lot of State Changes. There are quite some SQL queries you can use to have a look at the State Changes taken place in your OpsMgr environment. Just take a look at some of the SQL queries on the weblog of my colleague Jonathan Almquist. But what if you only want to know the State Changes that took place for a specific Monitor? Again you can use one of Jonathan’s SQL queries and change them to fit your needs. And this is exactly what I’ve done but I also wanted to run the query from the OpsMgr Command Shell. The reason for this was that I easily wanted to retrieve the Monitor Name using the OpsMgr Get-Monitor Cmdlet.   So here is the PowerShell script I created. You can change the SQL query if you want, to fit your own needs ;-) Result screenshot: Have fun with OpsMgr and PowerShell!

I know there are already quite some other blog posts about OpsMgr Data Warehouse Grooming.  But I was helping a customer with grooming their OpsMgr Data Warehouse Database (OperationsManagerdw) and got some questions. And you may have the same questions but you are afraid to ask ;-)

How can I change the Grooming settings for the OpsMgr Data Warehouse? This cannot be done from within the OpsMgr Console. So what are the options then?

DWdatarp tool Use the Data Warehouse Data Retention Policy (dwdatarp.exe) tool from the MOM team weblog. Tip: if after running the tool you don’t see any results, you may not be dbowner on the OperationsManagerDW database.   You can save the results to a csv, but you need to do some manual stuff in Excel to have a nice formatted overview.   Read the help (dwdatarp /?) for all the options. You can also change the Grooming settings for the OpsMgr Data Warehouse with this tool.

SQL queries You have to use some SQL queries and run those on the operationsmanagerdw database in SQL Server Management Studio.   To view the Current OpsMgr Data Warehouse queries I use the next SQL queries from several sources.

--Current Grooming Settings USE OperationsManagerDW SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation --Last Grooming Time USE OperationsManagerDW select min(datetime) as MinDate, max(datetime) as MaxDate, datediff(d,min(datetime),max(datetime)) AS NoOfDaysInDataSet from Perf.vPerfHourly --To view the number of days of total data of each type in the DW: USE OperationsManagerDW SELECT DATEDIFF(d, MIN(DWCreatedDateTime), GETDATE()) AS [Current Alert] FROM Alert.vAlert SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Event] FROM Event.vEvent SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Perf Raw] FROM Perf.vPerfRaw SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Perf Hourly] FROM Perf.vPerfHourly SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Perf Daily] FROM Perf.vPerfDaily SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current State Raw] FROM State.vStateRaw SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current State Hourly] FROM State.vStateHourly SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current State Daily] FROM State.vStateDaily --To view the oldest and newest recorded timestamps of each data type in the DW: USE OperationsManagerDW select min(DateTime) AS [Oldest Event Date] from Event.vEvent select max(DateTime) AS [Newest Event Date] from Event.vEvent select min(DateTime) AS [Oldest Perf Date]from Perf.vPerfRaw select max(DateTime) AS [Newest Perf Date]from Perf.vPerfRaw select min(DWCreatedDateTime) AS [Oldest Alert Date] from Alert.vAlert select max(DWCreatedDateTime) AS [Newest Alert Date] from Alert.vAlert --Which Tables used the most space USE OperationsManagerDW SELECT so.name, 8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb, Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id) WHERE 'U' = so.type GROUP BY so.name  ORDER BY data_kb DESCIf you look at results of the first two queries (current grooming settings and last grooming time)   Another interesting query is the “Which Tables used the most space” query. You can run this query before and after changing the grooming settings to see if the grooming had any effect. If you want to change the Grooming settings you can use the next queries. N.B. Change to the values you want to have your Grooming settings configured!!

-- From http://ops-mgr.spaces.live.com/blog/cns!3D3B8489FCAA9B51!176.entry -- Alert Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 100 WHERE GroomStoredProcedureName = 'AlertGroom' --Event Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 40 WHERE GroomStoredProcedureName = 'EventGroom' --Performance Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 100 WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationIntervalDurationMinutes = '60' USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 200 WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationIntervalDurationMinutes = '1440' --State Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 40 WHERE GroomStoredProcedureName = 'StateGroom' AND MaxDataAgeDays = 180 USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 100 WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationIntervalDurationMinutes = '60' USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 200 WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationIntervalDurationMinutes = '1440' How can I see if Grooming has worked? You can check if Grooming has worked after changing the Grooming settings by looking at the dwdatarp tool results or by running some SQL queries. Tip: save the results from the dwdatarp tool or SQL queries before and after changing the grooming settings to compare them. If you have used the dwdatarp tool for saving the before and after grooming results you can have a look at the columns Current Size and Current Row Count if they changed after changing the grooming settings.      If you like to use SQL you can run the “Which Tables used the most space” sql query to look if those have changed after changing the grooming settings. It’s also important to look at the current size and free space of the operationsmanagerdw before starting to groom.
Why don’t my database files shrink after grooming? That’s another question people often ask, and that is because the SQL DB files are static – they are manually sized. You can check your autogrow settings for the OperationsManagerDW with the Microsoft SQL Server Management Studio. For the OperationsManager Database Autogrow is default disabled and for the OperationsManagerDW the default setting for Autogrow is enabled. If you look at the Autoshrink setting for the OperationsManagerDW you can see it’s disabled. That’s why the database files won’t shrink after grooming has taken place. Please keep in mind that we don’t support/recommend EVER shrinking a DB file for OpsMgr. It causes fragmentation issues. The only thing that will change (shrink) after grooming is the used space in the database. You can check the used space for a database with the Disk Usage Report in Microsoft SQL Server Management Studio. But, sometimes shrinking the database is the only option left if you don’t have any space left… Disclaimer Please be very careful when changing your grooming settings, you can loose data ;-) Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use Links to other blog posts about Grooming:

Modifying grooming settings for the OpsMgr databases Data Warehouse Data Retention Policy (dwdatarp.exe) Changing the data retention in the OpsMgr datawarehouse

I know there are already quite some other blog posts about OpsMgr Data Warehouse Grooming.  But I was helping a customer with grooming their OpsMgr Data Warehouse Database (OperationsManagerdw) and got some questions. And you may have the same questions but you are afraid to ask ;-)

How can I change the Grooming settings for the OpsMgr Data Warehouse? This cannot be done from within the OpsMgr Console. So what are the options then?

DWdatarp tool Use the Data Warehouse Data Retention Policy (dwdatarp.exe) tool from the MOM team weblog. Tip: if after running the tool you don’t see any results, you may not be dbowner on the OperationsManagerDW database.   You can save the results to a csv, but you need to do some manual stuff in Excel to have a nice formatted overview.   Read the help (dwdatarp /?) for all the options. You can also change the Grooming settings for the OpsMgr Data Warehouse with this tool.

SQL queries You have to use some SQL queries and run those on the operationsmanagerdw database in SQL Server Management Studio.   To view the Current OpsMgr Data Warehouse queries I use the next SQL queries from several sources.

--Current Grooming Settings USE OperationsManagerDW SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes, MaxRowsToGroom FROM StandardDatasetAggregation --Last Grooming Time USE OperationsManagerDW select min(datetime) as MinDate, max(datetime) as MaxDate, datediff(d,min(datetime),max(datetime)) AS NoOfDaysInDataSet from Perf.vPerfHourly --To view the number of days of total data of each type in the DW: USE OperationsManagerDW SELECT DATEDIFF(d, MIN(DWCreatedDateTime), GETDATE()) AS [Current Alert] FROM Alert.vAlert SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Event] FROM Event.vEvent SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Perf Raw] FROM Perf.vPerfRaw SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Perf Hourly] FROM Perf.vPerfHourly SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current Perf Daily] FROM Perf.vPerfDaily SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current State Raw] FROM State.vStateRaw SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current State Hourly] FROM State.vStateHourly SELECT DATEDIFF(d, MIN(DateTime), GETDATE()) AS [Current State Daily] FROM State.vStateDaily --To view the oldest and newest recorded timestamps of each data type in the DW: USE OperationsManagerDW select min(DateTime) AS [Oldest Event Date] from Event.vEvent select max(DateTime) AS [Newest Event Date] from Event.vEvent select min(DateTime) AS [Oldest Perf Date]from Perf.vPerfRaw select max(DateTime) AS [Newest Perf Date]from Perf.vPerfRaw select min(DWCreatedDateTime) AS [Oldest Alert Date] from Alert.vAlert select max(DWCreatedDateTime) AS [Newest Alert Date] from Alert.vAlert --Which Tables used the most space USE OperationsManagerDW SELECT so.name, 8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb, Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id) WHERE 'U' = so.type GROUP BY so.name  ORDER BY data_kb DESCIf you look at results of the first two queries (current grooming settings and last grooming time)   Another interesting query is the “Which Tables used the most space” query. You can run this query before and after changing the grooming settings to see if the grooming had any effect. If you want to change the Grooming settings you can use the next queries. N.B. Change to the values you want to have your Grooming settings configured!!

-- From http://ops-mgr.spaces.live.com/blog/cns!3D3B8489FCAA9B51!176.entry -- Alert Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 100 WHERE GroomStoredProcedureName = 'AlertGroom' --Event Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 40 WHERE GroomStoredProcedureName = 'EventGroom' --Performance Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 100 WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationIntervalDurationMinutes = '60' USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 200 WHERE GroomStoredProcedureName = 'PerformanceGroom' AND AggregationIntervalDurationMinutes = '1440' --State Data: USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 40 WHERE GroomStoredProcedureName = 'StateGroom' AND MaxDataAgeDays = 180 USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 100 WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationIntervalDurationMinutes = '60' USE OperationsManagerDW UPDATE StandardDatasetAggregation SET MaxDataAgeDays = 200 WHERE GroomStoredProcedureName = 'StateGroom' AND AggregationIntervalDurationMinutes = '1440' How can I see if Grooming has worked? You can check if Grooming has worked after changing the Grooming settings by looking at the dwdatarp tool results or by running some SQL queries. Tip: save the results from the dwdatarp tool or SQL queries before and after changing the grooming settings to compare them. If you have used the dwdatarp tool for saving the before and after grooming results you can have a look at the columns Current Size and Current Row Count if they changed after changing the grooming settings.      If you like to use SQL you can run the “Which Tables used the most space” sql query to look if those have changed after changing the grooming settings. It’s also important to look at the current size and free space of the operationsmanagerdw before starting to groom.
Why don’t my database files shrink after grooming? That’s another question people often ask, and that is because the SQL DB files are static – they are manually sized. You can check your autogrow settings for the OperationsManagerDW with the Microsoft SQL Server Management Studio. For the OperationsManager Database Autogrow is default disabled and for the OperationsManagerDW the default setting for Autogrow is enabled. If you look at the Autoshrink setting for the OperationsManagerDW you can see it’s disabled. That’s why the database files won’t shrink after grooming has taken place. Please keep in mind that we don’t support/recommend EVER shrinking a DB file for OpsMgr. It causes fragmentation issues. The only thing that will change (shrink) after grooming is the used space in the database. You can check the used space for a database with the Disk Usage Report in Microsoft SQL Server Management Studio. But, sometimes shrinking the database is the only option left if you don’t have any space left… Disclaimer Please be very careful when changing your grooming settings, you can loose data ;-) Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified in the Terms of Use Links to other blog posts about Grooming:

Modifying grooming settings for the OpsMgr databases Data Warehouse Data Retention Policy (dwdatarp.exe) Changing the data retention in the OpsMgr datawarehouse

Jonathan Almquist started a blog post about Operations Manager 2007 SQL Queries. “I’m starting a new post here, similar to my Command Shell reference.  This will include some useful SQL queries that I happen to need and direct my customers to on a daily basis.  If you don’t see something here, check Kevin Holman’s blog.  He’s already got a library of useful queries posted, in which I will not duplicate here. I will continue to update this table periodically.  If you subscribe to my blog, you will receive each new example as I post it.” I also have some SQL queries I used at customers I want to share. Would be nice if we had some kind of Wiki where we could post all our OpsMgr SQL queries though ;-)

OperationsManager OperationsManagerDW

  Logical Free Megabytes on Windows Server 2000 and higher

% Logical Disk Free Space on Windows Server 2003

List all Windows Computers  

Total Number of Servers per OS  

Source: Simple-Talk Thomas LaRock has written an article about using OpsMgr Reports to Validate (your SQL components) uptime. Check it out. You can also follow Thomas LaRock (@sqlbatman) on Twitter if you want.

I helped a customer creating a OpsMgr Monitor for checking if the there has been a SQL Full or Differential Backup within a specified number of hours. David Scheltens created the SQL query. This is how you could create this kind of monitor in your own environment. Steps:

Create a Timed Script Two State Monitor. Save Monitor in other MP than Default MP. Give the Monitor a Name, like “Custom – SQL Full or Differential Backup Check”. Target the Monitor to SQL Database. Select a suitable Parent Monitor like Availability. Disable Monitor (enable by an Override later). Configure how often this Monitor should run (default is 15 mins, but you could change this depending on the time your SQL backups run) Copy script from here. The script uses three parameters (or arguments) - Param 0: The SQL connection string for the server - Param 1: The Database to use - Param 2: The threshold (in hours) to use Configure the Parameters.    Configure Unhealthy Expression. Property[@Name='NumHours'] Greater Than 1 (threshold when your backup should have been run)
Configure Healthy Expression Property[@Name='NumHours'] Less than or equal to 1 (threshold when your backup should have been run)
Configure Health.
Configure Alerting I used the Reason, BackupType and Number of Hours in the Alert Description.
Enable the Monitor via an Override. Remark: You may need to Use a Run As Account with the right permission for this Monitor. Result:

Some time ago Jonathan Hambrook has written an article about Designing a new Report in SCOM 2007. Although this is great article you need to have some prerequisites ready to get started with Designing your new OpsMgr 2007 Reports. Here are the prerequisites: Visual Studio 2005 or 2008 (Express) SQL Server Business Intelligence Development Studio (BIDS) BIDS is a Microsoft Visual Studio Tool that may be used to develop Integration Services projects, Reporting Server projects, Analysis Server projects and other database projects. BIDS is available in the SQL Server program group after installation. So you need to install Visual Studio (Express) and SQL Server Business Intelligence Development Studio. Installing Visual Studio 2005 or 2008 You can download Visual Studio 2008 Express from here or Visual Studio 2005 Express from here. Installing SQL Server Business Intelligence Development Studio Click on Advanced. Select Business Intelligence Development Studio. After installing Visual Studio and Business Intelligence Development Studio you can open the BIDS via All Programs –> Microsoft SQL Server 2005 –> SQL Server Business Intelligence Development Studio. And after Opening BID you can follow Jonathan Hambrook article to create a Report Model.