cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Advanced SQL

How can I join this 2 scripts to create a report with Event time and the currently down nodes..

 

SELECT
Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Status AS Status, Interfaces.InterfaceName AS Interface_Name, Interfaces.Status AS Status_1, Nodes.StatusDescription AS Status_Description, Interfaces.InterfaceLastChange AS Interface_Last_Change
 FROM
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
 WHERE 
(
  (Nodes.Status = '2') OR
  (Interfaces.Status = '2')
)

 

and

 

SELECT Nodes.NodeID AS NodeID,
Events.EventTime AS Event_Time,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
( STR(Events.EventType) + '.gif') AS EventTypeIcon,
Events.Message AS Message

FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)


WHERE
( EventTime BETWEEN 39552 AND 39918 )
 AND 
(
  (Events.EventType = 1)
)



ORDER BY 2 DESC

0 Kudos
2 Replies
Level 15

Hi,

Is the following query returning what you need?

------------------------------------------

SELECT Nodes.NodeID AS NodeID,
Events.EventTime AS Event_Time,
Nodes.Caption AS NodeName,
Nodes.IP_Address AS IP_Address,
( STR(Events.EventType) + '.gif') AS EventTypeIcon,
Events.Message AS Message

FROM
Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID


WHERE
( EventTime BETWEEN 39552 AND 39918 )
 AND
(
  (Events.EventType = 1)
)
AND Nodes.Status = 2 AND Interfaces.Status = 2


ORDER BY 2 DESC

----------------------------------------

Note that the EventTime BETWEEN 39552 AND 39918 is a constant in your query and means:

39552 => 14 April 2008

39918 => 15 April 2008

(you can use a spreadsheet application to transform the values)

Have a look at the following post and the DateDiff statements to replace your conditon:


HTH,

Yann

0 Kudos

Thanks for your your help.

 

We need to display a report with fields event time and node name every 9am and 3pm daily. Is this possible?

0 Kudos