Archiving SCOM Console Task Status history to the data warehouse

Only our server operations team and a limited number of production control staff are allowed access to our production Windows Server consoles. As a result, operations must be involved in the security update process for certain legacy applications that require special handling. In some cases, data is corrupted if services and databases are not stopped prior to Windows shutdown. I documented that process here: http://www.systemcentercentral.com/patching-windows-servers-with-configmgr-2012/

To free up our operations team from this responsibility, I have been granting SCOM console access directly to the application support teams, limiting their access to only those servers that run their services. They now have the ability to not only stop and restart their services, but also reboot their servers and put them into maintenance mode. This has been working out very well, except for the fact that Task Status history in Operations Manager is only available in the console. Because objects are groomed after a 7 day period, we do not have the ability to go back and audit tasks run by our application teams after that period. This can be problematic if there is a change that needs to be audited after the standard seven day grooming period of the Operations Manager database.

I posed this issue to the Microsoft Operations Manager product team and all the MVP’s during the Cloud and Datacenter Management Roundtable Birds of a Feather session at MMS 2013 and we had a very lively discussion regarding Task Status. I spoke with Richard Benwell, who runs Squared Up Ltd again the next day regarding the issue and he promised to get back with me with some options he had in mind to resolve this issue.

Sure enough, Richard got back with me with several options this week, so I wanted to close the loop on the final solution that I am currently implementing.

Richard presented several options:

  1. Extend the grooming period for the Operations Manager database. Due to possible performance issues with keeping an extended grooming period, I ruled this option out.
  2. Transfer the data to the data warehouse via the Get-SCOMTaskResult powershell cmdlet.
  3. Use the Get-SCOMTaskResult powershell cmdlet to dump the data to another database.

For consistency with all my other Operations Manager reporting and to keep the solution as simple as possible, I went with option 2

This solution only requires one powershell script, one Timed Commands rule and one event collection rule. The Timed Command rule runs every night at 1AM and the powershell script executes several cmdlets to pull all Tasks run in the console for the previous day and loops them all into the Operations Manager event log.

Timed Commands Rule

  • CreateOMTaskStatusEvents.ps1
Import-Module OperationsManager$api=New-Object -comObject MOM.ScriptAPI

 $tasks = Get-SCOMTaskResult | Where-Object {($_.TimeScheduled).ToString(“yyyy-MM-dd”) -like (Get-Date).AddDays(-1).ToString(“yyyy-MM-dd”)}

 foreach ($task in $tasks){

                If ($task.Status -eq “Succeeded”){

                                $detail = (Get-SCOMTask -Id $task.TaskId)

                                If ($detail.Category -ne “System” -and $detail.DisplayName -ne “Reserved”){

                                                $agent = Get-SCOMMonitoringObject -id $task.LocationId

                                                $evtdesc = $detail.DisplayName + ” Submitted by: ” + $task.SubmittedBy + ” on agent: ” + $agent.DisplayName + ” at ” +  $task.TimeStarted + ” with status: ” + $task.Status

                                                $api.logscriptevent(“SCOM Console Task”,100,0, $evtdesc)                                }

                }             

}

  • If you do not already have one, create a folder on the data drive of one of your Operations Manager Management servers. For me, it is E:\Scripts, copy the powershell script to this folder.
  • Open the SCOM Console
  • Navigate to the Authoring pane, Management Pack Objects, then Rules.
  • In the Task bar, select Create a Rule
  • Select Timed Commands and Execute a Command as the Rule Type
  • Create a new Management Pack
  • Rule name: Create OM Task Status Events
  • Rule Category: Custom
  • Rule target: Windows Computer
  • Rule is enabled: UNCHECKED
  • Configure your schedule
    • Based on fixed simple recurring schedule
    • Period: 24 Hours
    • Synchronize at: CHECKED, 01:00 AM
  • Full path to file: %SystemRoot%\System32\WindowsPowerShell\v1.0\powershell.exe
  • Parameters: E:\Scripts\CreateOMTaskStatusEvents.ps1
  • Working directory: %SystemRoot%\System32\WindowsPowerShell\v1.0
  • Timeout (in seconds): 300
  • Click on Create
  • Navigate to the Authoring pane and select Groups
  • From Tasks, select Create a New Group
  • Group Name: SCOM Task Event Collection Server
  • Put it in the same destination management pack as the rule you previously created.
  • Explicit group membership: I selected the Microsoft.Windows.Computer object of the management server that the script will run from.
  • Navigate to Authoring pane, Management Pack Objects, Rules.
  • Look for OM Task
  • Right-click on the Create OM Task Status Events rule, select Overrides, Override the Rule, For a group
  • Search for your override group
  • Check the override option for Parameter Name Enabled, and change the Override Value to True

