Rock Watchdog

What

Trigger email and text alerts and optionally a workflow when a persisted dataview result count exceeds a specific threshold. Note some use cases for this recipe might be better served by making use of the v18 automations feature.

Why

There are times when an admin or other staff need to respond to specific data driven events. 

Examples

1. As an admin I want to be alerted when the number of exceptions in last 30 minutes exceeds a specific threshold
2. As an admin I want to be notified when there any check-in related dataviews found without Persistence disabled

 What

How?

1. Create a new dataview category called "Watchdog" or something similar. Note the id of the created category. In my system the id is 602.

2. Add three new dataview entity attributes. NotificationThreshold of type integer, NotificationPerson of type Person, and Threshold Response Workflow of type Workflow Type. Each attribute should use a Qualifier Field of type CategoryId and a Qualifer Value equal to the id value of the "Watchdog" category to keep things tidy. You may also want to adjust security on who can edit these attribute values.

3. Create a workflow called "Watchdog Response". This workflow will be triggered to send notifications and execute a workflows as specified when a watchdog dataview record count threshold is exceeded.

This workflow will need the following attributes keys:

• ThresholdResponseWorkflow of type Workflow Type
• TextMessage of type Text
• NotificationThreshold of type Text
• PersistedValueCount of type Text
• NotificationPerson of type Person
• Dataview of Type Data View

Here is a high level overview of what this workflow will accomplish:

You may find these snippets helpful for creating your version of this workflow.

//- Notification Person associated with dataview passed into this workflow

{{ Workflow | Attribute:'Dataview','Object' | Attribute:'NotificationPerson','PrimaryAliasGuid'}}
//- ThresholdResponseWorkflow Person associated with dataview passed into this workflow
{{ Workflow | Attribute:'Dataview','Object' | Attribute:'ThresholdResponseWorkflow','Guid'}}
//- Email/Text Body
The record count threshold ({{ Workflow | Attribute:'NotificationThreshold' }}) associated with the dataview {{ Workflow | Attribute:'Dataview' }} has been exceeded.
There were {{ Workflow | Attribute:'PersistedValueCount' }} records. 

3. Create a workflow called "Check Watchdog Notification Thresholds". This workflow has just one text attribute called "WorkflowsActivateText". The workflow has just one activity that runs the following lava. Remeber run lava always needs an attribute to store the result in otherwise it will not actually run. This workflow will query persisted datasets associated with the Watchdog category and check if the record count of their persisted dataset exceeds the dataview's specified threshold for notifications. 

Customize the highlighted lines to match ids in your system.

{% sql %}
DECLARE @WatchdogCategory INT = 602;
DECLARE @DataViewNotificationThresholdAttributeId INT = 14246;
SELECT
DV.Id, DV.Guid,
    AV.[ValueAsNumeric] As[NotificationThreshold],
        COUNT(DVPV.EntityId) AS PersistedValueCount
FROM
    DataView DV
    LEFT JOIN DataViewPersistedValue DVPV ON DVPV.DataViewId = DV.Id
    LEFT JOIN AttributeValue AV ON AV.AttributeId = @DataViewNotificationThresholdAttributeId
                                 AND AV.EntityId = DV.Id
WHERE
DV.CategoryId = @WatchdogCategory
GROUP BY
DV.Id, DV.Guid, AV.[ValueAsNumeric]
Having COUNT(DVPV.EntityId) >= AV.[ValueAsNumeric];
{% endsql %}
{% for item in results %}
//- 217 here is the Watchdog Response workflow
{% workflowactivate workflowtype: '217' Dataview: '{{item.Guid}}' NotificationThreshold: '{{item.NotificationThreshold}}' PersistedValueCount: '{{item.PersistedValueCount}}' %}
        Activated new workflow with the Id of #{ { Workflow.Id } }.
{% endworkflowactivate %}
{% endfor %}

4. Create and schedule a new job "Check Watchdog Notification Thresholds" with the following settings

5. Create a least one persisted dataview under your Watchdog category with a notification threshold and notification person attribute values. Make sure the persistence schedule matches how often your scheduled "Check Watchdog Notification Thresholds" job is set to run.