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).

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

Reply Children
No Data