How to get more out of your application monitoring by utilizing queries

Version 1

    So a few years ago I sat down with an application team at the company worked for.  I was doing my typical monitoring pitch.  How do you watch your application?  If we were to build monitors to monitor your systems what should we look for.  One of the answers surprised me.

     

    Every morning I sit down and run about 10 different queries against our database to check on various error conditions.

     

    I had never heard that one before.   I said to the admin.    What if I could have SolarWinds run those queries for you?  Also not only run them once a day but all throughout the day.  I could even fire off immediate notification if they found fault.

     

    SAM has 3 different components that can be used for this operation.  SQL,Oracle, ODBC user experience monitors.  Now since they are called User Experience Monitors it is my belief that most people probably use them simply to check if a database is responding or measure the response time.  However these components also can evaluate a numeric response.

     

    Now don't get me wrong I certainly didn't have full understanding of this application owner's database.  The app owner wrote the query and I simply told him how to craft it.  These component monitors require a numeric response.  The easiest is to have the query simply return only one row and one column.  (You can have it return two columns and put a message in the second column but SAM won't evaluate the message)

     

    Here is an example.  Now in this case I'm simply running a query against my Orion Database

    2015-08-06_0736.png

     

    What is required for such a monitor.   1.  A user with permission to execute the query.  2.  A query to run.  3.  The database you are running it against (specified in the Initial Catalog field in the component)   4.  The instance also needs to be specified if one exists.

    In my case this is a simple Select Count (*) From Nodes.Data   This will simply return a numeric value telling me how many nodes I have in SolarWinds.  While that might be cool to track and trend over time what value might it have?

     

    How about this.   What if I was talking about a CRM database?   What if my DB owner tracked errors with a specific code?  Select Count (*) From Errors Where ErrorID like '%12345%'  I could then set a Statistic Threshold value to have it alert me.  Now understand I'm using very simple queries here for examples.  I am not by any means a DBA and there is a good chance if you are reading this post then you are a monitoring admin as well.   I just wanted to expose to you the power of what these 3 simple looking components can actually do

     

    Here is another example.  In this case I have 3 different SolarWinds instances in a lab.  So I'm running a simple query again to track how many events fire in an hour.  Using a multiple object chart in SAM I then combined those monitors into one graph so I can see which instance is busier. 

    2015-08-06_0747.png

    Like I said I'm running these against the SolarWinds DB (as it is one I have access and knowledge about) but this could be almost anything.  That one simple discussion led me to having several hundred of these running in our environment for a myriad of different applications. I even was pulling back data that had nothing to do with IT.  A query could be ran to track how many widgets were produced on a given day to track and trend output.  Or maybe you want to track and trend how many users are registering for your company website. 

     

    The point of all this has been that there is some great power within these components and show you how you can leverage them. So I encourage you to look at this as an option to provide some deep insight into how their applications might be performing.

     

    Thanks