Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored procedure help

Status
Not open for further replies.
junt

junt

Programmer
Oct 2, 2010
2
IT
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 [Sad]

Here is 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



--*****
Thanks to everyone :)

 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
379
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
872
Chris Miller
Chris Miller
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
122
bmacbmac
bmacbmac
G12Consult
  • Locked
  • Question
Help with some code
Replies
2
Views
146
SaltyTheFrog
SaltyTheFrog
Cpreston
  • Locked
  • Question
Field change as caused issues to store procedure
Replies
19
Views
258
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top