Query

Contents

• Introduction
• Query Reduction
  • Order
  • Occurrence
• Condition Analysis
• Execute!
• Post-Process

Introduction

Typically when requesting objects from PRTG such as sensors, devices and groups, PrtgAPI must construct a collection of request parameters specifying things such as

• Conditions to filter results by
• How many items to return
• The property to sort results by

While PrtgAPI provides many overloads to assist in constructing parameters for you, this can be very tedious when attempting to do things like specify multiple filters, with each filter requiring a SearchFilter object be defined. Instantiating an object to specify a filter is not a very natural way of specifying intent. Ideally, we would like to construct our request using a much more pleasant form of expression, such as LINQ.

This can be accomplished in .NET by implementing a custom LINQ Provider. The major challenge when defining a new provider however is the fact that not all methods supported by LINQ may be supported by the target system. For example, you cannot GroupBy a certain property in API requests

//This makes no sense
client.QuerySensors().GroupBy(s => s.Device);

The standard approach taken by most IQueryProvider implementations is to rigidly define what is allowed and not allowed. If you do something that is not allowed, you get an exception. This is bad. As the end user, you don't really care about what is "supported". You know what you want to do. Make it work!

The recommended way of circumventing unsupported operations is to cast your query IEnumerable within the specified expression. This will allow you to then execute the query and then continue parsing with LINQ to Objects

//Yuck
client.QuerySensors().AsEnumerable().GroupBy(s => s.Device);

This is arguably even worse, as now you're expected to keep tracking of what is and isn't supported by your provider, as well as stick AsEnumerable() in there every time you want to do something mildly complicated.

PrtgAPI solves these problems by defining a hybrid query provider. Upon parsing the request

1. The content and order of the specified queries are analyzed to determine how much of the expression is a valid query
2. As many illegal sub-components of the valid query are removed as possible
3. The maximal legal query is executed against PRTG
4. The PRTG response is fed into a LINQ to Objects representation of the original query

By utilizing a hybrid approach, PrtgAPI successfully abstracts away the implementation details of how the PRTG API operates away from the user, allowing for arbitrarily complex requests to be specified

Phase 1: Query Reduction

Order

PRTG can only execute each component of a request in a single "step". In order to guarantee as many components are executed server side as possible, queries must be specified in the following order

1. OrderBy / Where / Any(pred) / Count(pred) / First(pred)
2. Skip
3. Take

In addition to these methods, Select and SelectMany can be used at any position within the query

//A high performance query
client.QuerySensors()
    .Where(s => s.Id > 4000)
    .OrderBy(s => s.Name)
    .Select(s => s.Probe)
    .Skip(1)
    .Take(2);

Queries are most performant when ordered logically based on how you would expect to operate on a list of elements. Consider the following example which specifies queries in a less than optimal order

//A slow query
client.QuerySensors().Skip(1).Where(s => s.Id > 4000);

In the this query, we first skip the first element, then filter for objects whose ID is greater than 4000. The issue with this query is that you will get completely different results based on whether object that was skipped would have matched the condition that came later. If the object had an ID of 3000, we just skipped a random object we would have excluded anyway. If the object had an ID of 5000, we just excluded an object that otherwise would have been included.

The intent of the query is to return an ambiguous number of results based on the order of input items. PRTG does not support such ambiguity, however PrtgAPI will provide it to you by requesting all but the first item from PRTG, then filtering the results client side to yield the final result.

By contrast, consider an expression with the order of the queries reversed

//A fast query
client.QuerySensors().Where(s => s.Id > 4000).Skip(1);

This query will always return the same number of items every time it is run. It is a highly specific query, and as such can be executed in full server side.

Occurrence

When determining what can be executed server side, PrtgAPI allows queries to be specified according to the following rules

1. A given method can be specified as many times in a row as desired
2. Additional calls to a given method do not apply when interrupted by other methods
3. All methods specified after an unsupported method are ignored

The following complex query demonstrates all of these principles

client.QuerySensors()
    .Where(s1 => s1.Name == "Ping")
    .Where(s2 => s2.Id <= 3000)
    .Skip(3)
    .Where(s => s.Device.Contains("dc"))
    .SkipWhile(s => s.Probe.Contains("contoso"))
    .Any(s => s.Group == "Servers");

Upon parsing this query, PrtgAPI performs the following operations:

1. Merge: multiple Where expressions in a row can be combined to create a logical AND
2. Include: Skip(3) can be unambiguously executed against the results from Step 1
3. Ignore: The third Where cannot be executed server side, as it must execute after constructing the skipped collection
4. Ignore: SkipWhile is not supported by PRTG, so is excluded
5. Ignore: SkipWhile contaminated the query; we have no idea what's going on, so must ignore Any

After filtering these queries, the request is reduced to a simple query that can be understood by PRTG

client.QuerySensors()
    .Where(s1 => s1.Name == "Ping" && s1.Id <= 3000)
    .Skip(3)

The original query will then be transformed into an appropriate expression based on Enumerable (with redundant nodes like Skip removed as required) and applied against the results returned from PRTG.

Phase 2: Condition Analysis

PRTG defines two rules for processing search filters specified in API requests

• Filtering on the same property multiple times is equivalent to a logical OR
• Filtering on multiple different properties is equivalent to a logical AND

These rules are very easy to enforce when operating on SearchFilter objects, as there is no explicit logical intent. You specify a bunch of filters, and you get what PRTG gives you.

//Request all objects whose name contains "Disk" OR "Usage".
//PRTG does not understand "Disk" AND "Usage"
var filters = new[]
{
    new SearchFilter(Property.Name, FilterOperator.Contains, "Disk"),
    new SearchFilter(Property.Name, FilterOperator.Contains, "Usage"),
}

var sensors = client.GetSensors(filters);

With LINQ however, filters are suddenly part of the language. You can now specify anything you want!

//Panic!
var sensors = client.QuerySensors().Where(s => s.Name.Contains("Disk") && s.Name.Contains("Usage"));

//Panic!!
var devices = client.QueryDevices().Where(d => d.Name.Contains("dc") || d.Probe == "Contoso");

PrtgAPI solves this problem in two ways

1. Eliminate all illegal conditions from the query
2. If the illegal condition was for a logical OR, execute a separate request to retrieve that condition's items

//Remove the second condition on "Name"
var sensors = client.QuerySensors().Where(s => s.Name.Contains("Disk"));

//Split the query into two requests
var devices1 = client.QueryDevices().Where(d => d.Name.Contains("dc"));
var devices2 = client.QueryDevices().Where(d => d.Probe == "Contoso");

The results returned by these requests are then fed into the original query, now based on Enumerable, yielding a single list of matching the specified expression. Since the same object might be returned multiple times by these split requests, PrtgAPI transparently filters the response with DistinctBy on the object's Id as the results are enumerated.

Phase 3: Execute!

Phase 4: Post-Process