turbot/net_insights

Query: dns_soa_report

Usage

powerpipe query net_insights.query.dns_soa_report

Steampipe Tables

SQL

with domain_list as (
select distinct domain, substring( domain from '^(?:[^/:]*:[^/@]*@)?(?:[^/:.]*\.)+([^:/]+)' ) as tld from net_dns_record where domain = $1 order by domain
),
domain_parent_server as (
select l.domain, d.domain as tld, d.target as parent_server from net_dns_record as d inner join domain_list as l on d.domain = l.tld where d.type = 'SOA'
),
domain_parent_server_ip as (
select * from net_dns_record where domain in (select parent_server from domain_parent_server)
),
domain_parent_server_with_ip as (
select domain_parent_server.domain, host(domain_parent_server_ip.ip) as ip_text from domain_parent_server inner join domain_parent_server_ip on domain_parent_server.parent_server = domain_parent_server_ip.domain where domain_parent_server_ip.type = 'A' order by domain_parent_server.domain
),
domain_parent_server_ns_list as (
select net_dns_record.domain, net_dns_record.target as name_server from net_dns_record inner join domain_parent_server_with_ip on net_dns_record.domain = domain_parent_server_with_ip.domain and net_dns_record.dns_server = domain_parent_server_with_ip.ip_text and net_dns_record.type = 'NS'
),
domain_primary_ns as (
select target from net_dns_record where domain = $1 and type = 'SOA'
),
domain_ns_records as (
select * from net_dns_record where domain in (select domain from domain_list) and type = 'NS' order by domain
),
ns_ips as (
select * from net_dns_record where domain in (select target from domain_ns_records) order by domain
),
ns_with_ip as (
select domain_ns_records.domain, host(ns_ips.ip) as ip_text from domain_ns_records inner join ns_ips on domain_ns_records.target = ns_ips.domain where ns_ips.type = 'A' order by domain_ns_records.domain
),
unique_serial as (
select
distinct r.serial,
r.domain
from
net_dns_record as r
inner join ns_with_ip as i on r.domain = i.domain and r.dns_server = i.ip_text
where
r.type = 'SOA'
)
select
'Name servers have same SOA serial' as "Recommendation",
case
when (select count(*) from unique_serial where domain = d.domain) is null or (select count(*) from unique_serial where domain = d.domain) > 1 then '❌'
else '✅'
end as "Status",
case
when (select count(*) from unique_serial where domain = d.domain) is null or (select count(*) from unique_serial where domain = d.domain) > 1
then 'At least one name server has different SOA serial.'
else 'All name servers have same SOA serial.'
end
|| ' Sometimes serial numbers become out of sync when any record within a zone got updated and the changes are transferred from primary name server to other name servers. If the SOA serial number is not same for all NS record there might be a problem with the transfer.' as "Result"
from
domain_list as d
UNION
select
'Primary name server listed at parent' as "Recommendation",
case
when (select count(*) from domain_parent_server_ns_list where name_server in (select * from domain_primary_ns)) is null then '❌'
else '✅'
end as "Status",
case
when (select count(*) from domain_parent_server_ns_list where name_server in (select * from domain_primary_ns)) is null then 'Primary name server not listed in parent.'
else domain || ' primary name server ' || (select target from domain_primary_ns) || ' listed at parent.'
end
|| ' Primary name server is the name server declared in your SOA file and generally reads your records from zone files. It is responsible for distributing the data to secondary name servers. Unmatched NS records can cause delays when resolving domain records, as it tries to contact a name server that is either non-existent or non-authoritative.' as "Result"
from
domain_list
UNION
select
'SOA serial number should be between 1 and 4294967295' as "Recommendation",
case
when serial < 1 or serial > 4294967295 then '❌'
when not (select serial::text ~ '^\d{4}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}\d{2}$') then 'ℹī¸'
else '✅'
end as "Status",
case
when not (select serial::text ~ '^\d{4}[0-1]{1}[0-9]{1}[0-3]{1}[0-9]{1}\d{2}$') then domain || ' SOA serial number ' || serial || ' doesn''t match recommended format (per RFC1912 2.2) YYYYMMDDnn.'
else domain || ' SOA serial number is ' || serial || '.'
end as "Result"
from
net_dns_record
where
domain = $1
and type = 'SOA'
UNION
select
'SOA refresh value should be between 20 minutes and 12 hours' as "Recommendation",
case
when refresh < 1200 or refresh > 43200 then '❌'
else '✅'
end as "Status",
'SOA Refresh interval is: ' || refresh || '. This value indicates how often a secondary will poll the primary server to see
if the serial number for the zone has increased (so it knows
to request a new copy of the data for the zone). As per RFC1912 section 2.2 value should be in between 20 mins to 2 hrs.' as "Result"
from
net_dns_record
where
domain = $1
and type = 'SOA'
UNION
select
'SOA retry value should be between 2 minutes and 2 hours' as "Recommendation",
case
when retry < 120 or retry > 7200 then '❌'
else '✅'
end as "Status",
'SOA Retry value is: ' || retry || '. If a secondary was unable to contact the primary at the
last refresh, wait the retry value before trying again. Recommended value is 2 minutes to 2 hours.' as "Result"
from
net_dns_record
where
domain = $1
and type = 'SOA'
UNION
select
'SOA expire value should be between 2 weeks and 4 weeks' as "Recommendation",
case
when expire < 1209600 or expire > 2419200 then '❌'
else '✅'
end as "Status",
'SOA Expire value is: ' || expire || '. This value indicates how long a secondary will still treat its copy of the zone
data as valid if it can''t contact the primary. As per RFC1912 section 2.2 value should be in between 2-4 weeks.' as "Result"
from
net_dns_record
where
domain = $1
and type = 'SOA'
UNION
select
'SOA minimum TTL value should be between 10 minutes to 24 hours' as "Recommendation",
case
when minimum < 600 or minimum > 86400 then '❌'
else '✅'
end as "Status",
'SOA Minimum TTL is: ' || minimum || '. This value was used to serve as a default TTL for records without a given TTL value and now is
used for negative caching (indicates how long a resolver may cache the negative answer). RFC2308 recommends a value of 1-3 hours.' as "Result"
from
net_dns_record
where
domain = $1
and type = 'SOA'

Params

ArgsNameDefaultDescriptionVariable
$1domain_name_input

    Dashboards

    The query is used in the dashboards: