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.

SQL Script to get min/max/average bps in/out for each interface.

Hi.

I need help to find what is the sql script SQL Script to get min/max/average bps in/out for each interface directly from data base.

If I execute the sql query that report writer provides me directly on the data base , the information that I get is not the same that application casts.

I hope you can help me.

 

Thanks!

  • Hi Dfarfans--

    The information you get from RW and from the Web Console should be the same - it's just two different ways that Orion NPM presents data in the database.

    To confirm this:

    1. Go to the Min/Max/Average bps resource.
    2. Select View Chart Data from the dropdown.
    3. Compare this info with what RW generates.

    Note: you will need to compare the same time periods.

    Let me know--if the info is different, I research what to do next.

    Thanks,

    M

  • Hi MarieB.

     

    I´still haven´t found the answer to this situation.

    I am using the application for billing purposes and don´t get the same information between what appears in the excel and what I get from the database, perhaps the Application has certain algorithms to calculate the percentage of traffic every 5 minutes but simply can not find how to get those data.
    I need to know how to get MAX/MIN / Average BPS of each interface each 5 minutes directly form the data base using sql scripts.
  • Hi Marie B.

    While I haven´t solution to my problem,I wonder If you can help me or it´s better find out the solution by myself.

    I haven´t received any response from you and I´m still having this problem.

    Really, we are considering implementing another managemento tool into our network because we haven´t received help from Solarwinds even Aventiasys.

    I hope you can´t respond to this post again.

  • Hi dfarfans--

    Apologies for the delay on getting back to you. If I understandy you correctly, you need a SQL query to do what you want. This is really a question for the thwack community. However, I'll do some asking around internally and see if I can get you an answer.

    Thanks,

    M

  • Hi, you could try following SQL query:

    SELECT  TOP 10000
    DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,'20000101',[DateTime])/5.0)*5,'20000101') AS SummaryDate,
    Interfaces.FullName AS Full_Name,
    MIN(InterfaceTraffic.In_Minbps) AS MIN_of_Minimum_Receive_bps,
    AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
    MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
    MIN(InterfaceTraffic.Out_Minbps) AS MIN_of_Minimum_Transmit_bps,
    AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
    MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps

    FROM Interfaces
    INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
    GROUP BY DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,'20000101',[DateTime])/5.0)*5,'20000101'), Interfaces.FullName
    ORDER BY SummaryDate

  • Hi drarfans--

    Did you get the answer you need for this?

    M

  • Apparently we have found a solution and we will prove that solution for one month. We are going to use your sql query to bill our customers.

    I want to thank you for your help and consideration.

    If I ever have trouble again, I hope you can help me.

    Thanks a lot Marie!

  • Hi dfarfans--

    I will do my best to help you. Also, as you may know, we have a great community, so someone will definitely help.

    Best,

    M