Notification

Icon
Error

Report on Machines with DNS servers other than X, Y, or Z - Nerwork Configuration Report

Posted: Thursday, October 1, 2020 9:28:23 PM(UTC)
ENA

ENA

Member Original PosterPosts: 2
0
Like
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!
RC62N
#1RC62N Member Posts: 488  
posted: 10/5/2020 4:27:23 PM(UTC)
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):
Code:
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
CyberCitizen
#2CyberCitizen Member Posts: 395  
posted: 10/27/2020 2:29:30 AM(UTC)
Originally Posted by: RC62N Go to Quoted Post
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.

Active Discussions

Report Center Default Browser and Version
by  RC62N  
Go to last post Go to first unread
Last post: 11/26/2020 8:00:54 PM(UTC)
Lansweeper Trying to make a custom report that sorts assets by state
by  mzipperer   Go to last post Go to first unread
Last post: 11/25/2020 5:36:52 PM(UTC)
Lansweeper Report - All Assets with Specfic Default Gateway
by  RC62N  
Go to last post Go to first unread
Last post: 11/25/2020 4:18:08 PM(UTC)
Report Center Windows update report
by  ToeJoe   Go to last post Go to first unread
Last post: 11/24/2020 3:39:00 PM(UTC)
Lansweeper Report - assets
by  Andy.S  
Go to last post Go to first unread
Last post: 11/23/2020 2:42:01 PM(UTC)
Lansweeper Run Report on PCs from a list
by  RC62N   Go to last post Go to first unread
Last post: 11/16/2020 4:23:12 PM(UTC)