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.

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_expiry

registrar_expiry

created_on

last_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_at	rootdns_checked_at	secondary_whois_checked_at
ping_checked_at	manual_edited_at	alexa_checked_at
home_page_checked_at	ip_whois_at	ssl_checked_at
google_index_checked_at	added_on	subdomains_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

pingtime	axfr_status	google_index_count
alexa_rank	sid

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_found

edited

write_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

registrar	ip	ns1
ns2	ns3	ns4
availability	registry_whois	registrar_whois
redirect_url	home_page_status	home_page_title
home_page_size	home_page_token	mx1
mx2	mx3	mxip1
mxip2	mxip3	whois_server
owner	organization	address
owner_country	admin_email	tech_email
billing_email	registrant_email	notes_a
notes_b	notes_c	notes_d
ip_whois	ip_net_name	ip_org_name
ip_rtech_name	ip_asnumber	ip_asname
status	ascii_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.