Excel sheet to convert CIDR to IP range for discovery import set

[Mike 3ra]

My client gave me an Excel sheet of CIDR networks to discover (e.g. 10.1.2.0/24) and to import into Discovery I need start and end IPs for the import set transform.

Anyone know an easy way to convert this in Excel?

[Anshu_Anand_]

Hard Way:-

The online subnet calculator will show you the first and last IP of the CIDR range

https://mxtoolbox.com/subnetcalculator.aspx

Easy Way:-

You can also achieve via javascript if you import the CIDR into ip range table and via GlideRecord and this script find start and end IP and update into starting and ending IP

function getIpRangeFromAddressAndNetmask(str) {
  var part = str.split("/"); // part[0] = base address, part[1] = netmask
  var ipaddress = part[0].split('.');
  var netmaskblocks = ["0","0","0","0"];
  if(!/\d+\.\d+\.\d+\.\d+/.test(part[1])) {
    // part[1] has to be between 0 and 32
    netmaskblocks = ("1".repeat(parseInt(part[1], 10)) + "0".repeat(32-parseInt(part[1], 10))).match(/.{1,8}/g);
    netmaskblocks = netmaskblocks.map(function(el) { return parseInt(el, 2); });
  } else {
    // xxx.xxx.xxx.xxx
    netmaskblocks = part[1].split('.').map(function(el) { return parseInt(el, 10) });
  }
  // invert for creating broadcast address (highest address)
  var invertedNetmaskblocks = netmaskblocks.map(function(el) { return el ^ 255; });
  var baseAddress = ipaddress.map(function(block, idx) { return block & netmaskblocks[idx]; });
  var broadcastaddress = baseAddress.map(function(block, idx) { return block | invertedNetmaskblocks[idx]; });
  return [baseAddress.join('.'), broadcastaddress.join('.')];
}

gs.log(getIpRangeFromAddressAndNetmask('10.1.2.0/24'));

Output:- 

10.1.2.0,10.1.2.255

If its helpful ,please mark answer as correct

[Michael de Boer]

When you use the type "IP Network" as a discovery IP range, you have the attributes "Network IP" and "Network mask".

In your case it will be:
Network IP: 10.1.2.0
Network Mask: 24

Maybe you can split the CIDR in excel to 2 new columns and import IP and mask separately.

Regards,

Michael