Automate your monitoring

How it starts…

When you encounter a system error condition, the expert personnel dispatched to solve it quickly establish an understanding of the circumstances that caused it and a sense of how to recognize the conditions that surround its presence or absence.

Often, the practitioners will develop some sort of SQL query script for inspecting the database. After hours of honing WHERE clauses and JOINs between critical tables, the perfect concoction is formulated, allowing the developers or support to make their case to prove or disprove the presence of the problem.

And that’s where it stops

Everyone breathes a sigh of relief. For a few minutes or hours, the solved problem becomes the subject of momentary conversations between peers.

Hey, did you hear about the problem with the Order system defect?

Yeah, I figured out what the problem was! It was a bug in the code, but I created an UPDATE script to fix it next time.

But we do little after that. The day ends, we shut down our machine, with that nifty little database script nestled comfortably on our hard drives, quickly to be forgotten over a weekend of grass mowing, dinner eating, and other random things. And in the flurry of end-of-sprint activities, we seldom think to include the impromptu aid in our review, spreading awareness to the rest of the team about how to recognize the problem discovered or how to fix it–with the script that’s still on your hard drive!

Step 1: Share the experience

You should include such actions in the sprint review. If you’re supporting a production system, and you’re included in the sprint planning process, your work outputs are as much a part of the work of the team as the new features being developed by the coders. You may do development and support. That’s cool. Just be sure to highlight your solutions when you create them–not later when the memory fades.

Time is of the essence

We quickly lose the enthusiasm of the “hunt”–tracking down an elusive bug or data corruption condition. It fades quickly as other thoughts and activities replace it, redirecting our energy to what’s next, not the great accomplishment we just achieved.

Step 2: Start the ritual–comment, commit, communicate

Important: Take the time to observe this moment with a ritual. Email the team. Share the conditions you discovered that indicate that the problem exists. Better yet, add the script you create (with comments) to source control. Try to make it repeatable and distinguish between working/intermediate queries that don’t affect the overall outcome. When you add your script,

Next Level: Automate Your Knowledge

Inspector Views

In the case of a database system that allows queries to be stored in the database as a view, leverage this capability by creating a permanent database view that exposes or highlights a previously discovered problem.

You should include the following:

1. Comments
   1. A description of the problem
   2. An expectation of whether or not records in the query represent the presence or absence of the proble
   3. Date information and your name so others can ask you questions if you’re still available
   4. A reference to any bug report, user story, or task that describes the initial condition reported
2. The query Consider implementing a “hint” column that labels resulting rows as “good” or “bad” depending on whether the presence of the row is a good thing or a bad thing.

-- *******
-- * Note (JM, 09/28/2019): In Defect #5286, it was discovered that some 
-- * username values were NULL in the Customers table.
-- * 
-- * Note: If this query returns records, 
-- *                                                 
-- *  ___         __        __      __        __    /
-- *   |  |__| | /__`    | /__`    |__)  /\  |  \  / 
-- *   |  |  | | .__/    | .__/    |__) /~~\ |__/ .  
-- *
-- * ASCII Art courtesy of: http://patorjk.com/software/taag/                                            
-- *                                                 
-- *******
CREATE VIEW inspector.Customer_NullUserNames AS
    SELECT
        [Comment] = "Problem found! Null username"
        , c.id
        , c.first_name
        , c.last_name
        , c.created_date
    FROM dbo.Customers c
    WHERE c.user_name IS NULL

Even better: build an automated test!

In the previous example, we’re probably talking about an integration test.

Here’s a simple recipe:

1. Build an inspector query (that can be run ad-hoc).
2. Build an integration test to “inspect” the query results.
3. Decide how to include it in your automation or utility test suite.
4. Include the test to run regularly as part of your processes.
5. Important!: Tell others what you did! If you don’t, your payoff will be greatly diminished, leaving you with only the hope that someone will discover the tool later. That would be sad.

Get the word out! Present your inspector view and automated test during your team’s scheduled review. It’s worth it.

TESTS for the WIN!

About the Author

Jeffrey A. Miller is a Senior Consultant, Trainer, Author, Speaker, and Leader. Jeffrey has over 25 years of experience helping organizations bring value to their mission through software. He has presented a variety of programming, data, and team topics at user groups and tech conferences.

Jeffrey and his wife, Brandy, are adoptive parents and have written a fun children’s book called “Skeeters” with proceeds supporting adoption.