Notification

Icon
Error

Find overlapping IP scan ranges

Posted: Wednesday, January 2, 2019 3:49:58 PM(UTC)
klaus

klaus

Member Original PosterPosts: 14
1
Like
This report shows you overlapping IP scan ranges and the assigned site server.
Code:
Select Top 1000000 t1.IprangeID As [1 Range ID],
  t1.Servername As [1 Scanning server],
  t1.Ipstart As [1 Start],
  t1.Ipend As [1 End],
  '<----->' As [=],
  t2.IprangeID As [2 Range ID],
  t2.servername As [2 Scanning server],
  t2.Ipstart As [2 Start],
  t2.Ipend As [2 End]
From tsysIPScanRanges t1
  Inner Join (Select t2.IprangeID,
        t2.Ipstart,
        t2.Ipend,
        t2.Servername As servername,
        Convert(numeric(18),ParseName(t2.Ipstart, 4) + Right('000' +
        IsNull(ParseName(t2.Ipstart, 3), ''), 3) + Right('000' +
        IsNull(ParseName(t2.Ipstart, 2), ''), 3) + Right('000' +
        IsNull(ParseName(t2.Ipstart, 1), ''), 3)) As istart2,
        Convert(numeric(18),ParseName(t2.Ipend, 4) + Right('000' +
        IsNull(ParseName(t2.Ipend, 3), ''), 3) + Right('000' +
        IsNull(ParseName(t2.Ipend, 2), ''), 3) + Right('000' +
        IsNull(ParseName(t2.Ipend, 1), ''), 3)) As iend2
      From tsysIPScanRanges As t2
      Where t2.Enabled = 1) As t2 On
    (t2.istart2 >= Convert(numeric(18),ParseName(t1.Ipstart, 4) + Right('000' +
      IsNull(ParseName(t1.Ipstart, 3), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipstart, 2), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipstart, 1), ''), 3)) And
      t2.istart2 <= Convert(numeric(18),ParseName(t1.Ipend, 4) + Right('000' +
      IsNull(ParseName(t1.Ipend, 3), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipend, 2), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipend, 1), ''), 3)) And t2.IprangeID <> t1.IprangeID)
    Or
    (t2.iend2 >= Convert(numeric(18),ParseName(t1.Ipstart, 4) + Right('000' +
      IsNull(ParseName(t1.Ipstart, 3), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipstart, 2), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipstart, 1), ''), 3)) And
      t2.iend2 <= Convert(numeric(18),ParseName(t1.Ipend, 4) + Right('000' +
      IsNull(ParseName(t1.Ipend, 3), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipend, 2), ''), 3) + Right('000' +
      IsNull(ParseName(t1.Ipend, 1), ''), 3)) And t2.IprangeID <> t1.IprangeID)
Where t1.Enabled = 1
tomscott2340
#1tomscott2340 Member Posts: 10  
posted: 5/15/2019 3:41:32 PM(UTC)
Hello, I get the following when trying to run your report.. Any ideas? Thanks

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
warlock1663
#2warlock1663 Member Posts: 15  
posted: 5/16/2019 2:47:45 PM(UTC)
How do you set this up to run as a report?

Active Discussions

Lansweeper local admin users of a specific device
by  kdunnett   Go to last post Go to first unread
Last post: 5/25/2020 9:30:26 PM(UTC)
Lansweeper How to get total disk usage of all VM assets
by  Erik.T  
Go to last post Go to first unread
Last post: 5/25/2020 4:49:58 PM(UTC)
Lansweeper Windows Server 2016 & Patch Tuesday May 2020
by  Hendrik.VE   Go to last post Go to first unread
Last post: 5/22/2020 8:20:05 PM(UTC)
Lansweeper Users mapped "shared" printers
by  Andy.S  
Go to last post Go to first unread
Last post: 5/22/2020 4:16:23 PM(UTC)
Lansweeper Report on Assets in a Static Group
by  Andy.S   Go to last post Go to first unread
Last post: 5/22/2020 2:55:03 PM(UTC)
Lansweeper Windows 10 Activation
by  TruSynergy  
Go to last post Go to first unread
Last post: 5/21/2020 7:54:25 PM(UTC)
Lansweeper Filtering Report Based On Active Status
by  CyberCitizen   Go to last post Go to first unread
Last post: 5/21/2020 4:04:33 AM(UTC)
Lansweeper Windows: Unauthorized Administrators (Built-in)
by  Jackie.L  
Go to last post Go to first unread
Last post: 5/20/2020 8:01:17 PM(UTC)