Team LiB   Previous Section   Next Section

6.4 Reporting Log Events

6.4.1 Problem

You need a human-readable report of the activity on the Stock table. You want the report to show activity in the correct order, and you want to see only one row on the report for each update operation. A row-level log table, such as the StockLog table from the previous recipe, records events in the order in which they occur. You can easily generate reports of these events.

6.4.2 Solution

Based on the Stock table, you can use the following query to generate a human readable report of the audit log in the StockLog table: 

SELECT 
   MAX(UserID) AS UserID,
   MAX(
      CASE 
        WHEN Type='D' THEN 'Deleted' 
        WHEN Type='I' THEN 'Inserted'
        ELSE 'Updated'  END) AS Type,
   MAX(ProductID) AS ProductID,
   MAX(CASE 
         WHEN Type!='N' THEN Qty 
         ELSE null END) AS Qty1,
   MAX(CASE 
         WHEN Type='N' THEN Qty 
         ELSE null END) AS Qty2,
   MAX(EventTime) EventTime
FROM StockLog 
GROUP BY EventId
ORDER BY max(LogId)

When you execute this query, you may receive the following message: "Warning: Null value eliminated from aggregate." This is nothing to worry about. The message is informing you that some of the values summarized using MAX were NULL. This is, in fact, the intended use, since we are explicitly setting the qty values in some rows to NULL to ignore them.

As an example of how this query functions, let's assume that the following SQL statements had been executed against the stock table. Each INSERT and DELETE statement would have generated one log entry, while the UPDATE statement would have generated two. 

INSERT INTO Stock(ProductID,Qty) 
      VALUES('Banana',10)
INSERT INTO Stock(ProductID,Qty) 
      VALUES('Apple',20)
INSERT INTO Stock(ProductID,Qty) 
      VALUES('Orange',30)
UPDATE STOCK SET Qty=2 
      WHERE ProductID='Banana'
DELETE FROM Stock 
      WHERE ProductID='Banana'

Given the operations shown here, the query in this recipe will produce the following results: 

UserID  Type     ProductID  Qty1  Qty2  EventTime
------- -------- ---------- ----- ----- ---------------------------
Max     Inserted Banana     10    NULL  2000-06-27 22:51:30.017
Justin  Inserted Apple      20    NULL  2000-06-27 22:51:30.023
Joshua  Inserted Orange     30    NULL  2000-06-27 22:51:30.027
Ryan    Updated  Banana     10    2     2000-06-27 22:51:30.027
Gerard  Deleted  Banana     2     NULL  2000-06-27 22:51:30.030

This report is more readable than what you would get were you simply to query the raw data from the audit-log table, and it can be easily understood by users. Of course, the query shown in this recipe can easily be adapted to narrow down the results to just the data needed to answer specific questions. For example, you might want to report on the activity of a single user, or you might want to report on a single product.

6.4.3 Discussion

There are four key things to notice about the query in this recipe:

  • The first CASE statement translates the type codes.

  • CASE statements divide quantity values into two columns.

  • The grouping functions combine updates into one row.

  • The ORDER BY clause sorts the activity by LogId.

The CASE statements in this query translate the one-letter type codes to words that will have meaning to the person reading the report. Thus, D is translated to Deleted, I to Inserted, and so forth.

You'll notice that this query returns two quantity columns. This is done to report both the old and new quantities for an update operation. The first quantity column, named Qty1, will contain the quantities for any operation with a type code not equal to N. This includes insert and delete operations. It also includes the original quantity (type code E) from an update operation. The following CASE statement, embedded in the query, sets the value for this column: 

MAX(CASE WHEN Type!='N' THEN Qty 
      ELSE null END) AS qty1

If the type is not equal to N, the qty1 column returns the quantity; otherwise, it returns a NULL value. A similar CASE statement is used to place the quantity value into the Qty2 column whenever it is not placed in the Qty1 column. In other words, the second quantity column takes on the quantity from type N log records. These represent the updated values from update operations.

The effects of the CASE statements are fairly obvious. The use of grouping functions to convert two update-related log records into one output record is not so obvious, and it deserves some extra explanation. The following example illustrates the output that this query would generate were the grouping functions not used. The two records in the example are types N and E, which means they were generated as the result of an UPDATE statement. 

UserID  Type     ProductID  Qty1  Qty2  EventTime
------- -------- ---------- ----- ----- ---------------------------
Ryan    Updated  Banana     10    NULL  2000-06-27 22:51:30.027
Ryan    Updated  Banana     NULL  2     2000-06-27 22:51:30.027

Notice that the CASE statement has translated the type codes N and E both to the word Updated. The result is that all columns in both rows are identical except for the Qty1 and Qty2 columns. You can combine these two rows by using the SQL GROUP BY clause. Group the two rows based on the EventID, and simply take the maximum value of each column. In the case of the UserID, Type, and ProductID columns, both vales are the same, so applying the MAX function returns just one occurrence of each value. In the case of Qty1 and Qty2, applying the MAX function returns the numbers, eliminating the NULLs. That's because numbers are always higher than NULLs. The resulting single row would be: 

UserID  Type     ProductID  Qty1  Qty2  EventTime
------- -------- ---------- ----- ----- ---------------------------
Ryan    Updated  Banana     10    2     2000-06-27 22:51:30.027

The use of grouping in this particular example to combine the two log entries from an update operation into one row on the report depends on two things. Each operation must have a unique EventId, and update operations must not have changed any values other than the quantity for a product. As long as each insert and delete operation has a unique EventId, the log entries for those operations will never be grouped with any others. Similarly, as long as each update record only represents a change in quantity, the other columns in the log entries will be identical, and the log entries will be successfully combined into one record for the report.

The query used in this section to report on the log is designed under the assumption that updates do not change the primary key for a row — in this case, the ProductID. If changing the primary key is a possibility, you will need to treat the primary-key columns in the same manner as the Qty column was treated.

    Team LiB   Previous Section   Next Section