October 2, 2010 at 3:11 am
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:
ipstarttimeendtime
10.0.0.12010-09-15 10:00:002010-09-15 12:00:00
10.0.0.22010-09-15 11:00:002010-09-15 11:10:00
11.1.0.12010-09-15 13:00:002010-09-15 13:30:00
11.1.0.22010-09-15 13:20:002010-09-15 13:45:00
10.0.0.22010-09-15 13:40:002010-09-15 13:50:00
11.1.0.12010-09-15 14:10:002010-09-15 14:30:00
So here, the effective downtime will be this:
ipsstarttimeendtime
10.0.0.1-10.0.0.22010-09-15 11:00:002010-09-15 11:10:00
11.1.0.1-11.1.0.2-10.0.0.22010-09-15 13:20:002010-09-15 13:50:00
I'm trying to do this:
Save the combinations on one table like this:
ip1ip2
10.0.0.110.0.0.2
10.0.0.111.1.0.2
11.1.0.110.0.0.2
11.1.0.111.1.0.2
10.0.0.210.0.0.1
10.0.0.211.1.0.1
11.1.0.210.0.0.1
11.1.0.211.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
You are right, so there is the 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,
127.0.0.1 [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
October 2, 2010 at 3:25 am
You know, the people that help out here are all un-paid volunteers, so please HELP US HELP YOU. Providing the DDL scripts (CREATE TABLE, CREATE INDEX, etc.) for the tables affected, and INSERT statements to put some test data into those tables that shows your problem will go a long way in getting people to look at your issue and help you out. Please include code for what you have already tried. Don't forget to include what your expected results should be, based on the sample data provided. As a bonus to you, you will get tested code back. For more details on how to get all of this into your post, please look at the first link in my signature.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 3, 2010 at 11:32 am
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply