cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
EvrazNA
Engaged Sweeper
Would someone please help me write (or modify an existing) report that produces a list of machines with DNS servers that do not match a small list of "approved" DNS servers?

Desired Output:

ComputerName, IPLocation, DNS1, DNS2, DNSx ...

Thanks!
2 REPLIES 2
RCorbeil
Honored Sweeper II
Modifying "Network: No DNS server specified": List machines where the DNS search list doesn't contain one or more of Cloudflare, Google, Quad9, OpenDNS (to use some actual examples):
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblNetwork.DNSServerSearchOrder
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where
tblState.Statename = 'Active'
And tblNetwork.IPAddress <> '0.0.0.0'
And tblNetwork.IPAddress <> ''
And tblNetwork.IPEnabled = 'True'
And NOT (tblNetwork.DNSServerSearchOrder LIKE '%1.1.1.1%'
OR tblNetwork.DNSServerSearchOrder LIKE '%8.8.8.8%'
OR tblNetwork.DNSServerSearchOrder LIKE '%9.9.9.9%'
OR tblNetwork.DNSServerSearchOrder LIKE '%208.67.222.222%'
)
Order By
tblAssets.AssetName,
tblAssets.Domain
RC62N wrote:
Modifying "Network: No DNS server specified": List machines where the DNS search list doesn't contain one or more of Cloudflare, Google, Quad9, OpenDNS


Thank you I stole part of this to report on DNS servers that are not our Corporate ones.