6 Replies Latest reply on Sep 15, 2009 2:25 PM by njoylif

    3.5 SQL code/report question

    njoylif

      I have a need to look at NETFLOW IP group apps stats and I want another monitoring app to query the last 15 minutes or something so it can trend the info.

      What tables do I need to hit to get that info.  I wrote the code for 4+ hours ago *summarized), but want something more close to realtime and it needs to run quicker than hitting ALL of those tables...

      In report writer, I can do the last hour, but

      Anyone have any thoughts?

       

      SQL:::::::this gives me 5 hours to 4 hours ago.

      SELECT TOP 4
      IPAddressGroups_Dest_IPAddressGroups.IPAddressGroupName AS IP_Address_Group,
      SUM(NetflowSummary.TotalBytes) AS SUM_of_Bytes_Transferred
      FROM
      NetflowSummary LEFT OUTER JOIN IPAddressGroups IPAddressGroups_Dest_IPAddressGroups ON (NetflowSummary.DestIPSort BETWEEN IPAddressGroups_Dest_IPAddressGroups.IPRangeStart AND IPAddressGroups_Dest_IPAddressGroups.IPRangeEnd)
      WHERE
      ( DateTime between
          dateadd (hh, datepart(hour, dateadd(hh, -5,getdate() )) , convert(datetime, floor(convert(float,getdate())), 120 ))
      AND 
          dateadd (hh, datepart(hour, dateadd(hh, -4,getdate() )) , convert(datetime, floor(convert(float,getdate())), 120 ))
       )
       AND 
      (
        (IPAddressGroups_Dest_IPAddressGroups.IPAddressGroupID IS NOT NULL) AND
        (IPAddressGroups_Dest_IPAddressGroups.Enabled = 1)
      )
      AND
      (
      (EXISTS(SELECT 1 FROM NetFlowSources WITH(nolock) WHERE NetFlowSources.InterfaceID=InterfaceIDRx AND NetFlowSources.Enabled=1))
      )
      GROUP BY IPAddressGroups_Dest_IPAddressGroups.IPAddressGroupName
      ORDER BY 2 DESC

        • Re: 3.5 SQL code/report question
          njoylif

          I expect it uses different tables (not summarized) because when I try to copy the top 5 by IP addr group last 24 and change it to last hour, I get the following error:

          • Re: 3.5 SQL code/report question
            ET

            Hi,

            in ReportWriter you are able to query only compressed data, it means data, which are older than your setting Keep uncompressed data for .... .

            If your setting is 15 minutes, than you are able query data 15 minutes behind present.

              • Re: 3.5 SQL code/report question
                njoylif

                bummer, but that's kind of what I figured.

                Also, since staff responded, I would love to know how to add an index on this type of "clustered" table environment so I can speed up my queries - if you can assist with that.  I think it is already indexed on datetime, I'd like to add index on destinationIP or an IP group number column

                Thanks for the reply and any further help is greatly appreciated.

                • Re: 3.5 SQL code/report question
                  njoylif

                  another thing that just occurred to me is I have no issue (and probably was going to) running this outside of report writer.

                  Is there a way to get to that info before compression?  my raw time is 4 hours.

                  Thanks

                    • Re: 3.5 SQL code/report question
                      ET

                      indexes, hmmm nice, but I can't recommend this to you. We made a lot of testing in our lab, and if you add more indexes you will slow down insert operation proportionately. That's easy to calculate. And in case, that you are not able insert new data to DB, you start dropping and you will have gaps in charts. I'm not sure how chatty your network is, but anyway amount of data is enormous.

                       

                      And I have to disappoint you, there's no official way how to access uncompressed data in ReportWriter.