4 Replies Latest reply on May 16, 2013 11:17 AM by slidermike

    Generate a report based on each month (not just the previous month)

    slidermike
    slidermike May 14, 2013 11:07 AM

      Hello,

      I have a question about an existing report I have.

      Currently the report runs based upon the current date & subtracts 1 to get the last months date range.

      It turns out my boss is NOT running the report on the 1st of each month as I told him he needed to for the results to be the previous months values.

      As a result I now have to ask for your help in getting results filtered by each month and not just the last month from when I execute the report.

      Ideally having the report run (whatever day I chose to run it) and it will generate each previous months Outage (for up to 12 months).

      Would anyone be kind enough to help me with the sql query?

      I believe the specific changes would have to impact the "eventtime between" line of code.

       

      Here is how my current report looks in sql.

      SELECT

          Nodes.Caption,

          StartTime.EventTime,

         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 (nodes.caption like '%USRTAVIN%' OR nodes.caption like '%USRTRNYNY80%' OR nodes.caption like '%USRTRLACA001%') and

      eventtime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))


      --ORDER BY Nodes.Caption ASC

      ORDER BY StartTime.EventTime ASC

      • 119 Views
      • Tags:
        • Re: Generate a report based on each month (not just the previous month)
          zzz
          zzz May 14, 2013 5:24 PM (in response to slidermike)

          This is how the built-in reports do Group by Month.

           

          SELECT

          CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101) AS SummaryMonth,

          ...

           

          GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

          ...

           

          ORDER BY SummaryMonth ASC,

          ...

           

          After that, you still need to extend the start date to what month you want to see. ie:

          DATEADD(m, DATEDIFF(m,0,DATEADD(m,-2,GETDATE())), 0)  = 1st day of 2 month ago (3/1/2013).

          • Re: Generate a report based on each month (not just the previous month)
            njoylif
            njoylif May 14, 2013 7:04 PM (in response to slidermike)

            i would suggest you schedule the report to run on the first of every month and have it automatically email your boss and copy yourself to ensure it is going out properly.

            Report scheduler can be accessed by RDPing into the primary NPM box. 

              • Re: Generate a report based on each month (not just the previous month)
                slidermike
                slidermike May 16, 2013 11:17 AM (in response to njoylif)

                You are spot on njoylif,

                That's exactly what I did.

                Created the report; had it run & email automatically to the boss on the 1st of each month.

                The issue we ran into was the boss did NOT collect the data each month & was storing the links thinking he could go back later (in this case 2 months) and choose the hyperlink to get the data at a much later date.

                 

                It turns out there is only 1 minor adjustment needed to the existing query to go back however many months (though it wont pull it for a given month only but collectively back from when you run it to x previous months.

                The issue I realized was that my DB was archiving only 30 days of the events data so I cant see anything past 30 anyway no matter what I put into the query string. Cannot pull data that isn't there.

                 

                Anyway the trick in the existing query is to change the "-1" to "-3" for the previous 3 months (after having changed the DB archiving).

                eventtime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))

                eventtime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -3 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))

                 

                Thank you guys for the help.