PowerQuery Monitors

DataSet Monitor historically was used mostly for endpoint detection (ex. httpMonitor) and AWS integrations such as S3 bucket and RDS logs/metrics ingestions. Recently, we've expanded additional support for ingesting events from Azure Eventhub and GCP Stackdriver. The goal is to enable users to get data in (GDI) to DataSet not only with ease but on a massive scale. Thus, DataSet monitor has become our top implementation choice since the JSON configuration is very intuitive to the end-users, and our engineering also as the flexibilities to handle large log volume ingestions behind the scene.

 The "PowerQuery Monitor" is built on the above premise. In contrast to ingesting data to DataSet, PowerQuery Monitor queries existing data from the account and logs the results back. The function is primarily built as a substitute for "PowerQuery for alerts" before the feature is available.  

For example, I'd like to set up an alert when the number of bytes per event exceeds 100 from host "uploadfile-host-01". First, I use the following PowerQuery to get the number of bytes per message from the host.

|join
upload_bytes = (tag == "logVolume" metric='logBytes' host = 'uploadfile-host-01'|group bytes = sum(value) by host) ,
upload_events = (serverHost= 'uploadfile-host-01' | group total_msg = count() by host=serverHost )
on host
| let bytes_per_event = bytes/total_msg

Copy and paste the query to the PowerQuery page to confirm the query works as expected.

We can then transfer query to the DataSet Monitor config file. Here is the snippet of the sample config.

{
    type: "powerquery",
    executionIntervalMinutes: "1.0",
    logAttributes: {
        test: "weili"
    },
    queries: [
      {
        query: "|join upload_bytes = (tag == 'logVolume' metric='logBytes' host = 'uploadfile-host-01'|group bytes = sum(value) by host), upload_events = (serverHost= 'uploadfile-host-01' | group total_msg = count() by host=serverHost ) on host | let bytes_per_event = bytes/total_msg",
        startTime: "1 minutes",
        endTime: "0 minutes"
      }
    ]
}  

In this sample config, the PowerQuery Monitor fetches the result for the past 1 minute and logs the result back to the account every minute. Here is the screenshot of the PowerQuery Monitor output.

Lastly, the query output can now be used as an alert.

{
    alertAddress: "weili@scalyr.com",
    description: "Test PowerQuery Alert",
    gracePeriodMinutes: 0,
    renotifyPeriodMinutes: 60,
    resolutionDelayMinutes: 5,
    trigger: "mean:2m(bytes_per_event where test == 'weili') > 100",
}