Custom SQL Queries - How To

Reports on Demand

The custom queries tab in the left pane allows you to generate CSV reports based on specific search conditions. Use the Custom SQL Query box to provide specific search criteria.

Custom Auto Queries

More on SQL Queries

You can use any domain data column in your custom queries. You will find many examples below.

Columns that show just the date

registry_expiryregistrar_expirycreated_onlast_update

These columns store just the date (no time component). Use normal mathematical operators to construct queries for these columns. The dates should always be in yyyy-mm-dd format and should have single quotes around them.

You can use AND or OR to merge multiple queries.

Examples

registry_expiry LT '2014-7-21'

created_on GT '2001-12-31'

registrar_expiry = '2017-1-30'

created_on > '2001-12-31' AND registry_expiry < '2016-7-21'

created_on < '2000-12-31' OR created_on >= '2009-12-31'

To check if a date is set or not set...

registry_expiry is NULL

registry_expiry is not NULL

Sometimes you may need to check for 0-0-0 date entries too to find domains that have an empty date.

registry_expiry IS NULL OR registry_expiry = '0000-00-00'

Date and Time Columns

primary_whois_checked_atrootdns_checked_atsecondary_whois_checked_at
ping_checked_atmanual_edited_atalexa_checked_at
home_page_checked_atip_whois_atssl_checked_at
google_index_checked_atadded_onsubdomains_checked_at

You can use yyyy-mm-dd h:m:s or yyyy-mm-dd formats to compare these fields. Please note that if you don't specify the hours, minutes and seconds, it will be taken as 0:0:0

Examples

lookedup = '2014-4-25 04:08:03'

lookedup > '2014-4-25 04:17:0' AND lookedup < '2014-4-25 04:18:59'

lookedup > '2014-4-25'

Number Columns

pingtimeaxfr_statusgoogle_index_count
alexa_ranksid

These columns store just numbers. Use normal mathematical operators to construct queries for these columns. Don't use any quotes around the numbers.

Examples

pingtime > 300

alexa_rank < 200000 AND google_index_count > 10

alexa_rank < 150000

Boolean Columns

page_token_foundeditedwrite_protect

You can treat this as a number column. You can use != or <> for not equal to comparisons.

Examples

page_token_found = 0

page_token_found != 0

Text Columns

registraripns1
ns2ns3ns4
availabilityregistry_whoisregistrar_whois
redirect_urlhome_page_statushome_page_title
home_page_sizehome_page_tokenmx1
mx2mx3mxip1
mxip2mxip3whois_server
ownerorganizationaddress
owner_countryadmin_emailtech_email
billing_emailregistrant_emailnotes_a
notes_bnotes_cnotes_d
ip_whoisip_net_nameip_org_name
ip_rtech_nameip_asnumberip_asname
statusascii_domain

These columns store text. Use single quotes around any string when you construct queries for these columns.

Examples

ns2 LIKE '%softnik%

notes_b LIKE 'sales%'

status = 'ok'

status NOT LIKE 'ok'

Like operator allows you to pick only the domains that are "like" whatever you specify. The percent sign "%" is used as a wild card to match any possible character. For example:

ns1 LIKE 'ns0%'

Will select all domains whose name server (NS1) starts with ns0. Examples include ns0.softnik.com, ns0.netnames.org, etc.

status LIKE '%delete%'

Will select all domain names whose status contains the word "delete" anywhere. Examples include clientDeleteProhibited, ServerDeleteProhibited, etc.