Filter Queries for Lookup Scheduler and Reports

Filter queries allow you to control the domains that will be looked up or reported as part of the Lookup / Report Scheduler jobs.

The filter queries are almost similar to regular SQL queries but with the mathematical operator changed to use alphabetic codes.

A typical SQL query fragment to find registry expiry dates after a specified date is

registry_expiry > ?

This will become

registry_expiry GT ?

You should also provide parameters for every ? you have in your query. You can use entries like [TODAY], [TODAY+30], [TODAY-46], etc. as parameters.

Use the following table for constructing queries that use comparison.

LT	Less than
LE	Less than or equal
GT	Greater than
GE	Greater than or equal
BW	Begins with
EQ	Equals
EW	Ends with
NE	Not equal
CN	Contains

Domain vs Subdomain Columns

You can use the d. prefix to identify domain columns and s. prefix to identify subdomain columns.

The auto_added Column

The subdomain table uses a column called auto_added as an identifier for the source of data.

The 'auto_added' column will be set to 0 for entries manually added by you (for example, subdomain labels like www), it will be 1 for DNS columns added after lookups and 2 for SSL columns added after lookups. Please see the examples below for a better understanding of this.

Examples

Find domains that will expire within 30 days.

Find COM or NET domains with empty registrar expiry date

Filter

((d.domain LIKE ? OR d.domain LIKE ?) AND (d.registrar_expiry IS NULL))

Parameters

%.com,%.net

Find Newly Added Subdomains

s.auto_added=0 AND s.added_on > d.subdomains_checked_at AND d.subdomains_checked_at IS NOT NULL

Find Domains Belonging To a Category

You can use the shortcut [IN_XXXX] to find domains that belong to a specific category. For example, [IN_Business Domains] will find domains that belong to 'Business Domains'.

This shortcut is especially useful when you want to create a custom lookup schedule to update domains that belong to a specific category. For example, you can create a lookup schedule and then set it's query parameter to [IN_Important Domains] and the application will lookup all the domains in 'Important Domains' category as per your scheduler settings.

Please also see

Custom Queries