Notification

Icon
Error

Assign assets to their right scanning server based on IP scan ranges

Posted: Wednesday, January 2, 2019 3:53:45 PM(UTC)
klaus

klaus

Member Original PosterPosts: 12
0
Like
This report shows you all assets that aren't assigned to the right scanning server based on IP range scanning configuration.

Code:
select *
From tblAssets
  Inner Join (Select tsysIPScanRanges.Servername As servername,
        Convert(numeric(18),ParseName(tsysIPScanRanges.Ipstart, 4) +
        Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 3), ''), 3) +
        Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 2), ''), 3) +
        Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 1), ''),
        3)) As ipstartnumeric,
        Convert(numeric(18),ParseName(tsysIPScanRanges.Ipend, 4) + Right('000' +
        IsNull(ParseName(tsysIPScanRanges.Ipend, 3), ''), 3) + Right('000' +
        IsNull(ParseName(tsysIPScanRanges.Ipend, 2), ''), 3) + Right('000' +
        IsNull(ParseName(tsysIPScanRanges.Ipend, 1), ''), 3)) As ipendnumeric
      From tsysIPScanRanges where enabled = 1) As tsir On tblAssets.IPNumeric >=
    tsir.ipstartnumeric And tblAssets.IPNumeric <= tsir.ipendnumeric
Where tblAssets.Scanserver <> tsir.servername


If you want to automatically fix that, you can run this on a daily basis. But you should make sure that you don't have any overlapping IP scanning ranges. (see my other report in this forum).

Code:
update tblassets
set tblassets.scanserver = tsir.servername
From tblAssets
  Inner Join (Select tsysIPScanRanges.Servername As servername,
        Convert(numeric(18),ParseName(tsysIPScanRanges.Ipstart, 4) +
        Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 3), ''), 3) +
        Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 2), ''), 3) +
        Right('000' + IsNull(ParseName(tsysIPScanRanges.Ipstart, 1), ''),
        3)) As ipstartnumeric,
        Convert(numeric(18),ParseName(tsysIPScanRanges.Ipend, 4) + Right('000' +
        IsNull(ParseName(tsysIPScanRanges.Ipend, 3), ''), 3) + Right('000' +
        IsNull(ParseName(tsysIPScanRanges.Ipend, 2), ''), 3) + Right('000' +
        IsNull(ParseName(tsysIPScanRanges.Ipend, 1), ''), 3)) As ipendnumeric
      From tsysIPScanRanges where enabled = 1) As tsir On tblAssets.IPNumeric >=
    tsir.ipstartnumeric And tblAssets.IPNumeric <= tsir.ipendnumeric
Where tblAssets.Scanserver <> tsir.servername
Esben.D
#1Esben.D Member Administration Posts: 1,631  
posted: 1/7/2019 2:33:30 PM(UTC)
Thank you for sharing your reports. I'm sure some people will find them helpful.

Just as an FYI, since there are different configuration tabs for each scanning server, the best practice is to configure the correct scanning target to the preferred scanning server.

This alternative method is not officially supported, but that doesn't mean it doesn't have it's uses ;)
klaus
#2klaus Member Original PosterPosts: 12  
posted: 1/7/2019 5:31:35 PM(UTC)
Thanks Charles. Just to point out why I am doing this. AD scanning and LSPush scanning do have an effect on the scanning server per asset. Now if we don't have the assets bound to their closest scanning server, this affects deployment timings. We have scanning servers in all different time zones.
This query corrects the situation for us. Scanning server configuration is identical, but great you pointed that out

Originally Posted by: Charles.X Go to Quoted Post
Thank you for sharing your reports. I'm sure some people will find them helpful.

Just as an FYI, since there are different configuration tabs for each scanning server, the best practice is to configure the correct scanning target to the preferred scanning server.

This alternative method is not officially supported, but that doesn't mean it doesn't have it's uses ;)


Active Discussions

Lansweeper Update DNS record
by  Lewis_Spokane   Go to last post Go to first unread
Last post: Today at 12:17:26 AM(UTC)
Lansweeper Trigger deploy when seen
by  bbazian  
Go to last post Go to first unread
Last post: Yesterday at 9:13:02 PM(UTC)
Lansweeper Restoring server from backup, retaining Lansweeper
by  JacobH   Go to last post Go to first unread
Last post: Yesterday at 8:48:39 PM(UTC)
Lansweeper LSAgent has forgotten x64 Windows applications
by  Esben.D  
Go to last post Go to first unread
Last post: Yesterday at 3:37:55 PM(UTC)
Lansweeper Not finding some switches on IP range scan
by  acstanley   Go to last post Go to first unread
Last post: Yesterday at 1:23:36 PM(UTC)
Lansweeper Generate report based upon filter?
by  pryan67  
Go to last post Go to first unread
Last post: Yesterday at 1:20:58 PM(UTC)
Lansweeper LsAgent on Ubuntu 18.04 - Not getting updated in the console
by  rpokkyarath   Go to last post Go to first unread
Last post: Yesterday at 10:15:22 AM(UTC)
Lansweeper Auditing deletes
by  B.L.  
Go to last post Go to first unread
Last post: Yesterday at 9:48:45 AM(UTC)