Advanced Search for Report Generation

Reports on Demand

The reports on demand screen allows you to generate reports based on specific search conditions. You can use the Custom SQL box on that screen to provide specific search criteria.

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

lookedup	ip_checked_at	home_page_checked_at
ping_checked_at	mx_checked_at	alexa_checked_at
yahoo_sitepop_checked_at	ip_whois_at	ssl_checked_at
google_index_checked_at	google_pr_checked_at	dns_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_time	google_pr	google_index_count
alexa_rank	alexa_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

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.