help with stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with stored procedure

Author

Topic

junt
Starting Member

2 Posts

Posted - 2010-10-02 : 05:17:04

Hello, I have a difficult task to do, I have the output from a network monitoring tool and i must extract the effective downtime from it.

There are 4 ip addresses with the downtime for each one (one record for an ip downtime). These ips belongs to 2 routers that are balanced.

10.0.0.1 = interface of the first router
10.0.0.2 = interface of the second router
11.1.0.1 = first node
11.1.0.1 = second node

So if only one of these ips is down, there is no down of service.
The effective down of service will appear if one of the combinations is verified:
10.0.0.1 - 10.0.0.2
10.0.0.1 - 11.1.0.2
11.1.0.1 - 10.0.0.2
11.1.0.1 - 11.1.0.2

The extract from the tool will be something like this:
ip starttime endtime
10.0.0.1 2010-09-15 10:00:00 2010-09-15 12:00:00
10.0.0.2 2010-09-15 11:00:00 2010-09-15 11:10:00
11.1.0.1 2010-09-15 13:00:00 2010-09-15 13:30:00
11.1.0.2 2010-09-15 13:20:00 2010-09-15 13:45:00
10.0.0.2 2010-09-15 13:40:00 2010-09-15 13:50:00
11.1.0.1 2010-09-15 14:10:00 2010-09-15 14:30:00

So here, the effective downtime will be this:

ips starttime endtime
10.0.0.1-10.0.0.2 2010-09-15 11:00:00 2010-09-15 11:10:00
11.1.0.1-11.1.0.2-10.0.0.2 2010-09-15 13:20:00 2010-09-15 13:50:00

I'm trying to do this:
Save the combinations on one table like this:
ip1 ip2
10.0.0.1 10.0.0.2
10.0.0.1 11.1.0.2
11.1.0.1 10.0.0.2
11.1.0.1 11.1.0.2
10.0.0.2 10.0.0.1
10.0.0.2 11.1.0.1
11.1.0.2 10.0.0.1
11.1.0.2 11.1.0.1

Open a cursor for each line from the data extracted from the tool ordered by starttime and endtime.

Open a second cursor for the same data.

Fetch the first cursor, and in this cursor fetch the second one.

Check if the starttime of the second cursor is between starttime and endtime of the first one and that the endtime of the second cursor is minor that endtime of the first one.

If that condition is verified i make a select on the combination table to check if the ips from the two cursors are a combination to use.

But now i'm blocked, do you think that this is a good way to follow?

Anyone has a more brilliant idea to solve that problem?

To solve this task I can use SSIS, but I haven't found a way to use them for this :(

Thanks to everyone :)

****EDIT

I'm attaching some code :)

The stored procedure is incomplete / wrong, it must extract only a record when there is a situation like this:

if1 down from 10.00-12.00
if2 down from 10.10-11.00
node2 down from 10.50-11.30

the down time will be from 10:10 to 11:30..

so it's a bit complicated to extract..

TLC contains some data, COMBINATIONS contains the ip combinations and RESULTS contains the wanted result

GO

