query 2 views and merge records to one

[ba543]

Here we go I have two views that I created and one is for node_up the other is for node_down. What this is doing is calling from a syslog table all the nodes that are up in one view and all the nodes that are down in the other. the way the table works is that if a node goes down (syslog_severity=1) it post a message with the ip address, syslog_severity, datetime, and nodename. So if you were to look at the node_down view it would look like as follows:

IP_Address syslog_severity datetime nodename
10.0.16.246 1 6/12/2006 10:15:14 F-232
10.10.10.189 1 6/14/2006 18:15:14 F-545

the node_up view as follows
IP_Address syslog_severity datetime nodename
10.0.16.246 5 6/12/2006 10:20:14 F-232

What I would like to do is merge the records with the up and down datetime to one record and show the duration as follows:

IP_Address Up_datetime down_datetime nodename
10.0.16.246 6/12/2006 10:15:14 6/12/2006 10:20 F-232

I do have this part working but here is the catch the down_time record needs to follow when it comes up next and be put into a single record. BUT what happens when there is more then one updatetime and downdatetime for the same ip address for example if we did a union on the two views we would see for example

IP_Address syslog_severity datetime nodename
10.0.16.33 1 6/12/2006 10:15:14 F-444
10.0.16.33 5 6/14/2006 18:15:14 F-444
10.0.16.33 1 6/12/2006 10:35:14 F-444
10.0.16.33 5 6/14/2006 18:45:14 F-444


This sinerio screws me up because with the two views I create a join by IP_Address to connect them so they will merge to one record. Hence my output looks like this

IP_Address Up_datetime down_datetime nodename
10.0.16.33 6/12/2006 10:15:14 6/14/2006 18:15:14 F-444
10.0.16.33 6/12/2006 10:15:14 6/14/2006 18:45:14 F-444
10.0.16.33 6/12/2006 10:35:14 6/14/2006 18:15:14 F-444
10.0.16.33 6/12/2006 10:35:14 6/14/2006 18:45:14 F-444


SO as you can see we need to see the down then the next up datetime and merge them but if the node goes down more than once it screws up and begins to compare all of them. There is no other significant field to associate these objects Im wondering if this maybe more of a T-SQL type approach. The software is canned to SQL server so whatever it adds to the database it does and we dont have control. they need a report of all nodes that when up and down within a week from a single table called syslog.

If you have any recommendations that would be great

here is my sql so far

Syslog_Node_Down view:

SELECT TOP 100 PERCENT Message, SysLogSeverity, DateTime, FirstIPInMessage
FROM dbo.SysLog
WHERE (Message LIKE '%Node%Down%') AND (CAST(CONVERT(char(10), DateTime, 101) AS datetime) >= DATEADD(d, - 6, GETDATE()))
ORDER BY FirstIPInMessage, DateTime

Syslog_Node_Up view:

SELECT TOP 100 PERCENT Message, SysLogSeverity, DateTime, FirstIPInMessage
FROM dbo.SysLog
WHERE (Message LIKE '%Reset%Node%Up%') AND (CAST(CONVERT(char(10), DateTime, 101) AS datetime) >= DATEADD(d, - 6, GETDATE()))
ORDER BY FirstIPInMessage, DateTime

Merge two views:

SELECT dbo.Syslog_Node_Down.DateTime AS [Node Down Time], dbo.Syslog_Node_Up.DateTime AS [Node UP Time], dbo.Nodes.Caption, DATEDIFF(mi,
dbo.Syslog_Node_Down.DateTime, dbo.Syslog_Node_Up.DateTime) AS Duration, dbo.Nodes.IP_Address,
dbo.Syslog_Node_Down.SysLogSeverity AS syslogdown, dbo.Syslog_Node_Up.SysLogSeverity AS syslogup
FROM dbo.Nodes INNER JOIN
dbo.Syslog_Node_Up ON dbo.Nodes.IP_Address = dbo.Syslog_Node_Up.FirstIPInMessage INNER JOIN
dbo.Syslog_Node_Down ON dbo.Syslog_Node_Up.FirstIPInMessage = dbo.Syslog_Node_Down.FirstIPInMessage

 

[rac2]

A quick look at the requirement suggests this is a case of needing one row with a start and end time, but what we have available is rows for start times and rows for end times. An events table instead of a duration table.

How to match them up?

Queries can be written to do that using a condition that finds the row with the MIN(time) greater than the time in this row. Actually the MIN(time) ending greater than the time in this starting row for rows regarding the same widget, or node. But that can eat up a lot of time if a widget has many starts and stops. It is necessary to find a stop time for every start time. Also to handle those start events which do not yet have a matching stops.

That leads to the approach of writing a procedure to loop through the set of start events and find the matching stop event. This I find easier to do than the query, but that may be a matter of personal preference.


I suspect the performance cost will be about the same either way. And it can be irritating if the report is run on the spur of the moment, or repeatedly by a busy manager.

Could you add a table that is used for reporting which has columns for start and stop times. Then populate that table using one of the approaches above either on a schedule or populate it with new data only whenever the report is run.

In other words match up the start and end rows once, save the result in a table which has the structure needed for an efficient report.