3 Replies Latest reply on Sep 2, 2011 10:46 AM by qle

    SQL Query for Last Month

    Shailesh

      Hi There,

       

      I have downloaded the report of Outage Duration Last month from content excahnge. When this report is run it gives the outage duration for the last 30 days and not exactly Last Month/Previous Month. I am not that good with writing sql queries and have check the date period parameter under various existing report with the last month tag...however it seems to different in each. So couldn't find a well defined repeated pattern in any of the reports. Hence posting for further advise. Below is the exisitng query of the Outage Duration Last month report....please can someone give me a query for Last/Previous Month ->

      SELECT
          StartTime.EventTime,
          Nodes.Caption,
          Nodes.Location,
          StartTime.Message,
          DATEDIFF(Mi, StartTime.EventTime,
          (SELECT TOP 1
              EventTime
              FROM Events AS Endtime
              WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
                  AND EndTime.NetObjectType = 'N'
                  AND EndTime.NetworkNode = StartTime.NetworkNode
              ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

      FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
      WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
      eventtime between dateadd(month, -1, getdate()) and getdate()
      ORDER BY Nodes.Caption ASC

      Thanks

        • Re: SQL Query for Last Month
          qle

          Try this. Change the second to last line from this

          eventtime between dateadd(month, -1, getdate()) and getdate()

          to this

          datepart(yy,eventtime) = datepart(yy,dateadd(m,-1,getdate())) and datepart(m,eventtime) = datepart(m,dateadd(m,-1,getdate()))

          Let us know if this is what you're looking for.

            • Re: SQL Query for Last Month
              Shailesh

              Hi gle,

              When I did this I got outage duration only for 2 devices (out of 250 more devices) and only 4 instances for the month of July. There are definately more. Any other query that I can try?

              Thanks

                • Re: SQL Query for Last Month
                  qle

                  I simply modified the window of the report. I assumed that the report was providing the correct information that you wanted for the last 30 days. What are the other types of devices that should've appeared in the report? Of the devices that did appear in the report, how many instances did you expect to appear?