• OpsMgr Custom Reporting Tips & Tricks

    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.

    1. What do I need for creating Custom OpsMgr Reports?
    2. What next? How do I create my first OpsMgr Custom Report?
      • The highlevel steps for creating a Custom OpsMgr Report are:
        1. Create a database query to get the data you need
        2. Use Visual Studio (SQL Server Business Intelligence Development Studio) to create the Report
        3. Deploy the Report to OpsMgr
      • In the Operations Manager 2007 Report Authoring Guide are some examples to get you started. (starts on page 23)
    3. 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)

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

      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.

      image

      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.

          image

    9. Select the fields to be displayed on the different areas of the report, and then  click Next.

        image

    10.Select the style of the layout.
        image

    11. Select the Deployment Location and click on Next.

        image

    12. Give the Report a Name and Review the Report Summary and select Preview Report and click on Finish

    image

    So this is the Result:

    image

    Maybe not completely what you wanted. Let’s change that.


    These are some things we are going to change:

    1. Less columns (EventNumber, Event Publisher Name, Date Time, ComputerName, UserName and Rendered Descripition)
    2. Add a Start and End Data Parameter
    3. Add a select Computer Group Parameter
    4. 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.

    image 

    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

    image

    image

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

    image

    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)

    image

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

    image

    Test the Report and select different Start and End Dates

    image

    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.

    image

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

    Select Add DataSet.

    image

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

    image

    And click on OK to save DataSet

    image

    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:

    image

    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.

    image

    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'


    image

    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.

    image

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

    image

    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.

    image

    image

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

    image

    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.

    image

    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.

    image

    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.

    image

    Let’s create the some JOINs.

    image

    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.

    image

    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:

    image

    After Changing the Computer Group:

    image

    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

    image

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

    image

    image

    Result:

    image

    As you see we need to configure the Group Display Name

    Therefore we need the ComputerGroup DataSet

    image 

    Go to Parameters folder and right click and select Parameter Properties.

    image

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

    image

    Test the Report with the Preview Tab.

    image

    Result:

    image

    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

    image

    Insert the next expression into you text box:

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

    image

    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:

    image

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

    image

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

    image

     

    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!

  • Which permissions are needed for OpsMgr 2007 SP1 Cumulative Hotfix?

    Some days ago I got a question from a customer why their OpsMgr 2007 SP1 Cumulative Hotfix installation was failing.

    As you might know this update provides a cumulative rollup of hotfixes for Microsoft System Center Operations Manager 2007 Service Pack 1 together with the following improvements:

    • Support for Windows 7
    • Support for Windows Server 2008 R2
    • Support for SQL Server 2008 and SQL Reporting Services 2008 Upgrade
    • Additional fixes and improvements
    Note This update applies to System Center Operations Manager 2007 SP1 only.

     

    SYMPTOMS

    They were getting the next error message when running the Run Server Update.


    image

    The first thing I do, is look at the install log file if an installation is failing. So type %temp% in your run box and go to the temp folder and look for the log file.

    While searching the log file I noticed it had probably something to do with the (database) permissions for the account which run the hotfix.

     

    “"MSI (s) (B8!94) [10:35:51:068]: Creating MSIHANDLE (28) of type 790531 for thread 2452

    2009-12-15T10:35:51.0687372+01:00        Warn:        SetRootHealthService: The sql client throws exception Cannot open database "OperationsManager" requested by the login. The login failed.

    Login failed for user 'CONTOSO\Test_UserSP1Hotfix'."

     

    CAUSE

    The account running the hotfix needs db_owner permissons for the OperationsManager database.

     

    RESOLUTION

    In my demo environment (An all-in-one RMS server with all OperationsManager databases on that machine) the account installing the hotfix needed the next permissions.

    Please keep in mind I only tested this in my demo environment. If you have your OpsMgr databases installed on separate servers more permissions may need to be configured.

    OpsMgr Permissions:

    • Member of the Operations Manager Administrators Role

    OS permissions:

    • Member of the Local Admin Group

    Database Permissions:

    • Public for server-wide  security

    clip_image002

     

    • Db_owner for OperationsManager database


    clip_image004

    For the Reporting Server Update no extra database permissions were needed.

     

    MORE INFORMATION

    You can find more info about the OpsMgr 2007 SP1 cumulative hotfix on Kevin Holman’s weblog.

     

    Posts in this blog are provided "AS IS" with no warranties, and confers no rights. Use of examples are subject to the terms specified in the Terms of Use

  • System Center Operations Manager Technet Virtual Labs

    Source: Partner Readiness & Training Newsletter – Dec 2009

     

    System Center Operation Manager TechNet Virtual Labs

    TechNet Virtual Lab: Building Management Packs with the Authoring Console

    TechNet Virtual Lab: Extending Operations Manager 2007 to Monitoring SAP on SQL

    TechNet Virtual Lab: Installing Operations Manager 2007

    TechNet Virtual Lab: Monitoring Windows Server 2008, SQL Server 2008 and Exchange 2007

    TechNet Virtual Lab: System Center Operations Manager 2007 Authoring Management Packs – Introduction (ITPro)

    TechNet Virtual Lab: System Center Operations Manager 2007 Management Pack Authoring – Advanced (ITPro)

    TechNet Virtual Lab: System Center Operations Manager 2007 R2 - Advanced

    TechNet Virtual Lab: System Center Operations Manager 2007 R2 - Introduction

    TechNet Virtual Lab: Understanding the Operations Manager 2007 SDK

     

    Have fun learning about OpsMgr 2007!

  • User Logon Time Report for Operations Manager 2007

    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:

    image

    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:

    1. Create two NT Event Collection Rules for each EventID (528 and 865) in OpsMgr.
    2. Create an SQL Query which calculates the Time difference between the two EventID’s collected by the Event Collection Rules for each user.
    3. 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]

    image image image

     

    image

    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 ;-)

    image

    image

    Disable Rule we enable the rules later for the servers we want to Collection Rules to be running on.

    image

    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:

    image 

    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:

    image

    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:

    image

    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:

    image

    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:

    image

    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.

    image

    Create a New Project and select Report Server Project Wizard and give your project a name.

    image

    Select Next in the Welcome to the Report Wizard Screen

    image

    Create a Data Source

    image

    Click Edit button to enter the SQL Server information.

    image

    And Test Connection if you want.

    Click Next in the Report Wizard Screen.

    image 

    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.

    image

    Select your Report Type and click on Next

    image

    Select how you want to group your report. (I just kept the default settings)

    image

    Select the Table Style of the Report and click on Next

    image

    Enter Report server and Deployment folder info and click on Next.

    image

    Give your Report a name and select Preview Report and click on Finish

    image

    Change Report Parameters from Text to Date/Time Date Type.

    image

    image

    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.

    image

    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.

    image

    So this could be the end result. And you can pimp it even more if you want…

     

    Have fun creating Custom OpsMgr Reports!

  • PowerShell: State Changes for a specified Monitor

    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:

    image

    Have fun with OpsMgr and PowerShell!

  • Have these servers an OpsMgr agent installed?

    Today I got a question from a customer if I would check if a number of servers had an OpsMgr agent installed?

    Because this was a large list and the number of servers being monitored by OpsMgr was also a very large list I didn’t wanted to to copy and paste all the server names for the text file and check them in the OpsMgr Console.

    So I created a PowerShell script to check if an OpsMgr Agent was installed for each of the servers in the file list.

    The first script I created was not very fast because it did a get-agent for each of the servers in the list and that takes quite some time in a large environment ;-) So I created a new one that’s much faster.

    Let’s start with the slow script:

    First you need to put all the servers you want to check in a file, like this:

    servername1
    servername2
    servername3
    servername4
    servername5
    servername6
    servername7
    servername8
    servername9
    servername10
    servername11
    dc8


    Save this list in a text file like d:\temp\servers.txt

    Now run the next script in the OpsMgr Command shell:

    $servers = get-content d:\temp\servers.txt

    foreach ($server in $servers) {get-agent | where {$_.ComputerName -eq $server} | select Name}

    As you can see it takes almost 12 seconds to do this the slow way.

    image

    And now the fast way.

    $Servers = get-content c:\temp\servers.txt
    $Agents = get-agent | select ComputerName
    Foreach ($agent in $Agents) {if ($servers –contains $agent.ComputerName) {$agent | select ComputerName}}

    image

    So the next time someone is asking you if an OpsMgr agent is installed on their servers you now it in seconds.

    Have fun using PowerShell!

  • Updated version for Schedule Maintenance Mode Management Pack Guide on OpsManJam

    This management pack provides an automated method to schedule maintenance mode for a given set of Windows computers that are in a pre-defined set of groups and the associated rules to target against the groups.

     

    Comments:

    Updates for this version:

    1. Created two additional alert rules that notify the admin/operator if the script is unable to connect to the RMS or resolve the Group Name provided to the script.

    2. Created a Troubleshooting section in the deployment guide.

    3. Provided a statement regarding using this demo MP and Windows Cluster services

    4. Provided additional guidance regarding the parameters that can be overridden.

    Contributor: Matt Goedtel

    Go and download at OpsManJam