A few years ago I had a requirement to allow both developers and customers to see rows from a table used to store error information while making sure that any PII, PCI or similar sensitive data was not displayed. The tolerance for “oops” was low! This article presents one way to solve the challenge by using a white list based on pattern matching. It also builds on that approach to make it possible to group on errors that would be considered distinct by SQL. I’m using errors as the focus, but you could apply the concept to any type of logging table as well.

First, We Need Errors

I’ve made up a few errors, so we have something to work with. We can see at a glance that there are some names in the messages (PII), and we do not want to expose table names to customers.

The Error Table

Here’s the table that contains our sample rows. It doesn’t contain the one thing that would make our life really easy: an error number that we could use to join to a table containing a safe description of the error. So, our example here is a little harder to work with, but possibly more useful too.

Making The Rules

We’ll add a table, named ErrorRules. It is worth noting is that I’ve set the max length of MatchRule to 900 instead of the 1000 we used in the message column in Errors. That’s because we hit the index limitation of 900 bytes. It’s never been a problem for me, and I just use a wild card if I have a really long error message.

Each MatchRule can be a literal or a wild card string. For each rule we will have three (or four, depending how you count) possible outcomes:

• Return the entire original message (from the Errors table)
• Return the matching ResultRule (from the ErrorRules table) (and it could be different message text entirely)
• If no rules are matched, return something safe indicating no rules matched

The suppress flag is so that we can mark an error to be ignored.  Maybe it’s not a real error, something we won’t fix, or a temporary problem that is creating lots of noise. You can use the value to actually suppress the error, or you might choose to let it pass through marked as suppressed and let the user filter it out. The latter at least gives you the chance to detect that the volume of an error has changed.

Here's one rule to get us started. I'll show the final list later in the article.

Get The Best Match

Our approach is going to be to treat all MessageRules as wildcards, which works fine if there is no wildcard of course. We will return the longest matching ResultRule if there is more than one. It’s a simple rule, but for our purposes a longer matching string usually means a more precise match. For example, imagine we had these two rules:

• File%
• File ‘%’ not found

Looking at our examples above both rules would match 3 rows and we would return the “File ‘%’ not found” as the “best” match. Here is the simple table valued function that will do the work:

CREATE FUNCTION [dbo].[GetLongestRuleMatch]
( 
@ErrorMessage varchar(1000)
)
RETURNS TABLE 
AS
RETURN 
(
  SELECT Top 1 MessagePattern, RuleToApply, Suppress from dbo.errorRules
  where
    @ErrorMessage like MessagePattern
order by len(RuleToApply) desc

Note: This is how I wrote it way back when. You could take a different approach, the key here is that we're using the longest messagepattern.

Applying The Rules

Now we can build the last piece, the query that combines errors and rules.  This is the base query, but you could do further filtering here, and I’ll show you an example of that later on. I’m removing the carriage return/line feed pairs if they exist. Depending on your errors, you might need to do this or more. You could also add to that expression other columns, so that you can apply your rules across columns without complicated logic. In my example, these would be ApplicationName or HostName, but in your table you might have a request URL or endpoint column, etc.

The case statement for the three outcomes are implemented as four lines. In a lot of cases we will want to show the original message because it’s entirely safe (say something like “File not found”) and using the #ShowMessage# value just saves us some typing and the possibility of a typo. But maybe instead the error message is “File Not Fund”, so we can clean up our errors (and our ability to aggregate) by making the ResultRule be “File Not Found”.

WITH ErrorList AS
  (SELECT ErrorLogId,
           DateLogged,
          Message,
          ApplicationName,
          Hostname,
          replace(trim(Message), char(13) + char(10), '') AS ErrorMessageForMatching
   FROM Errors E)
SELECT EL.ErrorLogID, EL.DateLogged, EL.Message, EL.ApplicationName, EL.Hostname, RM.MatchRule, RM.ResultRule, isnull(RM.Suppress, 0) as Suppress,
       CASE
           WHEN RM.ResultRule = '#Pattern#' THEN RM.MatchRule
           WHEN RM.ResultRule = '#ShowMessage#' THEN Message
           WHEN RM.ResultRule is not null then RM.ResultRule  --something else we want to force as the message
       ELSE '<-No Matching Rule for Message->'
       END AS SafeMessage
FROM ErrorList EL OUTER apply GetLongestRuleMatch(EL.ErrorMessageForMatching) RM

Example

Here’s the first run with just one rule in place. This shows how we’re handling messages with no rules.

Looking at the ‘no match’ ones, we can see we have a little work to do. I’m going to insert rules  into the ErrorRules table to cover these. Here's what the final list looks like:

A Safe View

Here’s one way we could show the masked errors to our users. In practice we would probably do a little more filtering to only show errors per user or customer (those columns aren’t present in my example table). I am filtering out the suppressed errors.

create view vErrorLogMasked as
WITH ErrorList AS
  (SELECT ErrorLogId,
      DateLogged,
          Message,
          ApplicationName,
          Hostname,
          replace(trim(Message), char(13) + char(10), '') AS ErrorMessageForMatching
   FROM Errors E)
SELECT EL.ErrorLogID, EL.DateLogged, RM.ResultRule as Message, EL.ApplicationName, EL.Hostname,
       CASE
           WHEN RM.ResultRule = '#Pattern#' THEN RM.MatchRule
           WHEN RM.ResultRule = '#ShowMessage#' THEN Message
           WHEN RM.ResultRule is not null then RM.ResultRule  --something else we want to force as the message
       ELSE '<-No Matching Rule for Message->'
       END AS SafeMessage
FROM ErrorList EL OUTER apply GetLongestRuleMatch(EL.ErrorMessageForMatching) RM
where
 isnull(Suppress, 0) = 0

Using this view, here are the results:

Summary

It’s a simple approach that has worked well because it’s easy to understand and maintain while providing a high level of confidence that we weren’t displaying any sensitive data. It does require you to add rules for new errors but I’ve found that to a positive because it leads to asking “why did this new error start happening?”.