/****** Object: Table [dbo].[TLC] Script Date: 10/02/2010 12:34:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TLC](
[id] [int] IDENTITY(1,1) NOT NULL,
[ip] [varchar](50) NULL,
[starttime] [datetime] NULL,
[endtime] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

GO

/****** Object: Table [dbo].[COMBINATIONS] Script Date: 10/02/2010 13:12:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[COMBINATIONS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ip1] [varchar](50) NULL,
[ip2] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

/****** Object: Table [dbo].[RESULTS] Script Date: 10/02/2010 13:21:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[RESULTS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DownStart] [datetime] NULL,
[DownEnd] [datetime] NULL
) ON [PRIMARY]

GO

INSERT INTO TLC (ip, starttime, endtime) VALUES ('10.0.0.1','2010-09-15 10:00:00','2010-09-15 12:00:00')
INSERT INTO TLC (ip, starttime, endtime) VALUES ('10.0.0.2','2010-09-15 11:00:00','2010-09-15 11:10:00')
INSERT INTO TLC (ip, starttime, endtime) VALUES ('11.1.0.1','2010-09-15 13:00:00','2010-09-15 14:30:00')
INSERT INTO TLC (ip, starttime, endtime) VALUES ('11.1.0.2','2010-09-15 13:20:00','2010-09-15 13:45:00')
INSERT INTO TLC (ip, starttime, endtime) VALUES ('10.0.0.2','2010-09-15 13:40:00','2010-09-15 13:50:00')
INSERT INTO TLC (ip, starttime, endtime) VALUES ('11.1.0.1','2010-09-15 16:10:00','2010-09-15 16:30:00')

INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('10.0.0.1','10.0.0.2')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('10.0.0.1','11.1.0.2')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('11.1.0.1','10.0.0.2')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('11.1.0.1','11.1.0.2')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('10.0.0.2','10.0.0.1')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('11.1.0.2','10.0.0.1')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('10.0.0.2','11.1.0.1')
INSERT INTO COMBINATIONS (ip1,ip2) VALUES ('11.1.0.2','11.1.0.1')

INSERT INTO RESULTS (DownStart, DownEnd) VALUES ('2010-09-15 11:00:00','2010-09-15 11:10:00')
INSERT INTO RESULTS (DownStart, DownEnd) VALUES ('2010-09-15 13:20:00','2010-09-15 13:50:00')

-- the sp...

-- some declarations..

DECLARE extcursor CURSOR FOR

SELECT

ip,

starttime,

endtime

FROM

TLC

ORDER BY

starttime,

endtime

DECLARE intcursor CURSOR FOR

SELECT

ip,

starttime,

endtime

FROM

TLC

ORDER BY

starttime,

endtime

DECLARE @extip varchar(50)

DECLARE @extstarttime datetime

DECLARE @extendtime datetime

DECLARE @intip varchar(50)

DECLARE @intstarttime datetime

DECLARE @intendtime datetime

DECLARE @realdownstart datetime

DECLARE @realdownend datetime

-- open the external cursor

OPEN extcursor

FETCH NEXT FROM extcursor INTO @extip, @extstarttime, @extendtime

WHILE @@FETCH_STATUS = 0

BEGIN

-- open the internal cursor

OPEN intcursor

FETCH NEXT FROM intcursor INTO @intip, @intstarttime, @intendtime

WHILE @@FETCH_STATUS = 0

BEGIN

-- check if the start time of the internal cursor is between start time and end time of the external cursor

IF

@intstarttime >= @extstarttime

AND

@intstarttime <= @extendtime

BEGIN

-- if so check if the ips of internal and external cursors are present in combinations table

IF

(SELECT COUNT(*) FROM COMBINATIONS WHERE ip1 = @extip AND ip2 = @intip) = 1

BEGIN

-- if so there is a real down, so i check that the end time of the internal cursor is minor that the end time of the external one

IF

@intendtime <= @extendtime

BEGIN

-- if so the real down is between internal cursor start time and internal cursor end time

SET @realdownstart = @intstarttime

SET @realdownend = @intendtime

SELECT 1,@realdownstart, @realdownend

END

ELSE

-- if not the real down is between internal cursor start time and external cursor end time // IS THAT RIGHT??

SET @realdownstart = @intstarttime

SET @realdownend = @extendtime

SELECT 2,@realdownstart, @realdownend

END

END

FETCH NEXT FROM intcursor INTO @intip, @intstarttime, @intendtime

END

CLOSE intcursor

FETCH NEXT FROM extcursor INTO @extip, @extstarttime, @extendtime

END

CLOSE extcursor

DEALLOCATE extcursor

DEALLOCATE intcursor

junt
Starting Member

2 Posts

Posted - 2010-10-03 : 13:32:04

maybe i solved, if anyone is interested or want to see if there are some bugs here is the code:

DECLARE extcursor CURSOR FOR

SELECT

ip,

starttime,

endtime

FROM

TLC

ORDER BY

starttime,

endtime

DECLARE intcursor CURSOR FOR

SELECT

ip,

starttime,

endtime

FROM

TLC

ORDER BY

starttime,

endtime

DECLARE @extip varchar(50)

DECLARE @extstarttime datetime

DECLARE @extendtime datetime

DECLARE @intip varchar(50)

DECLARE @intstarttime datetime

DECLARE @intendtime datetime

DECLARE @realdownstart datetime

DECLARE @realdownend datetime

CREATE TABLE #Down (

DataStart datetime,

DataEnd datetime

)

OPEN extcursor

FETCH NEXT FROM extcursor INTO @extip, @extstarttime, @extendtime

WHILE @@FETCH_STATUS = 0

BEGIN

OPEN intcursor

FETCH NEXT FROM intcursor INTO @intip, @intstarttime, @intendtime

WHILE @@FETCH_STATUS = 0

BEGIN

IF

@intstarttime >= @extstarttime

AND

@intstarttime <= @extendtime

AND

(SELECT COUNT(*) FROM COMBINATIONS WHERE (ip1 = @extip AND ip2 = @intip) OR (ip2 = @extip AND ip1 = @intip)) = 1

BEGIN

IF

@intendtime <= @extendtime

AND

@realdownstart IS NULL

AND

@realdownend IS NULL

BEGIN

SET @realdownstart = @intstarttime

SET @realdownend = @intendtime

END

ELSE

IF

@intstarttime > @realdownstart

AND

@intstarttime <= @realdownend

AND

@intendtime > @realdownend

BEGIN

SET @realdownend = @intendtime

END

END

FETCH NEXT FROM intcursor INTO @intip, @intstarttime, @intendtime

END

CLOSE intcursor

IF

@realdownstart IS NOT NULL

AND

@realdownend IS NOT NULL

BEGIN

INSERT INTO

#Down

(DataStart, DataEnd)

VALUES (

@realdownstart,

@realdownend

)

END

SET @realdownstart = NULL

SET @realdownend = NULL

FETCH NEXT FROM extcursor INTO @extip, @extstarttime, @extendtime

END

CLOSE extcursor

DEALLOCATE extcursor

DEALLOCATE intcursor

SELECT * FROM #Down

DROP TABLE #Down

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources