This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Event time - BETWEEN

WHERE
( EventTime BETWEEN 40223 AND 40254 )
 AND 
(
  (Nodes.Department = 'Airport Operational Systems') OR
  (Nodes.Department = 'Telecommunications') OR
  (Nodes.Department = 'Corporate Systems') OR
  (Nodes.Department = 'Network Operations') OR
  (Events.Message LIKE '%%has stopped responding%%') OR
  (Nodes.Escalation_Severity LIKE '%%Non-Critical%%') OR
  (Events.EventTime BETWEEN '2010-01-03 17:00:00' AND '2010-02-03 07:00:00')
)

 

Can anyone tell me why this isn't working?
When I perform this query I get I HUGE range of dates.
I have tried the 2010-01-03 in the 2010-03-01 syntax as well and get the same results. 

What I am trying to accomplish is I want to know about Non-Critical Nodes that go down between the hours of 17:00 and 07:00.  I don't know if I am trying to get this information from the wrong spot or what.

  • Can you provide a little more information?  It appears you are looking for a report that shows non-critical nodes that were down between the hours of 5:00p.m. and 7:00a.m.?  What defines a node as non-critical?  Is it the Escalation_Severity field?  Also, is there a certain time period that you want to look at with the report (i.e. last 24 hours, last week, last 30 days, etc...) 

    As for your current query...

    EventTime BETWEEN 40223 and 40254 translates to

    EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00

    sometimes it helps me to think of these types of queries as multiple individual queries in order to see the logic more clearly.  Your current query will select records where any of these individual pieces of logic are true

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Airport OPerational Systems)

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Telecommunications)

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Corporate Systems)

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (department = Network OPerations)

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (Message Like has stopped responding)

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (Escalation_Severity like Non-Critical)

    (EventTime BETWEEN 2-16-2010 00:00:00 and 3-19-2010 00:00:00) AND (EventTime BETWEEN 01-03-2010 17:00:00 and 02-03-2010 07:00:00)

  • Hey! Thanks for the reply.  I would be happy to give you some more information. 

    You're right, the "Escalation_Severity" defines the criticality of the Node. 
    There is a certain time frame that I am looking for.  it'll be in the "last month" context.

    Something I am confused about is the EventTime BETWEEN 40223 and 40254

    Are those seconds? Are you using a 3rd party tool to determine those dates or SQL or are you just a wizard with time?  I thought that me defining the hours in the EventTime BETWEEN 01-03-2010 17:00:00 and 02-03-2010 07:00:00) would override that.  Why I thought that, I don't know. 

    So what i think you're telling me that instead of defining the days and the hours that I want to report on in the "BETWEEN" that I should make it using the 40223 and 40254?

  • Adam,

    Those are dates converted to days as a number.

    you can convert these eaisily in excel by putting a date in the field and formatting it to 'number' with no decimal spaces.

  • I was going to go with the time wizard angle but I guess scottd already blew that for me ;)

    I like to use this sql to convert the numbers to time.

    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    SET @StartDate = 40223
    SET @EndDate = 40254
    select @StartDate as Start, @EndDate as Finish

    One other clarification.  Are you trying to find events that occurred between 7:00am and 5:00pm or between 5:00pm and 7:00am?

  • Time wizard.. denied! haha

    Yea, I am looking for events that took place between 5pm and 7am and also another report (though it would be cool to have it in the same one) of events that happened between 7:01 and 4:59

  • See if this is close to what you are looking for.

    WHERE 
    (EventTime BETWEEN
    dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND
    dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
    )
    AND
    (
    (CONVERT(Char, EventTime, 108)< '0600') OR
    (CONVERT(Char, EventTime, 108)> '1600')
    )
    AND
    (
    Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
    Events.Message LIKE '%has stopped responding%' AND
    Nodes.Escalation_Severity LIKE '%Non-Critical%'
    )

  • This is what is spit out:

     

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'WHERE'.

  • What I posted earlier was just the WHERE clause portion of the query.  You will also need to select the appropriate fields that you want the query to display.  Here is one possible example of a complete query.

    select Nodes.caption, events.eventtime, events.message from nodes
    join events on nodes.nodeid = events.networknode

    WHERE 
    (EventTime BETWEEN
    dateadd(mm,-1,dateadd(mm,datediff(mm,0,getdate()),0)) AND
    dateadd(ms,-3,dateadd(mm,0,dateadd(mm,datediff(mm,0,getdate()),0)))
    )
    AND
    (
    (CONVERT(Char, EventTime, 108)< '0600') OR
    (CONVERT(Char, EventTime, 108)> '1600')
    )
    AND
    (
    Nodes.Department in ('Airport Operational Systems', 'Telecommunications', 'Corporate Systems', 'Network Operations') AND
    Events.Message LIKE '%has stopped responding%' AND
    Nodes.Escalation_Severity LIKE '%Non-Critical%'
    )
    order by eventtime

  • Thank you.  I was lookin' at it, knowing that something was missing but I couldn't determine it.  As you can probably ascertain, my SQL skills are bad, at best.  I am takin' all of this as a "teach a man to fish" kinda thing.  So your patience is appreciated.

     

    so what I did was added:

    SELECT Nodes.Caption AS NodeName,
    Events.EventTime AS Event_Time

    and it's giving me grief about the "EventTime" in

    WHERE 
    (EventTime BETWEEN

    I change that EventTime to Event_Time and still got an issue

  • You will need to add something like this after your select statement and before the WHERE clause

    from nodes join events on nodes.nodeid = events.networknode

    See the complete query I posted earlier for an example.  Also, if you could post your complete query that would make troubleshooting easier.