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!

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

SQL syntax help needed

Status
Not open for further replies.

TinkerbeII

Technical User
Dec 13, 2007
6
GB
Hello

I am using a network monitoring tool called SolarWinds Orion NPM, in which you can create reports using SQL. The report output and associated SQL I have written so far is attached.

What I need to do now is calculate the TOTAL time that the node has been down for (e.g. over the past month/week), based on the up/down time stamps in the database. If anybody thinks they may be able to help please get in touch and I can provide more info if needed.

Thanks in advance
Amy
 
For those of use with internet restrictions at work that prevent access to sites like mediafire, are you able to post code section and simplified examples in this thread?

soi là, soi carré
 
My SQL syntax so far is as follows:

Select NodeID, Event_Time, Group_Status_Icon, NodeName, EventTypeIcon, Cast(Message As nvarchar(250)) as Message From ( SELECT Nodes.NodeID AS NodeID,
Events.EventTime AS Event_Time,
Nodes.GroupStatus AS Group_Status_Icon,
Nodes.Caption AS NodeName,
( STR(Events.EventType) + '.gif') AS EventTypeIcon,
Events.EventType AS char,
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 0000 AND 40874 )
AND

(Nodes.IP_Address = 'x.x.x.x')

AND
(
(Nodes.Status = '1') OR
(Nodes.Status = '2')
)

AND
(
(Events.EventType = '1') OR
(Events.EventType = '5')
)
) As r ORDER BY 2 DESC

Thanks
 
Thanks; and you're wanting to sum EventTime? I'm a little curious as 40874 resolves to 27th November 2011 if you're counting from 1st Jan 1900 - I assume that the time element of the field is to the right of the decimal point?

soi là, soi carré
 
Yes thats correct. The output currently shows multiple lines of up/down events and I would like to get the total time that the node was in a down state.

I don't think I have put the dates correctly, I'm struggling to understand the decimal system and couldn't find a decent explanation online about it. I would like to see data for the 'last month' and for it to update automatically depending on the day the query is executed. Does that make sense?

Thanks
 
The concern I'd have is that the eventTime is merely a record of an occurence, rather than the duration. The non-integer part will be the time of day, so 40874.25 is 6 a.m. on the 27th Novmber. You may need to investigate this, else your total time calculation will be waaaay out.

soi là, soi carré
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top