I'm working with a company that provided me with two separate lists: one of server names and their corresponding IP addresses, and another list of networks and VLANs in CIDR notation. The server list includes entries like Server01: 192.168.10.11 and Server55: 172.16.16.78. On the other hand, the network list contains entries like 172.16.16.0/28 for DMZ and 192.168.1.1/24 for LAN. I'm trying to find out which servers belong to which VLANs but have been struggling with using Excel for this task—specifically with VLOOKUP and converting VLANs to numeric. Can anyone suggest what other options I might have? Thank you in advance!
5 Answers
Using IP Address Management (IPAM) could really streamline this for you. With Windows IPAM, you can organize your IPs and pull comprehensive reports which could make your life easier.
You might want to try calculating the IP ranges for the subnets and then match those against your server IPs. If this is feeling too overwhelming, consider involving an IT professional to help out!
For a simpler approach, you can also calculate the VLOOKUP as text without treating the IPs as numbers. Just split them by the dot and use match logic according to the subnet. It can be a bit tricky but works!
These days, you can also leverage AI tools. Just feed both lists into an AI engine, and it could do the matching for you. If you have access to paid tools like ChatGPT or Copilot, they could likely handle the task quite well. If not, a script will still get the job done!
This task is quite simple with a few lines of Python or PowerShell code. You'll just need a script that can handle CIDR and subnets. For instance, in PowerShell, you could use the Indented.Net.IP module to read from both lists and do the comparison. It's pretty straightforward once you get the hang of it!
Exactly! You could import your CSV files for servers and subnets, then loop through them to test which server belongs to which subnet. Just a bit of scripting magic! But make sure you have the right libraries to handle CIDR.
Thanks for that tip! I hadn't considered using IPAM but it sounds like a reliable solution to keep everything organized.