/*
**++
**
** FACILITY: IMman
**
** MODULE DESCRIPTION:
**
** This module contains a set of stored procedures to performs an IP management,
** this task is closed by next procedures:
** ADD a network range
** LIST network ranges reserved/bound subnetwork
** BIND a subnet to a platypus customer id
** UNBIND a subnet has been bound to a platypus customer id
**
** BUILD:
** $define/user ipman.sql
** $isql_connect "sa*aaa@m$server.bsod.net"
** $isql_vms
**
**
** DESIGN ISSUE:
** A main functionality is implemented by stored procedures, an every sp performs a HTML
** output.
**
** AUTHOR: Ruslan R. Laishev
**
** CREATION DATE: 8-MAY-2001
**
** MODIFICATION HISTORY:
**
** 22-MAY-2001 RRL Made a SWIP request functinality.
** Added a ip_table_jnl.
** 6-JUN-2001 RRL Added an additional checking of input parameters in the ipman_add().
** Added network range deletion.
** 7-JUN-2001 RRL A network range mask expanded to /29
** 11-JUN-2001 RRL Added a sorting in ipman_list*(), ipman_().
** 12-JUN-2001 RRL Added a send SWIP request to deallocate an unbounded network.
** 20-JUN-2001 RRL Added a network range description field, some reorganization of the main menu.
** 20-JUN-2001 RRL Added a network range description text, mark by red colour empty ranges in the report.
** 7-AUG-2001 RRL Fixed problem with network range checking.
** 3-OCT-2001 RRL Added an username column into the ip_table_jnl.
** 17-OCT-2001 RRL Some changes on visualisation, ipman_list_,ipman_list_total_.
** 26-OCT-2001 RRL Added a selection of network range in list functions.
** 28-OCT-2001 RRL Added an ability to release/unbind a subnet for non-existen custid
** in the customer table.
**
**--
*/
/*
**
** Main storage area
**
*/
CREATE TABLE ip_table (
net VARCHAR(15), /* Network range in form 172.16.0.0 */
netmask VARCHAR(4), /* Network mask in form [/]24 */
subnet VARCHAR(15) NULL, /* Subnet 172.16.1.0 */
subnetmask VARCHAR(4) NULL, /* Subnet mask = /27 */
custid INT, /* A platypus customer id */
address VARCHAR(15) /* An IP address from the network range */
UNIQUE NONCLUSTERED,
addressbin INT /* The IP address as number */
UNIQUE NONCLUSTERED,
updated DATETIME, /* A timestamp of a last change */
username VARCHAR(32) /* An username who cause the event */
)
GO
/*
**
** It's a journale table to keep an information about changes in the IP_TABLE
**
*/
CREATE TABLE ip_table_jnl (
event_time DATETIME, /* A timestamp of an event */
event_desc VARCHAR(64), /* An event description text */
username VARCHAR(32) /* An username who cause the event */
)
GO
/*
**
** Network ranges description text
**
*/
CREATE TABLE ip_table_desc (
net VARCHAR(15) /* Network range in form 172.16.0.0 */
UNIQUE NONCLUSTERED,
netmask VARCHAR(4), /* Network mask in form [/]24 */
netdesc VARCHAR(128) /* A network description text */
)
GO
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** A main procedure to build a main HTML page to perform a navigation.
**
** FORMAL PARAMETERS:
**
** None.
**
** RETURN VALUE:
**
** None.
**
**
**--
*/
DROP PROCEDURE ipman_
GO
CREATE PROCEDURE ipman_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** A main procedure to build a main HTML page to perform a navigation.
**
** FORMAL PARAMETERS:
**
** None.
**
** RETURN VALUE:
**
** None.
**
**
**--
*/
AS
DECLARE @subnet VARCHAR(15)
DECLARE @subnetmask VARCHAR(4)
DECLARE @custid INT
DECLARE @option VARCHAR(255)
DECLARE @free INT
DECLARE @buf VARCHAR(255)
DECLARE bound_cursor CURSOR
FOR SELECT DISTINCT subnet,subnetmask,custid FROM ip_table
WHERE custid <> 0 ORDER BY subnet
DECLARE netw_cursor SCROLL CURSOR
FOR SELECT DISTINCT net,netmask FROM ip_table ORDER BY net
DECLARE custid_cursor CURSOR
FOR SELECT DISTINCT custid FROM ip_table
WHERE custid <> 0 ORDER BY custid
/*
** Put out an HTML header and first part of table
*/
PRINT 'IPMan, Version 1-B'
PRINT ''
CLOSE bound_cursor
CLOSE netw_cursor
CLOSE custid_cursor
DEALLOCATE bound_cursor
DEALLOCATE netw_cursor
DEALLOCATE custid_cursor
GO
DROP PROCEDURE ipman_add_
GO
CREATE PROCEDURE ipman_add_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** Add a network range in the table, generate IP addresses from the given network
** range, insert or update a network range description text.
**
** FORMAL PARAMETERS:
**
** @netw: a network range string
** @netwbin: a network in the binary form
** @mask: a number of bits in a network mask
** @maskbin: a number of bits for the IP addresses (/28 = x0000F,/21=x007FF)
** @netdesc: a network description text
**
** RETURN VALUE:
**
** 0 - Ok.
** -1 - Operation failed, all changes rollbacked.
**
**--
*/
@netw varchar(15),
@netwbin int,
@mask varchar(15),
@maskbin int,
@netdesc varchar(128)
AS
DECLARE @count int
DECLARE @aa smallint
DECLARE @bb smallint
DECLARE @cc smallint
DECLARE @dd smallint
DECLARE @addr varchar(15)
DECLARE @buf varchar(128)
/*
** A checking of input arguments
*/
IF ( @netw = '' OR @netwbin = 0 or @mask = '')
RETURN -1
PRINT 'IPMan'
/*
** Initialization
*/
SELECT @count = 0
SELECT @addr = ''
SELECT @maskbin = @maskbin + 1
/*
** Start a local transaction
*/
BEGIN TRANSACTION ipman_add_tid
/*
** Insert of update a network range description text
*/
SELECT @count = COUNT(*) FROM ip_table_desc
WHERE net = @netw AND netmask = @mask
IF ( @count = 0 )
INSERT INTO ip_table_desc (net, netmask,netdesc)
VALUES (@netw,@mask, @netdesc)
ELSE UPDATE ip_table_desc
SET net = @netw, netmask = @mask, netdesc = @netdesc
WHERE net = @netw AND netmask = @mask
/*
** Run over an everey IP address in the given network range
** and form and put a row in the table
*/
WHILE (@count < @maskbin AND @@ERROR = 0 )
BEGIN
/*
** Divide an IP address by octets
*/
SELECT @dd = @netwbin & 0x000000FF
SELECT @cc = (@netwbin & 0x0000FF00) / 0x0000100
SELECT @bb = (@netwbin & 0x00FF0000) / 0x0010000
SELECT @aa = (@netwbin / 0x1000000) & 0xFF
/*
** Special tips for emulate usigned digits in M$
*/
IF ( @aa > 127 ) SELECT @aa = @aa - 1
/*
** Convert the IP address to string
*/
SELECT @addr = CONVERT(varchar(3),@aa)
SELECT @addr = @addr + '.' + CONVERT(varchar(3),@bb)
SELECT @addr = @addr + '.' + CONVERT(varchar(3),@cc)
SELECT @addr = @addr + '.' + CONVERT(varchar(3),@dd)
/*
** Add row with a news IP address into the table
*/
INSERT INTO ip_table (net,netmask,address,custid,addressbin)
VALUES (@netw,@mask,@addr,0,@netwbin)
IF ( @@ERROR <> 0 )
BREAK
/*
** Compute a next IP address in the given network range
*/
SELECT @netwbin = @netwbin + 1
SELECT @count = @count + 1
END
/*
** Error handling
*/
IF ( @@ERROR <> 0 )
BEGIN
ROLLBACK TRANSACTION ipman_add_tid
SELECT @buf = '
Error defining the ' + @netw + '/' + @mask +'
'
PRINT @buf
PRINT ''
RETURN -1
END
COMMIT TRANSACTION ipman_add_tid
PRINT '
IPMan'
SELECT @buf = '
The network range ' + @netw + '/' + @mask + ' has been added
'
PRINT @buf
PRINT ''
RETURN 0
GO
DROP PROCEDURE ipman_list_
GO
CREATE PROCEDURE ipman_list_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** Display a network ranges and bound/reserved subnets by using a selection
** criteria: by customer ID, by IP address. If the both is null then make a full report
** about defined network range and bound/reserved subnets.
**
** FORMAL PARAMETERS:
**
** @address: an IP address to looking for, optional
** @custid: a platypus customer id, optional
** @netr: a network range
**
** RETURN VALUE:
**
** None.
**
**--
*/
@address VARCHAR(15),
@custid INT,
@netr VARCHAR(15)
AS
DECLARE @buf VARCHAR(255)
DECLARE @net_ VARCHAR(15)
DECLARE @net VARCHAR(15)
DECLARE @netmask VARCHAR(4)
DECLARE @subnet VARCHAR(15)
DECLARE @subnetmask VARCHAR(4)
DECLARE @name VARCHAR(40)
DECLARE @updated DATETIME
DECLARE @username VARCHAR(32)
DECLARE custid_cursor CURSOR
FOR SELECT DISTINCT custid,name,subnet,subnetmask FROM ip_table,customer
WHERE custid = @custid AND customer.id = @custid ORDER BY custid
DECLARE subnet_cursor CURSOR
FOR SELECT DISTINCT custid,name,subnet,subnetmask FROM ip_table,customer
WHERE address = @address AND customer.id = ip_table.custid
DECLARE netw_cursor CURSOR
FOR SELECT DISTINCT net,netmask,subnet,subnetmask,custid,updated,username FROM ip_table
WHERE custid <> 0
ORDER BY subnet
/*
** Put out an HTML header
*/
PRINT '
IPMan'
/*
** Generate a report by using a given platypus customer id
*/
IF ( @custid <> NULL )
BEGIN
OPEN custid_cursor
FETCH NEXT FROM custid_cursor INTO @custid,@name,@subnet,@subnetmask
IF ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @buf = '
List bound subnets for customer id = ' + CONVERT(VARCHAR(8),@custid)
SELECT @buf = @buf + ' (' + @name + ')
'
PRINT @buf
SELECT @buf = '' + @subnet + '/' + @subnetmask
PRINT @buf
END
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
FETCH NEXT FROM custid_cursor INTO @custid,@name,@subnet,@subnetmask
IF ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @buf = '
' + @subnet + '/' + @subnetmask
PRINT @buf
END
END
CLOSE custid_cursor
END
/*
** Generate a report by using a given IP address
*/
IF ( @address <> NULL )
BEGIN
OPEN subnet_cursor
FETCH NEXT FROM subnet_cursor INTO @custid,@name,@subnet,@subnetmask,@updated,@username
IF ( @@FETCH_STATUS = 0 )
BEGIN
SELECT @buf = '
The IP address ' + @address + ' has been occuped by customer id = ' + CONVERT(VARCHAR(8),@custid)
SELECT @buf = @buf + ' (' + @name + ') '
PRINT @buf
SELECT @buf = 'in subnet ' + @subnet + '/' + @subnetmask + '
'
PRINT @buf
END
ELSE
BEGIN
SELECT @buf = 'The IP address ' + @address + ' has not been bound
'
PRINT @buf
END
CLOSE subnet_cursor
END
/*
** Generate a full report if the both selection criteria is NULL
*/
IF ( @address = NULL AND @custid = NULL )
BEGIN
/*
** Printout a table header
*/
PRINT 'Networks ranges allocations report
'
OPEN netw_cursor
SELECT @net_ = ''
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
/*
** Fetch data and check status
*/
FETCH NEXT FROM netw_cursor INTO @net,@netmask,@subnet,@subnetmask,@custid,@updated,@username
IF ( @@FETCH_STATUS <> 0 )
BREAK
/*
** Is this request for a particulary network range ?
*/
IF ( (@netr <> NULL) AND (@net <> @netr) )
CONTINUE
IF ( @net_ <> @net )
BEGIN
IF ( @net_ <> @net )
PRINT ''
SELECT @buf = '
Network Range = ' + @net + '/' + @netmask + '
'
PRINT @buf
PRINT ''
PRINT 'SubNets | '
PRINT 'Customer ID (Name) | '
PRINT 'Who and When |
'
END
SELECT @net_ = @net
/*
** Fetch a customer name
*/
SELECT @name = NULL
SELECT @name = name FROM customer WHERE id = @custid
SELECT @name = ISNULL(@name,'Reserved or Unknown customer')
/*
**
*/
SELECT @buf = ''
SELECT @buf = @buf + @subnet + '/' + @subnetmask + ' | '
PRINT @buf
SELECT @buf = ''
SELECT @buf = @buf + CONVERT(VARCHAR(8),@custid) + ' (' + @name + ')'
SELECT @buf = @buf + ' | '
PRINT @buf
SELECT @buf = ''
SELECT @buf = @buf + 'by ' + @username + ' at ' + CONVERT(VARCHAR(23),@updated) + ' |
'
PRINT @buf
END
CLOSE netw_cursor
PRINT '
'
/*
** Call a routine to generate a summary network range utilisation report
*/
EXEC ipman_list_total_ @netr
END
/*
** Print out a and of the HTML stream
*/
PRINT ''
/*
** Destroy all cursors data
*/
DEALLOCATE custid_cursor
DEALLOCATE subnet_cursor
DEALLOCATE netw_cursor
GO
DROP PROCEDURE ipman_bind_subnet_
GO
CREATE PROCEDURE ipman_bind_subnet_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** Bind a subnet starting with in a given networ range to customer id, or if custid is -1 just reserve
** the subnets for future use.
**
** FORMAL PARAMETERS:
**
** @netw: a network range string
** @netwbin: a network in the binary form
** @mask: a number of bits in a network mask
** @maskbin: a number of bits for the IP addresses (/28 = x0000F,/21=x007FF)
** @custid: a customer id
** @subn: a subnetwork to bind/reserver
** @subnbin: a binary representation of the subnetwork to bind/reserver
** @username: an username
**
** RETURN VALUE:
**
** 0 - Ok.
** -1 - Operation failed, all changes rollbacked.
**
** SIDE EFFECT:
**
** Store a record in the journale table.
**
**--
*/
@netw VARCHAR(15),
@netwbin INT,
@mask VARCHAR(15),
@maskbin INT,
@custid INT,
@subn VARCHAR(15),
@subnbin INT,
@username VARCHAR(32)
AS
DECLARE @aa INT
DECLARE @bb INT
DECLARE @cc INT
DECLARE @dd INT
DECLARE @subnet VARCHAR(15)
DECLARE @maxaddr INT
DECLARE @retcode INT
DECLARE @count INT
DECLARE @buf VARCHAR(255)
DECLARE @ntsnum VARCHAR(15)
DECLARE @ntenum VARCHAR(15)
DECLARE @name VARCHAR(255)
DECLARE @addr VARCHAR(255)
DECLARE @city VARCHAR(255)
DECLARE @state VARCHAR(32)
DECLARE @zip CHAR(10)
DECLARE @ntname VARCHAR(100)
PRINT '
IPMan'
/*
** Initalization, get a maximum bits reserved for the network range
*/
SELECT @count = 0
SELECT @maxaddr = CONVERT(int,netmask) FROM ip_table
WHERE net = @netw
SELECT @maxaddr = @@ROWCOUNT
/*
** Check requested size of the subnetwork
*/
IF ( @maxaddr = 0 OR @maskbin > @maxaddr )
BEGIN
SELECT @buf = 'There is not free space in the network range (requested '
SELECT @buf = @buf + CONVERT(VARCHAR,@maskbin) + ', free ' + CONVERT(VARCHAR,@maxaddr)
PRINT @buf
PRINT '), please, check input data and try again.'
PRINT '
'
RETURN -1
END
IF ( @subn <> NULL AND @subn <> '' AND @subnbin <> NULL AND @subnbin <> 0 )
SELECT @netwbin = @subnbin
/*
** Run over whole network range by steping by maskbin + 1
*/
WHILE ( @count < @maxaddr AND @@ERROR = 0 )
BEGIN
SELECT @dd = @netwbin & 0x000000FF
/*
** Skip a 172.16.1.0 and 172.16.1.255 if a signle IP address (/32) is requested
*/
if ( @maskbin = 0 AND (@dd = 255 OR @dd = 0) )
BEGIN
SELECT @netwbin = @netwbin + @maskbin + 1
CONTINUE
END
/*
** Compute a start IP address for subnet
*/
SELECT @cc = (@netwbin & 0x0000FF00) / 0x0000100
SELECT @bb = (@netwbin & 0x00FF0000) / 0x0010000
SELECT @aa = (@netwbin / 0x1000000) & 0xFF
IF ( @aa > 127 ) SELECT @aa = @aa - 1
SELECT @subnet = CONVERT(varchar(3),@aa)
SELECT @subnet = @subnet + '.' + CONVERT(varchar(3),@bb)
SELECT @subnet = @subnet + '.' + CONVERT(varchar(3),@cc)
SELECT @subnet = @subnet + '.' + CONVERT(varchar(3),@dd)
/*
** Call an allocation routine
*/
EXEC @retcode = ipman_reserve_ip_block_ @subnet,@netwbin,@mask,@maskbin,@custid,@username
/*
** If it a request for allocation of the particular subnet just end the iteration
*/
IF ( @subn <> NULL AND @subn <> '' AND @subnbin <> NULL AND @subnbin <> 0 )
BREAK
/*
** If allocation is OK, just exit
*/
IF ( @retcode = 0 )
BREAK
/*
** Allocation routine return no-success status, jump to next block
*/
SELECT @netwbin = @netwbin + @maskbin + 1
SELECT @count = @count + @maskbin + 1
END
/*
** Put the bound subnet to the customer table in ip field for the account
*/
IF ( @retcode = 0 AND @custid > 0 )
BEGIN
/*
** Got a value of the IP field
*/
SELECT @buf = ip FROM customer
WHERE id = @custid
/*
** If the old value is NULL just put a subnet/mask pair into the field
** in other case make a list by using old and new value
*/
IF ( @buf = NULL OR @buf = '' )
SELECT @buf = @subnet + '/' + @mask
ELSE SELECT @buf = @buf + ',' + @subnet + '/' + @mask
/*
** Update a table and store error code for future analyze
*/
UPDATE customer SET ip = @buf
WHERE id = @custid
SELECT @retcode = @@ERROR
END
/*
** An additional check of allocation process
*/
IF ( @custid = -1 )
SELECT @buf = ' Reservation'
ELSE SELECT @buf = ' Reservation for customer id ' + CONVERT(VARCHAR(8),@custid)
IF ( @retcode <> 0 )
BEGIN
SELECT @buf = @buf + ' ended with error status
'
END
ELSE
BEGIN
SELECT @buf = @buf + ' ended with success status,'
SELECT @buf = @buf + ' subnet is ' + @subnet + '/' + @mask + '
'
END
PRINT @buf
PRINT ''
/*
** Check retcode and make prompt to send SWIP/Assign to ARIN
*/
IF ( @retcode = 0 AND @maskbin >= 7 AND @custid > 0 )
BEGIN
/*
** Fisrt IP address of the reserved range
*/
SELECT @ntsnum = @subnet
/*
** Last IP address of the reserved range
*/
SELECT @netwbin = @netwbin + @maskbin
SELECT @dd = @netwbin & 0x000000FF
SELECT @cc = (@netwbin & 0x0000FF00) / 0x0000100
SELECT @bb = (@netwbin & 0x00FF0000) / 0x0010000
SELECT @aa = (@netwbin / 0x1000000) & 0xFF
IF ( @aa > 127 ) SELECT @aa = @aa - 1
SELECT @ntenum = CONVERT(varchar(3),@aa)
SELECT @ntenum = @ntenum + '.' + CONVERT(varchar(3),@bb)
SELECT @ntenum = @ntenum + '.' + CONVERT(varchar(3),@cc)
SELECT @ntenum = @ntenum + '.' + CONVERT(varchar(3),@dd)
/*
** Prepare a body of mail to SWIP
*/
SELECT @name = name,@addr = addr1 + addr2,
@city = city,@state = state, @zip = zip, @ntname = username
FROM customer
WHERE id = @custid
PRINT ''
END
PRINT ''
/*
** Store an event in the journal table
*/
IF ( @retcode = 0 )
BEGIN
SELECT @buf = 'Bound/Reserved subnet:' + @subnet + '/' + @mask
SELECT @buf = @buf + ', custid:' + CONVERT(VARCHAR(8),@custid)
INSERT INTO ip_table_jnl (event_time,event_desc,username)
VALUES (GETDATE(),@buf,@username)
END
PRINT ''
GO
DROP PROCEDURE ipman_unbind_subnet_
GO
CREATE PROCEDURE ipman_unbind_subnet_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** UnBind/Release a has been Bound/Reserved subnet.
**
** FORMAL PARAMETERS:
**
** @subnet: a network range string
** @subnetbin: a network in the binary form
** @mask: a number of bits in a network mask
** @maskbin: a number of bits for the IP addresses (/28 = x0000F,/21=x007FF)
** @custid: a customer id
**
** RETURN VALUE:
**
** 0 - Ok.
** -1 - Operation failed, all changes rollbacked.
**
**--
*/
@subnet VARCHAR(15),
@subnetbin INT,
@mask VARCHAR(15),
@maskbin INT,
@custid INT,
@username VARCHAR(32)
AS
DECLARE @retcode INT
DECLARE @id INT
DECLARE @pos INT
DECLARE @len INT
DECLARE @buf VARCHAR(255)
DECLARE @ntsnum VARCHAR(15)
DECLARE @ntenum VARCHAR(15)
DECLARE @name VARCHAR(255)
DECLARE @addr VARCHAR(255)
DECLARE @city VARCHAR(255)
DECLARE @state VARCHAR(32)
DECLARE @zip CHAR(10)
DECLARE @ntname VARCHAR(100)
DECLARE @addressbin INT
DECLARE unbind_cursor SCROLL CURSOR
FOR SELECT DISTINCT address,addressbin FROM ip_table
WHERE custid = @custid AND subnet = @subnet AND subnetmask = @mask
ORDER BY addressbin
/*
** Get start and end addresses of a subnet to deallocate
*/
OPEN unbind_cursor
FETCH FIRST FROM unbind_cursor INTO @ntsnum,@addressbin
FETCH LAST FROM unbind_cursor INTO @ntenum,@addressbin
CLOSE unbind_cursor
DEALLOCATE unbind_cursor
/*
** If the given @custid is not 0 or -1 check it against the customer table
*/
IF ( @custid <> 0 AND @custid <> -1 )
SELECT @id = id FROM customer WHERE id = @custid
/*
** Call a deallocation routine
*/
EXEC @retcode = ipman_release_ip_block_ @subnet,@subnetbin,@mask,@maskbin,@custid
/*
** Put the bound subnet to the customer table in ip field for the account
*/
IF ( @retcode = 0 AND @custid > 0 AND @id <> NULL )
BEGIN
/*
** Got a value of the IP field
*/
SELECT @buf = ip FROM customer
WHERE id = @custid
/*
** Search a subnet/mask pair in the IP field for the customer
*/
SELECT @pos = CHARINDEX(@subnet + '/' + @mask,@buf)
/*
** Found something ?
*/
IF ( @pos > 0 )
BEGIN
SELECT @len = DATALENGTH(@subnet + '/' + @mask)
IF ( @pos > 1 )
SELECT @buf = SUBSTRING(@buf,1,@pos-2) + SUBSTRING(@buf,@pos+@len ,255)
ELSE SELECT @buf = SUBSTRING(@buf,1,@pos-1) + SUBSTRING(@buf,@pos+1+@len ,255)
/*
** Update a table and store error code for future analyze
*/
UPDATE customer SET ip = @buf
WHERE id = @custid
SELECT @retcode = @@ERROR
END
END
/*
** An additional check of allocation process
*/
PRINT 'IPMan'
IF ( @custid = -1 )
SELECT @buf = '
Releasing'
ELSE SELECT @buf = ' Releasing of the subnetwork for customer id = ' + CONVERT(VARCHAR(8),@custid)
IF ( @retcode <> 0 )
BEGIN
SELECT @buf = @buf + ' ended with error status
'
END
ELSE
BEGIN
SELECT @buf = @buf + ' ended with success status,'
SELECT @buf = @buf + ' subnet ' + @subnet + '/' + @mask + ' has been deallocated
'
END
PRINT @buf
/*
** Check retcode and make prompt to send SWIP/DeAssign to ARIN
*/
IF ( @retcode = 0 AND @maskbin >= 7 AND @custid > 0 AND @id <> NULL )
BEGIN
/*
** Prepare a body of mail to SWIP
*/
SELECT @name = name,@addr = addr1 + addr2,
@city = city,@state = state, @zip = zip, @ntname = username
FROM customer
WHERE id = @custid
PRINT ''
END
/*
** Store an event in the journal table
*/
IF ( @retcode = 0 )
BEGIN
SELECT @buf = 'UnBound/Released subnet:' + @subnet + '/' + @mask
SELECT @buf = @buf + ', custid:' + CONVERT(VARCHAR(8),@custid)
INSERT INTO ip_table_jnl (event_time,event_desc,username)
VALUES (GETDATE(),@buf,@username)
END
PRINT ''
GO
DROP PROCEDURE ipman_reserve_ip_block_
GO
CREATE PROCEDURE ipman_reserve_ip_block_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** Bind/Reserve IP addresse range starting with a given address.
**
** FORMAL PARAMETERS:
**
** @netw: a network range string
** @netwbin: a network in the binary form
** @mask: a number of bits in a network mask
** @maskbin: a number of bits for the IP addresses (/28 = x0000F,/21=x007FF)
** @custid: a customer id
** @username: an operator name who performs a change
**
** RETURN VALUE:
**
** 0 - Ok.
** -1 - Operation failed, all changes rollbacked.
**
**--
*/
@netw VARCHAR(15),
@netwbin INT,
@mask VARCHAR(15),
@maskbin INT,
@custid INT,
@username VARCHAR(15)
AS
DECLARE @count int
DECLARE @aa int
DECLARE @bb int
DECLARE @cc int
DECLARE @dd int
DECLARE @addr varchar(15)
DECLARE @updated DATETIME
/*
** Initialization
*/
SELECT @count = 0
SELECT @addr = ''
SELECT @maskbin = @maskbin + 1
SELECT @updated = GETDATE()
/*
** Start a local transaction
*/
BEGIN TRANSACTION ipman_reserve_ip_block_tid
WHILE ( @count < @maskbin AND @@ERROR = 0 )
BEGIN
/*
** Just update a row with a particulary IP address
*/
UPDATE ip_table SET custid = @custid, subnet = @netw, subnetmask = @mask, username = @username, updated = @updated
WHERE addressbin = @netwbin AND custid = 0
IF ( @@ROWCOUNT = 0 )
BEGIN
SELECT @count = 0
BREAK
END
/*
** Jump to next IP address in the requested range
*/
SELECT @count = @count + 1
SELECT @netwbin = @netwbin + 1
END
/*
** error handling
*/
IF ( (@@ERROR <> 0) OR (@count <> @maskbin) )
BEGIN
ROLLBACK TRANSACTION ipman_reserve_ip_block_tid
RETURN -1
END
COMMIT TRANSACTION ipman_reserve_ip_block_tid
RETURN 0
GO
DROP PROCEDURE ipman_release_ip_block_
GO
CREATE PROCEDURE ipman_release_ip_block_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** UnBind/Release IP addresse range starting with a given address.
**
** FORMAL PARAMETERS:
**
** @netw: a network range string
** @netwbin: a network in the binary form
** @mask: a number of bits in a network mask
** @maskbin: a number of bits for the IP addresses (/28 = x0000F,/21=x007FF)
** @custid: a customer id
**
** RETURN VALUE:
**
** 0 - Ok.
** -1 - Operation failed, all changes rollbacked.
**
**--
*/
@netw VARCHAR(15),
@netwbin INT,
@mask VARCHAR(15),
@maskbin INT,
@custid INT
AS
/*
** Start a local transaction
*/
BEGIN TRANSACTION ipman_release_ip_block_tid
/*
** Just update all rows in a given subnet
*/
UPDATE ip_table SET custid = 0, subnet = NULL, subnetmask = NULL
WHERE subnet = @netw AND subnetmask = @mask AND custid = @custid
/*
** Error handling
*/
IF ( (@@ERROR <> 0) OR (@@ROWCOUNT = 0) )
BEGIN
ROLLBACK TRANSACTION ipman_release_ip_block_tid
RETURN -1
END
COMMIT TRANSACTION ipman_release_ip_block_tid
RETURN 0
GO
DROP PROCEDURE ipman_list_total_
GO
CREATE PROCEDURE ipman_list_total_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** Display a conslodated utilisation report, optionaly for selected network range.
**
** FORMAL PARAMETERS:
**
** netr: A network range
**
** RETURN VALUE:
**
** None.
**--
*/
@netr VARCHAR(15)
AS
DECLARE @total INT
DECLARE @bound INT
DECLARE @reserved INT
DECLARE @free INT
DECLARE @net VARCHAR(15)
DECLARE @netmask VARCHAR(4)
DECLARE @buf VARCHAR(128)
DECLARE @colour VARCHAR(16)
DECLARE range_cursor CURSOR
FOR SELECT DISTINCT net,netmask FROM ip_table ORDER BY net
/*
** Open a cursor
*/
OPEN range_cursor
IF ( @@ERROR <> 0 )
RETURN @@ERROR
/*
** Printout a table header
*/
PRINT 'Networks ranges utilisation report
'
PRINT ''
PRINT 'Network Range | '
PRINT ''
PRINT 'Bound/Reserved Free/Total IP addresses | '
PRINT ''
PRINT 'Network range description |
'
/*
** Run over defined networks ranges
*/
WHILE ( @@ERROR = 0 )
BEGIN
/*
** Get a next network range
*/
FETCH NEXT FROM range_cursor INTO @net,@netmask
IF ( @@FETCH_STATUS <> 0 )
BREAK
IF ( (@netr <> NULL) AND (@net <> @netr) )
CONTINUE
/*
** Get counters for the gottent network range
*/
SELECT @bound = count(*) from ip_table
WHERE custid > 0
AND net = @net AND netmask = @netmask
SELECT @reserved = count(*) from ip_table
WHERE custid = -1
AND net = @net AND netmask = @netmask
SELECT @free = count(*) from ip_table
WHERE custid = 0
AND net = @net AND netmask = @netmask
SELECT @total = count(*) from ip_table
WHERE net = @net AND netmask = @netmask
/*
** Formate and print out a HTML table row
*/
SELECT @colour = '#66CCCC'
IF ( @free < (@total/2) ) SELECT @colour = 'YELLOW'
IF ( @free = 0 ) SELECT @colour = 'RED'
SELECT @buf = ''
SELECT @buf = @buf + @net + '/' + @netmask + ' | '
PRINT @buf
SELECT @buf = ''
SELECT @buf = @buf + CONVERT(VARCHAR(8),@bound) + '/'
SELECT @buf = @buf + CONVERT(VARCHAR(8),@reserved) + ' '
SELECT @buf = @buf + CONVERT(VARCHAR(8),@free) + '/'
SELECT @buf = @buf + CONVERT(VARCHAR(8),@total) + ' | '
PRINT @buf
SELECT @buf = ''
PRINT @buf
SELECT @buf = netdesc FROM ip_table_desc
WHERE net = @net AND netmask = @netmask
IF ( @@ROWCOUNT <> 0 )
PRINT @buf
PRINT ' |
'
END
/*
** Close an HTML table
*/
PRINT '
'
/*
** Close and destroy cursor
*/
CLOSE range_cursor
DEALLOCATE range_cursor
GO
DROP PROCEDURE ipman_del_
GO
CREATE PROCEDURE ipman_del_
/*
**++
** FUNCTIONAL DESCRIPTION:
**
** Delete a network range from the table WITHOUT ANY COMFIRMATIONS. Delet the network range
** description text also.
**
** FORMAL PARAMETERS:
**
** @netw: a network range string
** @netwbin: a network in the binary form
** @mask: a number of bits in a network mask
** @maskbin: a number of bits for the IP addresses (/28 = x0000F,/21=x007FF)
**
** RETURN VALUE:
**
** 0 - Ok.
** -1 - Operation failed, all changes rollbacked.
**
**--
*/
@netw varchar(15),
@netwbin int,
@mask varchar(15),
@maskbin int
AS
DECLARE @buf VARCHAR(255)
PRINT 'IPMan'
/*
** A checking of input arguments
*/
IF ( @netw = '' OR @netwbin = 0 OR @mask = '' )
RETURN -1
/*
** Start a local transaction
*/
BEGIN TRANSACTION ipman_del_tid
/*
** Delete a network range description text
*/
DELETE FROM ip_table_desc
WHERE net = @netw AND netmask = @mask
/*
** DELETE ALL ROWS WITH GIVEN NETWORK RANGE
*/
DELETE FROM ip_table
WHERE net = @netw AND netmask = @mask
/*
** Error handling
*/
IF ( @@ERROR <> 0 )
BEGIN
ROLLBACK TRANSACTION ipman_del_tid
SELECT @buf = '
Error deleting the network range' + @netw + '/' + @mask +'
'
PRINT @buf
PRINT ''
RETURN -1
END
COMMIT TRANSACTION ipman_del_tid
PRINT '
IPMan'
SELECT @buf = '
The network range ' + @netw + '/' + @mask + ' has been delete
'
PRINT @buf
PRINT ''
RETURN 0
GO
/*
**++
** Just a debug stuff
**--
*/
/*
EXEC ipman_reserve_ @netw = '172.16.0.0' ,@netwbin = -1408237568
,@mask = '24' ,@maskbin = 255
EXEC ipman_
EXEC ipman_list_ '216.145.224.17',14138
EXEC ipman_list_ NULL,NULL
*/