excel wizzes / cisco stuff

[dieselboy]

is any of you a dab hand at microsoft excel?

i would like to make a small sheet that you give it a network address and subnet mask, and then it will tell you how many addresses you have, and also it will place into cells, each address you have to use, plus the broadcast address, so that in the box next to the addresses you can type what these have been allocated to?

eg, if you have a network address of 81.143.129.216, and a subnet mask of 255.255.255.248 then you have 8 addresses, the .216 being the network address, .223 being broadcast and the addresses in between being the usuable ones.

[charvell]

WTF....................

[Sancho]

WTF....................

Glad someone else said that.....

I can do a lot of stuff with excel, but I'm buggered if I know what you're talking about.

[MattF]

You're trying to do it arse ways round. There are plenty of I.P calculators already available. Just grab and use one of those. Or use pen/paper/brain.

[dieselboy]

Or use pen/paper/brain.

thats what ive been doing, but in case someone else wanted to fill out a form, i wondered if there was code i can use that would do if for them so that its all on the excel sheet to be saved / printed or whatever

oh well, no biggie, ill just post a hyperlink in the excel sheet lol

thanks though!

[Albannach]

is any of you a dab hand at microsoft excel?

i would like to make a small sheet that you give it a network address and subnet mask, and then it will tell you how many addresses you have, and also it will place into cells, each address you have to use, plus the broadcast address, so that in the box next to the addresses you can type what these have been allocated to?

eg, if you have a network address of 81.143.129.216, and a subnet mask of 255.255.255.248 then you have 8 addresses, the .216 being the network address, .223 being broadcast and the addresses in between being the usuable ones.

Where does the .223 come from?

[dieselboy]

Where does the .223 come from?

.216 =

11011|000

so .217 is
11011|001

.223 is
11011111

isnt it?

[max6674]

Just look on DHCP, it will tell you what IP had been allocated to what if your network is set up correctly.. or am I missing something?

[dieselboy]

Just look on DHCP, it will tell you what IP had been allocated to what if your network is set up correctly.. or am I missing something?

yeah im talking about when i write up documentation for each company. would be quicker if i can do network address + sub mask, and it list all the ip's so i can write next to them what is allocated all in an excel spreadsheet.

things like switches, firewalls and routers have static ips we assign.
especially on outside addresses from ISPs which is actually what the 81.x.x.x ip address is

[max6674]

And if that info gets into the wrong hands, like the goverment keeps doing with sensitive info, you could be in trouble.. why would you want to pen down the IP's of all of your kit. I wouldnt want IP's listing on a spread sheet that all and sundry could possibly look at..

[dieselboy]

And if that info gets into the wrong hands, like the goverment keeps doing with sensitive info, you could be in trouble.. why would you want to pen down the IP's of all of your kit. I wouldnt want IP's listing on a spread sheet that all and sundry could possibly look at..

i hear what your saying, but were not the sort of people to keep all of that info on a cd, and then get on public transport and leave it on the seat next to us while i go for a kip. and then wake up, leave the train without it.

its all kept secure anyways and access lists prevent even pings unless its from the right people

[Albannach]

.216 =

11011|000

so .217 is
11011|001

.223 is
11011111

isnt it?

No idea, that's why I asked. I could probably do a spreadsheet or VB app to do what you want, if I understood the question.

[dieselboy]

No idea, that's why I asked. I could probably do a spreadsheet or VB app to do what you want, if I understood the question.

its no big deal, i was just hoping while it was quiet here today that i could nick one already done and place it on my spreadsheet to help the rest of us when writing up documentation that can take a while, and stops you doin the other things..

basically what i would like to happen is that the spreadsheet would work out each address and list them. when a co. is set up, generally they get 8 public addresses - addresses that are visible from anyone on the tinternet and are routable..

to understand ip addressing there is 4 octets of binary digits. eg:
01010010.00010111.00101000.110 11000 = 82.23.40.216

the way to work it out to decimal is like this. the first 1 or 0 is valued at 128. the next is 64, then 32, 16, 8, 4, 2 and 1, so that each of the 8 digits has a value. so if you see the first octet above is 01010010;

128 64 32 16 8 4 2 1
0 1 0 1 0 0 1 0
this means 64 + 16 + 2 = 82. thats the first octet

the subnet mask defines what part is the subnet address and what are the addresses in that subnet.
to make it 8 addresses and not any more or less they use a mask of 255.255.255.248
now the .248 is the important part. this in binary is .11111000 notice the last 3 0's.
now when you put this together with the network address:
.11011000 = .216
.11111000 = .255

see the 0's line up. this is where that the .216 is a network address. and then each address would increase up til there was no more available addresses in that subnet. (.11010|000 would be a different subnet address)
so,

.11011|001 = 217
.11011|010 = 218
.11011|011 = 219
.11011|100 = 220
.11011|101 = 221
.11011|110 = 222
.11011|111 = 223 = broadcast address

if you use a different mask you can get more or less ip addresses per subnet


all i want it to do is for me to put the full ip address and it to work out all the addresses given the subnet mask, only up to about 10 at max, and then fill out cells with each address in the cell.

if i have confused you even more then ive done a good job :/

[MattF]

i hear what your saying, but were not the sort of people to keep all of that info on a cd, and then get on public transport and leave it on the seat next to us while i go for a kip. and then wake up, leave the train without it.

its all kept secure anyways and access lists prevent even pings unless its from the right people

It's pretty much irrelevant where public I.P space is concerned. Five minutes with a basic set of *nix tools and they could practically tell you what colout undies you are wearing. Plus, if you set things up correctly, the PTR and A records are giving out quite a bit of basic information to work upon, before you even start with anything else. Pings are a basic connectivity testing tool, but they are the least of your concerns if you want to keep things secure and anonymous.

[MattF]

if i have confused you even more then ive done a good job :/

So basically, you want it to list the five, (after taking the router into consideration), usable addresses?