The Static Snapshot Column Variance triggers is very similar to the Static Snapshot Variance trigger, except specific columns can be configured for monitoring (vs. all).


This trigger works by storing a snapshot of data and then comparing current results with the snapshot.  If differences are found, results will be shown to the user and a notifications will be sent identifying the differences.




Options:

  • Min Row Differences
    • Minimum number of rows allowed to return from the query
  • Max Row Differences
    • Maximum number of rows allowed to return from the query
  • Send Notification Once
    • Monitor will only trigger and send a notification one time.  It will send a notification again unless it returns to a passing state and then triggers again.  This reduces the number of notifications that are sent by QuickReportz if for example a monitor is scheduled to run frequently and notifications are not wanted to be sent on every failure.
  • Compare Key
    • The compare key is used by QuickReportz to properly match the row in the snapshot to the row in the current execution.  While this key is optional, it is recommended to provide this value otherwise it is necessary for the exact order and count of rows to remain static between the snapshot and current execution.
  • Use Rolling Snapshots
    • A new snapshot will be taken automatically when the monitor triggers.  This will cause the monitor to automatically return itself to a passing state after a trigger.  This is useful when you want to monitor data in to the database and report on changes, but not necessarily consider it as "something went wrong", just report on the change and consider that the new norm.
  • Take Snapshot on Save
    • Select this option to have QuickReportz take a current snapshot at the same time the monitor is saved, by clicking the "Save" button at the bottom of the page.  NOTE: snapshots can also be taking on the monitor view page by clicking "Take Snapshot"
  • Snapshot
    • This is represents the active snapshot assigned to the monitor.  You can view the raw data in the snapshot by clicking on the date.



Command Text

Static Snapshot Column Variance triggers requires SQL command text and column configurations.


For example, the following query will return all rows from the books table.


SELECT BookId, Name, Price FROM examples.Books



Column Definitions

Next a threshold column is defined for the Price column.  



The min and max range values specify the allowable percent of increase/decrease of the current value compared to the snapshot.  For example, if the snapshot price of a book was $10, and a range of -50% and +50% has been defined for the Price column, the monitor will trigger, and a notification would be sent if the current book price is below $5 or above $15.  This is different than the Column Value"trigger type which can only compare row values against a preconfigured set range (not based on a snapshot)


To monitor a string value (vs. numeric ranges) for changes, or for exact matching just leave the MIN/MAX values at 0 and any change from the snapshot will cause the monitor to trigger.


Triggered Results

QuickReportz identifies within the UI what differences were found by adding a dynamic "QuickReports_MonitorMessage" column  with messages describing the differences found for the corresponding row as well as snapshot value vs. current value columns to assist in interpreting the results. 


 An example output is shown below for a monitor with columns defined to watch for changes in the name of books and any price change +-50% compared to the snapshot.