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: 528  
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: 423  
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

Lansweeper Helpdesk tabs always regenarated
by  rader   Go to last post Go to first unread
Last post: Today at 4:41:10 AM(UTC)
Lansweeper Priority Scanning
by  Tythesly  
Go to last post Go to first unread
Last post: Yesterday at 6:19:44 PM(UTC)
Lansweeper Scanning Cisco UCS
by  MikeMiller   Go to last post Go to first unread
Last post: Yesterday at 3:20:32 PM(UTC)
Lansweeper No One getting back to me from Lansweeper
by  FrankSc  
Go to last post Go to first unread
Last post: Yesterday at 10:57:32 AM(UTC)
Lansweeper Enterprise Options in Menu Bar/Configuration
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 10:54:19 AM(UTC)
Lansweeper INFO DateTimeService time refresh
by  miharix  
Go to last post Go to first unread
Last post: 6/18/2021 10:48:57 AM(UTC)
Lansweeper RPC Unavailable error
by  Greeno   Go to last post Go to first unread
Last post: 6/17/2021 7:15:07 PM(UTC)