1 Reply Latest reply on May 15, 2017 9:07 AM by bmacmt

    Using SWQL to find large consumers of bandwidth

    bmacmt

      I am learning swql trying to figure out how to determine what clients are using the bandwith within the time interval that is set for the top XX conversations.

      I have this query:

      SELECT  f.SourceIP, f.DestinationIP, f.Bytes, f.totalbytes, f.timestamp, AddHour(-6,f.timestamp) as ourtime,

      f.ingressbytes, f.destinationipgroupsegmentid, f.interfaceidrx, f.interfaceidtx, i.interfacealias, n.nodename

      FROM Orion.Netflow.Flowsbyconversation f

      inner join orion.npm.interfaces i on f.interfaceidrx = i.interfaceid

      inner join orion.nodes n on n.nodeid = i.nodeid

      WHERE

      f.TimeStamp > '2017-05-11 07:00:00' AND f.TimeStamp <= '2017-05-11 17:05:00'

      and n.nodename='switchname'

      and f.sourceip='xx.xx.xx.xx'

      and f.destinationip = 'yy.yy.yy.yy'

       

      What i cant figure out is how the interface figures out the transfer rates when you mouse over the timeline, thats the value Im looking to determine.

       

      Is there someplace solarwinds documents how to get that number? or better yet anyone have a query that shows how to do it.

       

      More info: what i am looking to figure out is which windows clients to not have their BITS rate limiting set (GPO policy) as those computers are using a large portion of the WAN pipe downloading windows updates from the WSUS server.

       

      thanks

        • Re: Using SWQL to find large consumers of bandwidth
          bmacmt

          Answering my  own post as I figured it out.

          First and formost, make sure you are looking at data from the  correct interface. :-)   (I was not..)

           

          In my case my polling interval is 30 seconds and my setting for orion is 1 minute intervals so I get two records in the orion.netflow.flowsybyconversation.

          I did a group by on the timestamp to sum the bytes value

          The next thing to do is multiply this value by 8 to get the bit count

          and then divide by the value you have for graphing (mine is 1 minute)

          this duplicated the number in the top xx conversations graph

           

          I further then used a having clause to only include those that were over 4 meg (the ones i think to look at for rate limiting)

          and then specified the hours to evaluate. Our GPO is only set to be enforced from 8am to 5pm so i only include those hours.

           

          Here is my final query: (I have links to ip groups for other reasons not included here)

          -- This will find the computers that are getting more than a 4 mg transfer of data from the WSUS server.

          -- These are the ones to review for not getting GPO to set BITS Transfer rates.

          --  The real one looks over days as these computers might have just gotten policy and its not active.

          SELECT  AddHour(-6,f.timestamp) as ourtime, hour(f.timestamp) as  hour, f.SourceIP, f.DestinationIP, sum(f.Bytes) as minBytes,

          (sum(f.bytes) * 8) / 60 as Mbps,

          f.destinationipgroupsegmentid, ig.name as ipGroup, f.interfaceidrx, i.interfacealias, n.nodename

           

          FROM Orion.Netflow.Flowsbyconversation f

          inner join orion.npm.interfaces i on f.interfaceidrx = i.interfaceid

          inner join orion.nodes n on n.nodeid = i.nodeid

          left outer join orion.netflow.ipgroupsegments igs on igs.ipsegmentid = f.destinationipgroupsegmentid

          left outer join orion.netflow.ipaddressgroups ig on ig.ipaddressgroupid = igs.ipgroupid

          WHERE f.TimeStamp > '2017-05-10 12:48:05' AND f.TimeStamp <= '2017-05-10 20:15:00'

          and f.sourceip='ip.of.ws.us'

          and n.nodename='switchnametoreview'

           

          group by AddHour(-6,f.timestamp), sourceip, destinationip, destinationipgroupsegmentid, ipgroup, interfaceidrx, interfacealias, nodename

          -- limit to only over 4mg between the hours of 8am and 5pm

          having (sum(f.bytes) * 8) / 60 > 4200000

          and hour(f.timestamp) -6 > 7 and hour(f.timestamp) -6 < 17

          order by  timestamp, destinationip