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.
|LE||Less than or equal|
|GE||Greater than or equal|
Domain vs Subdomain Columns
You can use the
d. prefix to identify domain columns and
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.
Find domains that will expire within 30 days.
Find COM or NET domains with empty registrar expiry date
((d.domain LIKE ? OR d.domain LIKE ?) AND (d.registrar_expiry IS NULL))
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
and the application will lookup all the domains in 'Important Domains' category as per your scheduler settings.