Source: System Center Central  I just found this QuickTrick on System Center Central and I could not believe I didn’t know this trick… If you want to Select All or Unselect all in some of the Report Parameters boxes you can just right-click and there it is. Let’s take a look at the Overrides Report in my Demo environment. Right-Click on the Management Pack Parameter Window. Great QuickTrick IMO. Thank you System Center Central!

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!

Today I got a question about retrieving the GUID for a newly created OpsMgr  Report Operator User Role in OpsMgr 2007 SP1. When I asked why they wanted to know the GUID for this new Report Operator User Role they answered they needed this GUID on the Report Server for configuring permissions to certain reports. There used to be a Securing Reports pdf on the System Center Forum website but I cannot find it anywhere else anymore. So I’m not sure if this describes all the steps to Secure an OpsMgr Report in OpsMgr 2007 SP1 so I though it would be handy to publish the steps here if you are looking for them. Back to the original question about retrieving the GUID for a Report Operator Role. The easiest way is using PowerShell. I used the next command to retrieve the GUID: Get-Userrole | where {$.Name –match “Test Report Operator Role”} or Get-Userrole | format-List Name, ID Here are all the steps to Secure an OpsMgr Report for a newly created Report Operator Role: 1. Create a Report :-) 2. Create new Report User Role using PowerShell So here is a script to create a new Report Operator user role using Command Shell: $mg = (get-item .).ManagementGroup $reportOperator = $mg.GetMonitoringProfiles() | where {$.Name -eq "ReportOperator"} $obj = new-object Microsoft.EnterpriseManagement.Monitoring.Security.MonitoringUserRole $obj.Name = "TestReportOperatorRole" $obj.DisplayName = "Test Report Operator Role" $obj.Description = "Test Report Operator Role" $obj.MonitoringProfile = $reportOperator $mg.InsertMonitoringUserRole($obj) After you execute this script “Test Report Operator Role” appears in UI and you would be able to add users to it using User Role Properties dialog. From: http://blogs.msdn.com/eugenebykov/archive/2007/10/13/creating-new-report-operator-user-role.aspx 3. Go to Report Server (http://servername/reports) 4. Click on Show Details from the Report you want to configure access to. 5. Click on Edit 6. Click on Security 7. Click on New Role Assignment 8. Insert GUID from New Report Operator Role. GUID can be found using the next PS command: get-userrole | where {$_.Name –match “Test Report Operator Role”}

Today I got a question about retrieving the GUID for a newly created OpsMgr  Report Operator User Role in OpsMgr 2007 SP1. When I asked why they wanted to know the GUID for this new Report Operator User Role they answered they needed this GUID on the Report Server for configuring permissions to certain reports. There used to be a Securing Reports pdf on the System Center Forum website but I cannot find it anywhere else anymore. So I’m not sure if this describes all the steps to Secure an OpsMgr Report in OpsMgr 2007 SP1 so I though it would be handy to publish the steps here if you are looking for them. Back to the original question about retrieving the GUID for a Report Operator Role. The easiest way is using PowerShell. I used the next command to retrieve the GUID: Get-Userrole | where {$.Name –match “Test Report Operator Role”} or Get-Userrole | format-List Name, ID Here are all the steps to Secure an OpsMgr Report for a newly created Report Operator Role: 1. Create a Report :-) 2. Create new Report User Role using PowerShell So here is a script to create a new Report Operator user role using Command Shell: $mg = (get-item .).ManagementGroup $reportOperator = $mg.GetMonitoringProfiles() | where {$.Name -eq "ReportOperator"} $obj = new-object Microsoft.EnterpriseManagement.Monitoring.Security.MonitoringUserRole $obj.Name = "TestReportOperatorRole" $obj.DisplayName = "Test Report Operator Role" $obj.Description = "Test Report Operator Role" $obj.MonitoringProfile = $reportOperator $mg.InsertMonitoringUserRole($obj) After you execute this script “Test Report Operator Role” appears in UI and you would be able to add users to it using User Role Properties dialog. From: http://blogs.msdn.com/eugenebykov/archive/2007/10/13/creating-new-report-operator-user-role.aspx 3. Go to Report Server (http://servername/reports) 4. Click on Show Details from the Report you want to configure access to. 5. Click on Edit 6. Click on Security 7. Click on New Role Assignment 8. Insert GUID from New Report Operator Role. GUID can be found using the next PS command: get-userrole | where {$_.Name –match “Test Report Operator Role”}

