10 Replies Latest reply on May 15, 2015 10:43 AM by Martin.Krivanek

    How to "Group By" within SWQL query using filter (Limit, TopKey, etc.)

    vipriel

      I am attempting to generate the results I get from Flow Navigator when I filter by Citrix ports (1494, 2598) using an SWQL query within the SWQL Studio.

       

      I currently have the following:

       

      SELECT ApplicationName, MapTo, TotalBytes, TotalBytesIngress, TotalBytesEgress

      FROM Orion.NetFlow.ApplicationsTop(Filter='NSF:TD:2015-03-23T04:00:00~2015-03-23T19:00:00,1,True,True;FD:Both', Limit=1000, Rx=True, Tx=True, TopKey='1494')

       

      The above gives me all instances of traffic from all interfaces matching port 1494. I am wanting to do a GROUP BY clause to get the SUM of traffic based on port 1494 in one row but I can't figure out the correct syntax to do so. I can't simply do a "GROUP BY AppID" as this does not filter the results without a SUM function under the SELECT clause.

       

      Furthermore, I don't know how to filter by both port 1494 and 2598 using the filter above (TopKey does not accept more than one value).

       

      Finally, I would love to be able to use DATEPART commands in the query like the following in order to exclude non-business hours :

      (

         (DATEPART(weekday, DateTime) > 1) AND

         (DATEPART(weekday, DateTime) < 7) AND

         (DatePart(Hour,DateTime) >= 4) AND

         (DatePart(Hour,DateTime) <= 19)

      )

      However, WSQL Studio states "Unable to resolve property weekday".

       

      Any assistance would be greatly appreciated.

        • Re: How to "Group By" within SWQL query using filter (Limit, TopKey, etc.)
          tom.rybka

          Hi vipriel,

           

          I have forwarded your question to the NTA development team and they will come up with an updated query soon.

           

          Regarding a function that would allow filtering by work days, unfortunately SWIS does not provide any support at the moment.

           

          Tom

            • Re: How to "Group By" within SWQL query using filter (Limit, TopKey, etc.)
              vipriel

              Tom,

               

              Thank you so much for the reply. Martin Krivanek already provided an updated query for me. Just for clarification regarding your answer, is there no way to utilize DATEPART functions within SWQL or will DATEPART functions simply not work in conjunction with the filters used within the SWIS API?

               

              The DATEPART function that I wrote down in the thread I created was directly from a SQL report that we used with Report Writer. It worked great but of course can’t query NTA using Report Writer.

                • Re: How to "Group By" within SWQL query using filter (Limit, TopKey, etc.)
                  Craig Norborg

                  SWQL is definitely different than SQL.  Refer to the Orion SDK.pdf document included with the SDK for what functions are or are not available.  Someone did help me once with figuring out the whole day of the week thing though.   It was in this discussion and if I remember right it did work, although I never took the time to figure out why...

                   

                  Help with SWQL - Day of the week?

                   

                  HTH!

                    • Re: How to "Group By" within SWQL query using filter (Limit, TopKey, etc.)
                      vipriel

                      Craig,

                       

                      Thank you for the reply. I have a working report now that works in SWQL Studio thanks to Martin that gave me syntax to filter by ports and if I can get the query to run as a web report (it currently is not, as it’s coming up as a not valid query when pasted from SWQL Studio), I wouldn't necessarily need the DATEPART functions. It’s possible to set a custom time duration in a web report (in my case I define the time period to be 17 hours earlier) and then schedule the report to run at a specific time during the days that you want (there is a Business Hours option under the scheduling for M-F) so when I combine the Business Hours duration with the daily schedule, I get exactly the days and hours I need for the report. The trick is to find out why the report is running in SWQL Studio but not as a web report when the Advanced Query (SQL, SWQL) option is selected.

                • Re: How to "Group By" within SWQL query using filter (Limit, TopKey, etc.)
                  Martin.Krivanek

                  Hi vipriel,

                   

                  If you're looking to get total traffic instead of interface breakdown, then you can add port numbers directly into the filter as follows:

                   

                  SELECT ApplicationName, MapTo, TotalBytes, TotalBytesIngress, TotalBytesEgress

                  FROM Orion.NetFlow.ApplicationsTop(Filter='NSF:TD:2015-03-23T04:00:00~2015-03-23T19:00:00,1,True,True;FD:Both;A:1494,2598', Limit=1000, Rx=True, Tx=True, TopKey='')

                   

                  Note that top key is empty and application/port filter was added into filter string. This might still give you two rows in results. I suggest to create Citrix application with both ports - this multiport application will have its own application ID, which you can then use instead of two port numbers in above query (like this A:123456). Check Orion.NetFlow.Application entity to find an application ID.

                   

                  This should get you going, let us know if you need anything else.

                   

                  Btw. which NTA version do you have? Since 4.0 release NTA supports different entity model, which is more user friendly.

                   

                  Martin