Proactive Alerting for Immediate Support

One thing that is critical in administering any system is alerts.  You never want to get a call from an end user to let you know that your system is down.  Not only does it make you look foolish, but now you have to scramble while users are waiting on you.  For critical alerting like “Is your machine running”, you’ll usually need to turn to your System Administrators.  MicroStrategy provides the Listener Utility that will at least monitor the Intelligence Server service and restart it if it dies or the system reboots.  New with 9.0.1, MicroStrategy offered a new (free!) product called Health Center to help monitor a little more of the MicroStrategy internals.

But one thing that always bothered me is when I’d stumble upon a user who was constantly receiving errors and not speaking up.  This usually means they’re having a bad user experience and that will inevitably lead to them not using the product or voicing displeasure with others.  Unfortunately, Health Center does not yet come with the ability to alert you when a user receives an error, but with a simple Enterprise Manager report, we can accomplish this easily on our own.

Step 1: Create Your Report

In your Enterprise Manager project, create the following Freeform SQL Report:

select RT_EM_USER_ID,
RT_EM_USER_NAME,
RT_IS_REP_ID,
RT_IS_REP_NAME,
RT_IS_REP_EXEC_ST_TS,
RT_IS_REP_EXEC_FN_TS,
RT_ERRORMESSAGE,
RT_JOB_DURATION/86400000.0,
RT_RESULTROWS,
RT_SQLPASSES
from RT_IS_JOB_MONITOR
WHERE RT_IS_REP_EXEC_FN_TS > dateadd(mi, -5, GETDATE())
and RT_EM_JOB_STATUS_ID = 4
and RT_IS_PROJ_ID = 2

What this report does is query the Real Time Statistics Tables and look for any jobs that had an Error status within the last 5 minutes.

Step 2: Create a Subscription

Create a new Schedule under Administration -> Configuration Managers -> Schedules and call it something like “Alerts (Every 5 Minutes)“.  Set it to be Time Triggered beginning today.

Choose the options: Daily, Execute all day every 5 minutes, and provide a Start Time / End Time if you wish to only monitor for a specific window.

Once complete, set an Alert subscription on your new Monitoring report using the new Schedule you’ve created.  You’ll only receive an email if an error occurred.

Step 3: Monitor!

Whenever a user receives an error message (such as invalid SQL, invalid Drilling, too many rows, etc), you’ll get a (near) instant email letting you know the user, the report they ran, the time and the error they received.  You can now take immediate action by sending that user a note and saying, “Hey, I saw you received this error.  I think I can help”.

You wouldn’t believe the quality points this will earn you with your users.  They will be very impressed that you are keeping up with the system at such an incredible speed.  Have you ever received an error in an application and then had the Administrator contact you immediate to offer assistance?

The other benefit is that you can spot systemic issues immediately.  If you just moved some new objects to Production and then a user receives some cross join errors, you can be hot on the trail to a fix before anyone even brings it up.  You can then inform the affected user(s) directly that the fix is in place and to try again instead of having to worry about prolonged outages and department wide emails informing everyone of the problem.

You may also like...