So you finally created that super cool custom report in OpsMgr and you want to add all the info necessary to run the report correctly. But how do you do that? If you use the MP Authoring Tool you have the option to add a Description. If you open the MP xml file you can see the above Description is added to the DisplayStrings section of the MP. This is the Result: To do more advanced stuff you need to add a KnowledgeArticles section to your XML.   So if you add the next section to the KnowledgeArticles Section: <KnowledgeArticle ElementID="My.Custom.Reporting.Report" Visible="true">           <MamlContent>             <maml:section xmlns:maml="http://schemas.microsoft.com/maml/2004/10">               <maml:title>Summary</maml:title>               <maml:para>                 <maml:ui>How does this report work?</maml:ui>               </maml:para>               <maml:para>This report shows the next items:</maml:para>               <maml:list>                 <maml:listItem>                   <maml:para>Item 1</maml:para>                 </maml:listItem>               </maml:list>               <maml:para />               <maml:para>The report finds data if the objects supplied are of type</maml:para>               <maml:para>Exampe type</maml:para>               <maml:para>Search for objects on blabla</maml:para>               <maml:para />               <maml:para>The report displays for every selected object a separate chart. Data is aggregated to days of a month.</maml:para>               <maml:para>                 <maml:ui>How to use this report?</maml:ui>               </maml:para>               <maml:para>When run from the Reporting space:</maml:para>               <maml:para>The second column in the search results shows of which type the displayed objects are. Make sure you select objects of type blabla.</maml:para>               <maml:para />               <maml:para>When run from the Monitoring space:</maml:para>               <maml:para>Create a state view or performance view by choosing the blabla type.</maml:para>               <maml:para />               <maml:para>                 <maml:ui>What Parameters are offered?</maml:ui>               </maml:para>               <maml:para>Date/Time selection: Allows selecting a relative or fixed date and time range and time zone.</maml:para>               <maml:para>Object: Allows adding objects to run this report for</maml:para>               <maml:para />             </maml:section>           </MamlContent>         </KnowledgeArticle>   This will be the result in the OpsMgr Console Report Description:

So you finally created that super cool custom report in OpsMgr and you want to add all the info necessary to run the report correctly. But how do you do that? If you use the MP Authoring Tool you have the option to add a Description. If you open the MP xml file you can see the above Description is added to the DisplayStrings section of the MP. This is the Result: To do more advanced stuff you need to add a KnowledgeArticles section to your XML.   So if you add the next section to the KnowledgeArticles Section: <KnowledgeArticle ElementID="My.Custom.Reporting.Report" Visible="true">           <MamlContent>             <maml:section xmlns:maml="http://schemas.microsoft.com/maml/2004/10">               <maml:title>Summary</maml:title>               <maml:para>                 <maml:ui>How does this report work?</maml:ui>               </maml:para>               <maml:para>This report shows the next items:</maml:para>               <maml:list>                 <maml:listItem>                   <maml:para>Item 1</maml:para>                 </maml:listItem>               </maml:list>               <maml:para />               <maml:para>The report finds data if the objects supplied are of type</maml:para>               <maml:para>Exampe type</maml:para>               <maml:para>Search for objects on blabla</maml:para>               <maml:para />               <maml:para>The report displays for every selected object a separate chart. Data is aggregated to days of a month.</maml:para>               <maml:para>                 <maml:ui>How to use this report?</maml:ui>               </maml:para>               <maml:para>When run from the Reporting space:</maml:para>               <maml:para>The second column in the search results shows of which type the displayed objects are. Make sure you select objects of type blabla.</maml:para>               <maml:para />               <maml:para>When run from the Monitoring space:</maml:para>               <maml:para>Create a state view or performance view by choosing the blabla type.</maml:para>               <maml:para />               <maml:para>                 <maml:ui>What Parameters are offered?</maml:ui>               </maml:para>               <maml:para>Date/Time selection: Allows selecting a relative or fixed date and time range and time zone.</maml:para>               <maml:para>Object: Allows adding objects to run this report for</maml:para>               <maml:para />             </maml:section>           </MamlContent>         </KnowledgeArticle>   This will be the result in the OpsMgr Console Report Description:

Sorry for not posting any new OpsMgr posts lately, but I’m just too busy right now. Hopefully I’ve some more time in the future, because I’ve quite some things to blog about. But today I was creating a Custom OpsMgr Report in Microsoft Visual Studio for a customer and needed to have some Default Parameters for this Report. This Report has only two parameters Report Year and Report Month, because this Report only needs to show data for a whole month. So if you want some Default Year and Month values for your Report Parameters you need to do this in Visual Studio: Go to Layout Tab and select Report – Report Parameters Select the Parameter you want to create a Default value for (in my case ReportYear) Select Non-queried Default Value and use the next function: =cint(DatePart(“yyyy”,Now())) This will create a Default ReportYear Parameter of ‘2009’ using the NOW() Scalar Function which returns the current system date and time. And by using DatePart you get an integer representing the specified datepart of the specified date. For the ReportMonth Default value you can use the next Function: =cint(DatePart(“m”,Now())-1) which return the previous month.   You could use those Report Year and Month Parameters in your SQL query like this example: Off course in your Report query you would not declare the @ReportYear and @ReportMonth variables but use the (Default) Parameter values ;-) The result of using Default Parameters in a Report is 2009 as ReportYear Parameter and 5 as ReportMonth Parameter (I changed it manually to 6 because in May I did not any data ;-))

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.

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.

After trying to open Reporting from the Ops Console I got the next error:

I checked if Reporting was configured correctly with the Configure Report Server tool.

Checked if SharePoint Integration was enabled. And that wasn’t the case. Tried to open ReportServer in a Browser.

Restarted the SQL Server Reporting Services (MSSQLSERVER) service. But still no luck. Opened Configure Report Server tool again and applied the Default Settings for the Report Server Virtual Directory and Report Manager Virtual Directory.

Re-entered the correct password for the Windows Service Identity and clicked Apply.

Now the Web Service Identity failed.

Changed the Web Service Identity for the Report Server and Report Manager from the DefaultAppPool to ReportServer. After the refresh the ASP.NET Service Account changed automatically from NT Authority\NetworkService to CONTOSO\OM_DRA.

Clicked Apply and did a Refresh and that solved it.