This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

IP Scanning versus IP Locations Report

Go to solution
Mister_Nobody
Honored Sweeper III

3 weeks ago - last edited Tuesday by Lansweeper Tech Support

We have 300+ subnetworks and very difficult to stay them actually.

So we decide to create some rules for their inventory:

1. You have to resolve all undefined IP location via correction of a description on  Assets Groups page

2. IP location must contains IP address of  network default gateway and be more or equal of IP Scanning Range. It helps to track cross-subnet assets moving https://community.lansweeper.com/t5/reports-analytics/asset-s-networks-tracker/m-p/81505

3. Names of scopes better to be equal.

So we create report for supporting this rules:

Select Top 1000000 allscopes.*,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then
      'Scopes names are different but scopes have same number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are same but scopes have different number of assets'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are different and scopes have different number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'OK'
    Else 'Please fill names of scopes'
  End As Scopes_status,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'Orange'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'LightSalmon'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'Tomato'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'LightGreen'
    Else 'skyblue'
  End As backgroundcolor
From (Select Top 1000000 ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      Sum(Case
        When a.locationid = ips.locationid Then 1
        Else 0
      End) in_IPLocation,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled,
      Sum(Case
        When (a.ipnumeric Between Convert(numeric,Right('000' +
          IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 1), ''),
          3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend, 4),
          ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
          3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
          Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))) Then 1
        Else 0
      End) in_ScanRange
    From (Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <= l.endip
        Union
        Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          Null IPLocation,
          Null Realstart,
          Null Realend,
          Null locationid
        From tsysIPScanRanges s
        Except
        Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          Null IPLocation,
          Null Realstart,
          Null Realend,
          Null locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <= l.endip
        Union
        Select Null Ipstart,
          Null Ipend,
          Null Description,
          Null Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
        Except
        Select Null Ipstart,
          Null Ipend,
          Null Description,
          Null Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <=
              l.endip) ips
      Left Join tblassets a On a.ipnumeric Is Not Null And (ips.locationid =
            a.locationid Or (a.ipnumeric Between Convert(numeric,Right('000' +
              IsNull(ParseName(ips.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipend, 1), ''),
              3)) And Convert(numeric,Right('000' +
              IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 1), ''), 3))))
    Group By ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled) As allscopes

 

1 ACCEPTED SOLUTION
Go to solution
Mister_Nobody
Honored Sweeper III

3 weeks ago

Fix "between" syntax error (unknown location scanning range counter):

Select Top 1000000 allscopes.*,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then
      'Scopes names are different but scopes have same number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are same but scopes have different number of assets'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are different and scopes have different number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'OK'
    Else 'Please fill names of scopes'
  End As Scopes_status,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'Orange'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'LightSalmon'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'Tomato'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'LightGreen'
    Else 'skyblue'
  End As backgroundcolor
From (Select Top 1000000 ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      Sum(Case
        When a.locationid = ips.locationid Then 1
        Else 0
      End) in_IPLocation,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled,
      Sum(Case
        When (a.ipnumeric Between Convert(numeric,Right('000' +
          IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 1), ''),
          3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend, 4),
          ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
          3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
          Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))) Then 1
        Else 0
      End) in_ScanRange
    From (Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <= l.endip
        Union
        Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          Null IPLocation,
          Null Realstart,
          Null Realend,
          Null locationid
        From tsysIPScanRanges s
        Except
        Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          Null IPLocation,
          Null Realstart,
          Null Realend,
          Null locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <= l.endip
        Union
        Select Null Ipstart,
          Null Ipend,
          Null Description,
          Null Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
        Except
        Select Null Ipstart,
          Null Ipend,
          Null Description,
          Null Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <=
              l.endip) ips
      Left Join tblassets a On a.ipnumeric Is Not Null And (ips.locationid =
            a.locationid Or (a.ipnumeric Between Convert(numeric,Right('000' +
              IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 1), ''),
              3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend,
              4), ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
              3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
              Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))))
    Group By ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled) As allscopes

View solution in original post

0 Kudos
3 REPLIES 3
Go to solution
Mister_Nobody
Honored Sweeper III

2 weeks ago

Report version 2.0

Simple code but IP location must be more or equal than IP Scanning Range:

Select Top 1000000 allscopes.*,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then
      'Scopes names are different but scopes have same number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are same but scopes have different number of assets'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are different and scopes have different number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'OK'
    Else
      'Please fill names of scopes or Location size is less than Scanning Range'
  End As Scopes_status,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'Orange'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'LightSalmon'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'Tomato'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'LightGreen'
    Else 'skyblue'
  End As backgroundcolor
