Find which range an IP address is in

[conanlloyd]

We have the IP Range table populated, including the starting IP and Ending IP fields.  The requirement is whenever the IP Address of a computer changes, we want to update the IP Location field with the Site Name of the corresponding range.  I wrote the following on change script but the glide lookup isn't working.  I'm assuming it's because I don't have the correct operators for finding it.  Can anyone out there help me?

function onChange(control, oldValue, newValue, isLoading, isTemplate) {
   if (isLoading || newValue === '') {
      return;
   }

//Get current IP address
  var myIP = g_form.getValue("ip_address");

//Find IP Range where current IP is between Start IP and End IP	
  var gr = new GlideRecord("ip_address_range");
  gr.addQuery("start_ip",">=",myIP);
  gr.addQuery("end_ip","<=",myIP);
  gr.query();
    if (gr.next()) {
// Update IP location with Range Site name
      g_form.setValue("u_ip_location",gr.u_site_name);   
    }
}

[Ankur Bawiskar]

Hi,

alert won't work in business rule

that script will iterate over all records of the table

ideally you should have 1 record satisfying the range and not multiple

So you should use break when it finds a match and come out of the while loop

var gr = new GlideRecord("ip_address_range");
gr.query();
while(gr.next()) {

var start = gr.start_ip;
var end = gr.end_ip;
var myIP = current.ip_address;

if(isWithinRange(myIP,start,end))
current.u_ip_location = gr.u_site_name;
break;
}

Mark ✅ Correct if this solves your issue and also mark 👍 Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[conanlloyd]

My apologies, in my tired state I missed that last alert when I was switching over to the gs.log statements.  Once I removed it, the script worked great.  Thank you for all your help!

[sridevi10]

Hi Ankur,

Can you please provide similar logic to compare IPV6 addresses.

Thanks,

sridevi

[pratiksha5]

Hi Ankur, what if I have CIDR notation, not the starting and ending IP? For eg 192.168.1.1/24 is stored in one table and when I put 192.168.1.2 that will fall in the above subnet. It should return 192.168.1.1/24

[Will Patterson]

I've run into a situation where we've got some data in our CMDB that has grown stale, however some of my other teams are not ready to have the data retired or deleted, so I've been put between a rock and a hard place where a requirement to have Location populated for Hardware classed CIs. After hours of searching, troubleshooting Discovery, and countless Community posts where someone is asking for something of this type and is met with "well, Discovery should really be populating Location". While I don't disagree with this in the slightest, sometimes business requirements outweigh the time allowed for completing something and if I can't get Discovery the ability to scan something, at least I can do my part (albeit, manually) to get the requirement fulfilled.. 

All of that said... I present to you, my best attempt at a fix script to populate Location of Hardware class CIs, with the location of a Discovery Range Item (discovery_range_item) --OR-- an IP Network (cmdb_ci_ip_network).

Currently, the Discovery Range Item query only looks at the first 3 octets, so /22s and larger will not be considered unless the IP being searched is in the first subnet of that range. This is where I could use some improvement.

