Command Shell Examples
Useful SQL Queries
Is alert grooming working? - Jonathan Almquist on Operations Manager - Site Home - TechNet Blogs

Is alert grooming working?

Is alert grooming working?

  • Comments 6
  • Likes

Evaluate whether alert grooming is working.  No result is good.

$Threshold=(Get-Date).AddDays(-(get-defaultsetting)[42].Value-1).ToUniversalTime().Date.AddMinutes(30);Get-Alert | Where {$_.TimeResolved -and $_.TimeResolved -lt $Threshold} | Measure-Object

main menu

See full post here.

I do not moderate this blog anymore. If you have a question regarding this post, send me a message.

Comments
  • I ran this query against my Opsmgr database and got 38.  Any tips on troubleshooting this?

  • First, I want to say that I just realized I was getting local time.  I just changed the command to get GMT, which will be more accurate depending on your time zone and when you actually run the query.  Try running the updated query, and see if that returns the same result.

    If same result... 

    Alert Grooming in the database is a fairly simple process.  It simply selects alerts that have a resolution state = 255 and TimeResolved <> NULL.  Then calculates, if TimeResolved + Resolved Alert retention days = Today...groom alert.  So, if there is a problem I would first check the InternalJobHistory.

    SELECT * FROM InternalJobHistory Order By InternalJobHistoryId DESC

    If you see any jobs with a status code <> 1, then the job isn't starting or finishing as expected.  You could run the job manually, to see if it succeeds.  Or, check again tomorrow and see if it ran successfully.  If either results in a status code <> 1, then you'll need to do some further digging.

  • Thanks for the update.  The new query returns no data.  Not even a 0.  I assume this is good!

    I also checked that Internaljob status and all have a code of 1.

    So it appears that all is healthly.  Thanks again.

  • That's right, Nathan.  No result from the query is a good thing.  That means no alerts were "stuck" after grooming finished.  I suppose I could write some output if the command returns no data, but I'm only giving core examples here.

    The reason why this needs to calculate based on GMT is because that's how the Date-Time stamp is stored in the database.  So, if you are located in GMT-6:00, for example, the query would return today's alert count if you run the command afte 7:00PM local time.  This would result in a false positive in the command results.

    Now I've got to go back and fix all my other commands that use an alert field containing a Date-Time stamp in a calulation.  Arghhh...

  • I tried running the SP manually and got followin in return:

    USE [OperationsManager_Dev]

    GO

    DECLARE @return_value int

    EXEC @return_value = [dbo].[p_AlertGrooming]

    SELECT 'Return Value' = @return_value

    GO

    Looks like it didn't run right?

    Also ran the (SELECT * FROM InternalJobHistory Order By InternalJobHistoryId DESC) and the status code shown (1) for all internal job history IDs.

    Any idea what's going on here? is (p_AlertGrooming) the only SP that needs to run to groom OpsMgr_DW database or are there other SPs? Can you provide a list of all the jobs in SCOM R2?

    Thanks

  • @MAkram,

    StatusCode=1 indicates success.  Data Warehouse grooming is performed in the DW, not the OperationsManager database.  A completely separate set of grooming procedures.  P_ParitioningAndGrooming is the SP that controls other grooming and partitioning SP's in the OperationsManager database.  If you look at that SP, you'll see the other SP's that are called.

    -Jonathan