From (Select Top 1000000 ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      Sum(Case
        When a.locationid = ips.locationid Then 1
        Else 0
      End) in_IPLocation,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled,
      Sum(Case
        When (a.ipnumeric Between Convert(numeric,Right('000' +
          IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 1), ''),
          3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend, 4),
          ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
          3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
          Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))) Then 1
        Else 0
      End) in_ScanRange
    From (Select Top 1000000 l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid,
          s.Description,
          s.Ipstart,
          s.Ipend,
          s.Enabled
        From tsysIPScanRanges As s
          Full Join tsysIPLocations As l On
              l.endip >= Convert(numeric,Right('000' + IsNull(ParseName(s.Ipend,
              4), ''), 3) + Right('000' + IsNull(ParseName(s.Ipend, 3), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipend, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) >=
              l.startip) ips
      Left Join tblassets a On a.ipnumeric Is Not Null And (ips.locationid =
            a.locationid Or (a.ipnumeric Between Convert(numeric,Right('000' +
              IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 1), ''),
              3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend,
              4), ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
              3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
              Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))))
    Group By ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled) As allscopes

 

Go to solution
Mister_Nobody
Honored Sweeper III

3 weeks ago

Fix "between" syntax error (unknown location scanning range counter):

Select Top 1000000 allscopes.*,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then
      'Scopes names are different but scopes have same number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are same but scopes have different number of assets'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then
      'Scopes names are different and scopes have different number of assets'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'OK'
    Else 'Please fill names of scopes'
  End As Scopes_status,
  Case
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'Orange'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'LightSalmon'
    When allscopes.iplocation <> allscopes.description And
      allscopes.in_IPLocation <> allscopes.in_ScanRange Then 'Tomato'
    When allscopes.iplocation = allscopes.description And
      allscopes.in_IPLocation = allscopes.in_ScanRange Then 'LightGreen'
    Else 'skyblue'
  End As backgroundcolor
From (Select Top 1000000 ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      Sum(Case
        When a.locationid = ips.locationid Then 1
        Else 0
      End) in_IPLocation,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled,
      Sum(Case
        When (a.ipnumeric Between Convert(numeric,Right('000' +
          IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
          IsNull(ParseName(ips.Ipstart, 1), ''),
          3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend, 4),
          ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
          3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
          Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))) Then 1
        Else 0
      End) in_ScanRange
    From (Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <= l.endip
        Union
        Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          Null IPLocation,
          Null Realstart,
          Null Realend,
          Null locationid
        From tsysIPScanRanges s
        Except
        Select s.Ipstart,
          s.Ipend,
          s.Description,
          s.Enabled,
          Null IPLocation,
          Null Realstart,
          Null Realend,
          Null locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <= l.endip
        Union
        Select Null Ipstart,
          Null Ipend,
          Null Description,
          Null Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
        Except
        Select Null Ipstart,
          Null Ipend,
          Null Description,
          Null Enabled,
          l.IPLocation,
          l.Realstart,
          l.Realend,
          l.locationid
        From tsysIPLocations l
          Inner Join tsysIPScanRanges s On
              l.startip <= Convert(numeric,Right('000' +
              IsNull(ParseName(s.Ipend, 4), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 3), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 2), ''), 3) + Right('000' +
              IsNull(ParseName(s.Ipend, 1), ''), 3)) And
              Convert(numeric,Right('000' + IsNull(ParseName(s.Ipstart, 4), ''),
              3) + Right('000' + IsNull(ParseName(s.Ipstart, 3), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 2), ''), 3) +
              Right('000' + IsNull(ParseName(s.Ipstart, 1), ''), 3)) <=
              l.endip) ips
      Left Join tblassets a On a.ipnumeric Is Not Null And (ips.locationid =
            a.locationid Or (a.ipnumeric Between Convert(numeric,Right('000' +
              IsNull(ParseName(ips.Ipstart, 4), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 3), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 2), ''), 3) + Right('000' +
              IsNull(ParseName(ips.Ipstart, 1), ''),
              3)) And Convert(numeric,Right('000' + IsNull(ParseName(ips.Ipend,
              4), ''), 3) + Right('000' + IsNull(ParseName(ips.Ipend, 3), ''),
              3) + Right('000' + IsNull(ParseName(ips.Ipend, 2), ''), 3) +
              Right('000' + IsNull(ParseName(ips.Ipend, 1), ''), 3))))
    Group By ips.IPLocation,
      ips.Realstart,
      ips.Realend,
      ips.Description,
      ips.Ipstart,
      ips.Ipend,
      ips.Enabled) As allscopes
0 Kudos
Go to solution
Obi_1_Cinobi
Lansweeper Tech Support
Lansweeper Tech Support

3 weeks ago

Hello there!

Thanks for sharing your hard work!

0 Kudos

Reports & Analytics

Ask about reports you're interested in and share reports you've created. Subscribe to receive daily updates of reports shared in the Community.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now