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

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 < '2014-7-21'

created_on > '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

lookedupip_checked_athome_page_checked_at
ping_checked_atmx_checked_atalexa_checked_at
yahoo_sitepop_checked_atip_whois_atssl_checked_at
google_index_checked_atgoogle_pr_checked_atdns_checked_at
google_sitepop_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

ping_timegoogle_prgoogle_index_count
alexa_rankalexa_links

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

Examples

ping_time > 300

google_pr > 2 AND google_index_count > 10

alexa_rank < 150000

Boolean Columns

page_token_found

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.