Create override group to enable rules

  • Navigate to the Authoring pane and select Groups
  • From Tasks, select Create a New Group
  • Group Name: SCOM Task Event Collection Server
  • Put it in the same destination management pack as the rule you previously created.
  • Explicit group membership: I selected the Microsoft.Windows.Computer object of the management server that the script will run from.

Override to enable Create OM Task Status Events rule

  • Navigate to Authoring pane, Management Pack Objects, Rules.
  • Look for OM Task
  • Right-click on the Create OM Task Status Events rule, select Overrides, Override the Rule, For a group
  • Search for your override group
  • Check the override option for Parameter Name Enabled, and change the Override Value to True

Collect OM Task Status Events rule

This is the rule that will collect the events created by the Create OM Task Status Events rule and put them into the data warehouse.

  • Navigate to the Authoring pane, Management Pack Objects, Rules.
  • From Tasks, select Create a Rule
  • Rule Type: Collection Rules, Event Based, NT Event Log
  • Choose the same management pack you created earlier for your Create OM Task Events rule.
  • Rule name: Collect OM Task Status Events
  • Rule Category: Event Collection
  • Rule target: Windows Computer
  • Rule is enabled: UNCHECKED
  • Log name: Operations Manager
  • Build Event Expression filters:
    • Event ID Equals: 100
    • Event Source Equals: Health Service Script
    • Parameter 1 Equals: SCOM Console Task
  • Click on Create
  • Navigate to Authoring pane, Management Pack Objects, Rules.
  • Look for OM Task
  • Right-click on the Collect OM Task Status Events rule, select Overrides, Override the Rule, For a group
  • Search for your override group
  • Check the override option for Parameter Name Enabled, and change the Override Value to True

Override to enable Collect OM Task Status Events rule

  • Navigate to Authoring pane, Management Pack Objects, Rules.
  • Look for OM Task
  • Right-click on the Collect OM Task Status Events rule, select Overrides, Override the Rule, For a group
  • Search for your override group
  • Check the override option for Parameter Name Enabled, and change the Override Value to True

Verifying Tasks where entered into data warehouse.

I can run the following query to confirm the events are now in the data warehouse.

 SELECT             

      DATEADD(“HH”,DATEDIFF(HH,GETUTCDATE(),GETDATE()),vEvent.DateTime)as EventTimeDate,

      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 LEFTOUTERJOIN

      vEventUserName ON vEvent.UserNameRowId =  

      vEventUserName.EventUserNameRowId LEFTOUTERJOIN

      vEventCategory ON vEvent.EventCategoryRowId =  

      vEventCategory.EventCategoryRowId LEFTOUTERJOIN

      vEventPublisher ON vEvent.EventPublisherRowId =

      vEventPublisher.EventPublisherRowId LEFTOUTERJOIN

      vEventLoggingComputer ON vEvent.LoggingComputerRowId =

      vEventLoggingComputer.EventLoggingComputerRowId LEFTOUTERJOIN

      vEventLevel ON vEvent.EventLevelId = vEventLevel.EventLevelId LEFTOUTERJOIN

      vEventChannel ON vEvent.EventChannelRowId =

      vEventChannel.EventChannelRowId LEFTOUTERJOIN

      Event.vEventDetail ON vEvent.EventOriginId = vEventDetail.EventOriginId

WHERE 1=—vEventLevel.EventLevelTitle = ‘Error’

      AND vEvent.EventDisplayNumber =’100′

      AND vEvent.DateTime>DATEADD(“D”,-1,GETDATE())

      and vEventPublisher.EventPublisherName =’Health Service Script’

ORDERBYDateTimeDESC

 Thanks for everyone who contributed to making this solution possible!

5 thoughts on “Archiving SCOM Console Task Status history to the data warehouse

  1. Profile photo of JohnJohn

    Hi Joe,

    Can you give me a hand with this?  I really like the idea but after following your instructions I don’t seem to get any results.  I’m trying to verify tasks have been entered into the warehouse but the query you provided doesn’t work.  I don’t see a table in the SCOM DW database called “Event”.  I appreciate the help.

  2. Profile photo of JohnJohn

    Hi Joe,

    Thanks for the quick reply.  Sorry, I meant that I don’t see a view called vEvent, but I don’t think that is really my issue.  I am not a SQL guru so please bear with me.   I had to clean up some syntax errors to get the query to run. I pasted in the query below.  It runs successfully but I don’t see any data in the columns.

    SELECT
    DATEADD(“HH”,DATEDIFF(HH,GETUTCDATE(),GETDATE()),vEvent.DateTime) as EventTimeDate,

    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 1=1

    AND vEventLevel.EventLevelTitle = ‘Error’

    AND vEvent.EventDisplayNumber =’100′

    AND vEvent.DateTime>DATEADD(“D”,-1,GETDATE())

    and vEventPublisher.EventPublisherName =’Health Service Script’

  3. Pingback: OpsMgr Self Maintenance Management Pack 2.5.0.0 | Tao Yang's System Center Blog

Leave a Reply