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.

SWQL: Totalbytes transferred this/last month?

Hi, I'm trying to generate a chart showing the total number of bytes transferred over specific interfaces on a monthyly level.  I can grab this info from Orion.NPM.Interfacetraffic.  I'm having trouble figuring out how i can sum all the bytes based on the time stamp though.

My query is:

SELECT DATETRUNC('month', DATETIME) AS MonthOfYear, SUM(TotalBytes) AS Sumbytes
FROM Orion.NPM.InterfaceTraffic ONIT WHERE ONIT.Interface.Node.CustomProperties.Site LIKE 'AWS'
GROUP BY DATETIME

I think i'm on the right track, but i have no experience writing SQL let alone SWQL.  Any help would be much appreciated.

Parents
  • You look to be on the right track, but there's a few things you should take into account.'

    1. the LIKE 'AWS" will only match things that are exactly 'AWS'.  LIKE is used for wildcard matches (LIKE 'AWS%' <-- things that start with AWS, LIKE '%AWS%' <-- things that contain AWS, LIKE '%AWS' <-- Things that end with AWS).
      What you have (functionally speaking) is 'CustomPropertyName = 'AWS'.  Probably not what you are looking for.
    2. You are grouping by the DateTime field and not the collection of them.  You probably need to group by "
      DATETRUNC('month', DATETIME)" (to show you the breakdown by month)
    3. You are also going to group anything from any year for 'August' - not just the last 12 months.  Maybe you need to add an additional WHERE clause like AND [DateTime] > GETUTCDATE()-365
  • Thanks for the feedback, i made a few changes.

    SELECT DATETRUNC('month', DATETIME) AS MonthOfYear, SUM(TotalBytes) AS Sumbytes
    FROM Orion.NPM.InterfaceTraffic ONIT WHERE ONIT.Interface.Node.CustomProperties.Site = 'AWS'
    GROUP BY DATETRUNC('month', DATETIME)

    However it still treats each timestamp as a uniquerow so i just have a bunch of entries for "2021-08-01 00:00:00"

    can i Sum totalbytes by my truncated datetime?

  • SELECT DATETRUNC('month', DATETIME) AS MonthOfYear
         , SUM(TotalBytes) AS Sumbytes
    FROM Orion.NPM.InterfaceTraffic AS ONIT
    --WHERE ONIT.Interface.Node.CustomProperties.Site LIKE 'AWS'
    GROUP BY DATETRUNC('month', DATETIME)
    ORDER BY DATETRUNC('month', DATETIME)

    I don't see how that's possible.  I'm running the above query and getting this result (I don't have a custom property, so I'm pulling everything for all interfaces in a small lab).

Reply
  • SELECT DATETRUNC('month', DATETIME) AS MonthOfYear
         , SUM(TotalBytes) AS Sumbytes
    FROM Orion.NPM.InterfaceTraffic AS ONIT
    --WHERE ONIT.Interface.Node.CustomProperties.Site LIKE 'AWS'
    GROUP BY DATETRUNC('month', DATETIME)
    ORDER BY DATETRUNC('month', DATETIME)

    I don't see how that's possible.  I'm running the above query and getting this result (I don't have a custom property, so I'm pulling everything for all interfaces in a small lab).

Children
  • You're right, i must have misread the data returned.  Thanks so much for your assistance!

  • Can we get this one by nodes?  

    How much data was transmitted on a per node basis by month.  

  • Per Node is easy.  Use a Navigation Property from Orion.NPM.InterfaceTraffic to connect to Orion.NPM.Interfaces and then via another Navigation Property to the Orion.Nodes data.

    SELECT DATETRUNC('month', [ONIT].DATETIME) AS MonthOfYear
         , SUM([ONIT].TotalBytes) AS [TotalBytes]
         , [ONIT].Interface.Node.Caption AS [Node Name]
    --               ^        ^     ^
    --               |        |     |
    --               +- Connected to 'Orion.NPM.Interfaces'
    --                        |     |
    --                        +- Connected from 'Orion.NPM.Interfaces' to 'Orion.Nodes'
    --                              |
    --                              +- Field 'Caption' from 'Orion.Nodes'
    
    FROM Orion.NPM.InterfaceTraffic AS ONIT
    WHERE [ONIT].DATETIME >= GETDATE() - 365 --Only for records over the past 365 days
    GROUP BY DATETRUNC('month', [ONIT].DATETIME), [ONIT].Interface.Node.Caption
    ORDER BY DATETRUNC('month', [ONIT].DATETIME)
           , SUM([ONIT].TotalBytes) DESC