Database Structure to Manage IP Addresses

Question

Database Structure to Manage IP Addresses

Brody Kilpatrick

Right there with Babe

Points: 757
More actions
March 19, 2008 at 4:37 pm

#379330

I have been tasked with the creation of a database that will be sufficient for managing are infrascture IP Addresses. We currently use between 10,000 and 20,000 IP Addresses from Classes A and C, but do not know exactly how many.
I need to create a good db structure. Has anyone created one for this before, or does anyone have any ideas.
I will not be able to use 3rd party apps. I can take the db structure from the app if someone has a suggestion, but actually use the app itself.
Any help would be greatly appriciated.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 1

Your structure would depend on what questions or reports you want to produce from it.

As a starting point, a single table might be enough. In the table below, constraints help prevent duplicate IP address assignment, insure only certain values are allowed, and that all necessary fields are completed. A trigger provides the auditing capabilities.

Storing the IP address can be done a number of ways: http://sqlserver2000.databases.aspfaq.com/how-should-i-store-an-ip-address-in-sql-server.html

CREATE TABLE IPTable

(

RecordID INT IDENTITY(1,1) PRIMARY KEY,

V4IPAddress CHAR(15) UNIQUE,

MACAddr CHAR(12) NULL,

AssignedDevice VARCHAR(100) NOT NULL,

DeviceType VARCHAR(100) NOT NULL,

Building VARCHAR(100) NULL,

ModifiedDate SMALLDATETIME DEFAULT GETUTCDATE() NOT NULL,

ModifiedBy VARCHAR(50) DEFAULT SUSER_NAME() NOT NULL,

CONSTRAINT ckDeviceType CHECK ( DeviceType IN ( 'PRINTER', 'SERVER',

'WORKSTATION', 'IPPHONE',

'ROUTER', 'OTHER' ) )

)

GO

-- Audit changes to records.

CREATE TRIGGER trIPTableAudit ON IPTable FOR UPDATE, INSERT

AS

SET NOCOUNT ON

UPDATE IPTable SET ModifiedDate = GETUTCDATE(), ModifiedBy = SUSER_NAME()

FROM inserted

Brody Kilpatrick Right there with Babe Points: 757 More actions · Answer 2

Thank you very much for replying. This constraint will help.

The each subnet will have it respected owner/owners. I also need to show what device/hardware the IP holds, information about the device the ip addresses Physical address, and nothing can be deleted. It needs to go inactive. Also when someone needs to use a new IP address from a specified subnet, the ones used will not show as available.

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3

Have a look at http://qa.sqlservercentral.com/articles/Advanced+Querying/2871/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Brody Kilpatrick Right there with Babe Points: 757 More actions · Answer 4

That was a very interesting Article, it will def. help in my IP Address endeavors. I believe I know what I am going to do as far as the IP Address table itself goes, but I am still trying to figure out the best structure for additional information.