The query on the IP Network table is a bit more robust because of the hi_ip * lo_ip fields. In this case, I exported/reimported my Discovery Range Items as IP Networks (for those that weren't automatically discovered) with the locations populated. Once there, all I had to do was convert the CI's IP address to a decimal and compare to hi_ip & lo_ip. As I iterate through the if statements, it will pick the smallest subnet first (/30) before considering the location populated on a large (regional in our case) subnet (up to /11).

I may come back later and edit this post after I fix a couple other things in my queue, but I'm happy to report that I'm now down to 0.42% Hardware CIs with an empty Location and have fulfilled the requirement on time. 

I hope someone else finds this useful. Please mark as helpful if so! 😄

(I am by NO means a Developer, so please be kind! 😛)

//set up first glide for hardware class table, adjust as needed
var hw = new GlideRecord('cmdb_ci_hardware');
//add query from your list view
hw.addEncodedQuery('');
//run query
hw.query();
//output number of records to be updated
gs.print(hw.getRowCount());
//iterate through the results
while (hw.next()){
    //new var for taking the hardware.ip_address and removing the last octet
    var ipVar = hw.ip_address.toString().slice(0, hw.ip_address.toString().lastIndexOf('.'));
    var ipDec = ip2int(hw.ip_address);
    var ipToLookup = ipVar + '.';
    //call lookupIP function to set hardware.location to the location of a found IP Range
    hw.location = lookupIP(ipToLookup);
    //validate that hardware.location.name is not empty
    if (hw.location.name.toString().length > 1){
        //print location name to screen for validation
        gs.print('1: ' + ipToLookup + ' found a Discovery Range Item! The location of ' + hw.name + ' will be set to: ' + hw.location.name);
        //prevent business rules from being triggered
        hw.setWorkflow(false);
        //update the CI
        hw.update();
    
    //else output info message to screen
    }else{
        //output log message stating that first lookup did not return results
        gs.print('No location found for: ' + ipToLookup + ' on the Discovery Range Item (discovery_range_item) table.');
        //clear hw.location to remove remnants of if...statement
        hw.location = null;
        //call lookupIP2 function to set hardware.location to the location of a found IP Network
        hw.location = lookupIP2(ipDec);
        //validate that hardware.location.name is not empty
        if (hw.location.name.toString().length > 1){
            //print location name to screen for validation
            gs.print('2: ' + ipDec + ' (' + hw.ip_address + ') found an IP Network! The location of ' + hw.name + ' will be set to: ' + hw.location.name);
            //prevent business rules from being triggered
            hw.setWorkflow(false);
            //update the CI
            hw.update();
        }
        else{
            gs.print('No location found for: ' + hw.ip_address + ' (' + ipDec + ') on the IP Network (cmdb_ci_ip_network) table.');
        }
    }
}
//IP lookup function (first 3 octets)
function lookupIP(lookup){
    //set up glide record for discovery range item (IP range)
    var range = new GlideRecord('discovery_range_item');
    //specify IP range query
    range.addQuery('network_ip', 'STARTSWITH', lookup);
    //run query
    range.query();
    //if range found, return found range's location sysID
    while(range.next()){
        return range.schedule.location;
    }
}
function lookupIP2(lookup){
    var network = new GlideRecord('cmdb_ci_ip_network');
    network.addEncodedQuery('location.name!=NULL^lo_ip<=' + lookup + '^hi_ip>=' + lookup);
    network.addNotNullQuery(network.location);
    network.query();
    
    while(network.next()){
        var lo_ip = network.lo_ip;
        var hi_ip = network.hi_ip;
        
        if(hi_ip - lo_ip <= 4){
            gs.print('/30 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 8){
            gs.print('/29 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 16){
            gs.print('/28 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 32){
            gs.print('/27 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 64){
            gs.print('/26 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 128){
            gs.print('/25 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 256){
            gs.print('/24 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 512){
            gs.print('/23 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 1024){
            gs.print('/22 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 2048){
            gs.print('/21 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 4096){
            gs.print('/20 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 8192){
            gs.print('/19 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 16384){
            gs.print('/18 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 32768){
            gs.print('/17 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 65536){
            gs.print('/16 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 131072){
            gs.print('/15 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 262144){
            gs.print('/14 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 524288){
            gs.print('/13 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 1048576){
            gs.print('/12 found: ' + network.subnet);
            return network.location;
        }else if (hi_ip - lo_ip <= 2097150){
            gs.print('/11 found: ' + network.subnet);
            return network.location;
        }else{
            return null;
        }
    }
}
function ip2int(ip) {
    return ip.split('.').reduce(function(ipInt, octet) { return (ipInt<<8) + parseInt(octet, 10)}, 0) >>> 0;
}