Skip to main content
itismo
itismo
New Member
September 13, 2019
Solved

Lost with dataset to sort top applications sorted by session count for each destination IP

  • September 13, 2019
  • 2 replies
  • 7986 views

I am trying to generate a report to show the top applications by session count and the destination IPs.

 

Essentially I have :

Dataset 1 to show top applications by sessions.

select appid, app, appcat, (case when (utmaction in ('block', 'blocked') or action='deny') then 'Blocked' else 'Allowed' end) as custaction, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth,count(*) as num_session from $log where $filter and logid_to_int(logid) not in (4, 7, 14) and nullifna(app) is not null and policyid != 0 group by appid,app, appcat,custaction order by bandwidth desc

 

Dataset 2 to show all the destination IPs by application and number of sessions.

select dstip, app, appid, hostname, count(*) as total_num from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by dstip, app, appid, hostname order by total_num desc

 

Dataset 2 does not sort the results by the *top* applications like in dataset 1 but rather by the session count, i have tried every SQL statement that can be thought of to do the sorting but i am not an expert when it comes to combining SQL queries.

 

What i want now is to combine both datasets to actually do a drill down of dataset 1.

 

Any help please?

    Best answer by AtiT

    Hello, I have a few things that you should thinking about in my opinion. ...Other ideas are also welcome...

     

    1. In dataset 1 and 2 I recommend to use this: logid_to_int(logid) not in (4, 7, 14, 20) Add the 20 logid into the list

    2. The dataset 2 is sorted by the session count because you said that you want to sor it that way: order by total_num desc In this case the whole row is tested to match another row and the exact match will increase the count number.

     

    I think that in your case you need to count the application name first to find out how many times the application is in the logs and after that sort the result... somehow. So you will have the most used applications at the top and other details will be attached to them.

    That means that yo need to use something that is called Window Function.

     

    You can try this (traffic-log):

    select `appid` as app_id,        `app` as application,        `hostname` as host_name,        `dstip` as destination,        sum(total_num) as sessions,        sum(total_num) over (partition by `app`) as app_totalnum from ###(     select `dstip`,            `app`,            `appid`,            `hostname`,            count(*) as total_num     from $log     where $filter           and logid_to_int(`logid`) not in (4, 7, 14, 20)     group by `dstip`,              `app`,              `appid`,              `hostname`     order by total_num desc )### as t group by app_id,          application,          host_name,          destination,          total_num having sum(total_num)>0 order by app_totalnum desc,          sessions desc

    Please try this dataset maybe it will be something close you need.

    The row called app_totalnum is a temporary result or "helping" result to sort the applications. Do not include this row in the chart.

     

    Please find the row: having sum(total_num)>0 In this case, when the number is 0 this row is not needed. It will be helpful in case that you will need to filter out some visits from the results. probably you will be not intrested in results that has only 1 visits, or 2 visits, maybe you want to filter out visits up to 10. In this case you can use to filter out them like: having sum(total_num)>10 I you do not need filter out anything than delete this line. There is no reason do a summary of something that is not used.

    I hope it helped in some way or at least this gave some useful information to someone.

    2 replies

    AtiT
    AtiTAnswer
    New Member
    September 16, 2019

    Hello, I have a few things that you should thinking about in my opinion. ...Other ideas are also welcome...

     

    1. In dataset 1 and 2 I recommend to use this: logid_to_int(logid) not in (4, 7, 14, 20) Add the 20 logid into the list

    2. The dataset 2 is sorted by the session count because you said that you want to sor it that way: order by total_num desc In this case the whole row is tested to match another row and the exact match will increase the count number.

     

    I think that in your case you need to count the application name first to find out how many times the application is in the logs and after that sort the result... somehow. So you will have the most used applications at the top and other details will be attached to them.

    That means that yo need to use something that is called Window Function.

     

    You can try this (traffic-log):

    select `appid` as app_id,        `app` as application,        `hostname` as host_name,        `dstip` as destination,        sum(total_num) as sessions,        sum(total_num) over (partition by `app`) as app_totalnum from ###(     select `dstip`,            `app`,            `appid`,            `hostname`,            count(*) as total_num     from $log     where $filter           and logid_to_int(`logid`) not in (4, 7, 14, 20)     group by `dstip`,              `app`,              `appid`,              `hostname`     order by total_num desc )### as t group by app_id,          application,          host_name,          destination,          total_num having sum(total_num)>0 order by app_totalnum desc,          sessions desc

    Please try this dataset maybe it will be something close you need.

    The row called app_totalnum is a temporary result or "helping" result to sort the applications. Do not include this row in the chart.

     

    Please find the row: having sum(total_num)>0 In this case, when the number is 0 this row is not needed. It will be helpful in case that you will need to filter out some visits from the results. probably you will be not intrested in results that has only 1 visits, or 2 visits, maybe you want to filter out visits up to 10. In this case you can use to filter out them like: having sum(total_num)>10 I you do not need filter out anything than delete this line. There is no reason do a summary of something that is not used.

    I hope it helped in some way or at least this gave some useful information to someone.

    itismo
    itismoAuthor
    New Member
    September 17, 2019

    AtiT, I can't thank you enough! That really worked :)

     

    I spent a lot of time trying to figure this out, how long did it take you to make this?

    AtiT
    AtiT
    New Member
    September 18, 2019

    Hello,

    yes it is different. Using the Dataset Test button the FAZ does not using cache. In reports caches are used.

     

    In our case I can see the problem. We have the sime lines multiple times so we have to summarize the results into one line.

    Try this dataset:

     

     

    select app_id,        application,        host_name,        destination,        sum(sessions) as sessions,        app_totalnum from (

        select `appid` as app_id,            `app` as application,            `hostname` as host_name,            `dstip` as destination,             sum(total_num) as sessions,             sum(total_num) over (partition by `app`) as app_totalnum

        from ###(

            select `dstip`,                `app`,                `appid`,                `hostname`,                count(*) as total_num

            from $log         where $filter               and logid_to_int(`logid`) not in (4, 7, 14, 20)         group by `dstip`,                  `app`,                  `appid`,                  `hostname`         order by total_num desc     )### as t     group by app_id,              application,              host_name,              destination,              total_num     order by app_totalnum desc,              sessions desc ) as a group by app_id,          application,          host_name,          destination,          app_totalnum having sum(sessions)>0 order by app_totalnum desc,          sessions desc

     

     

    itismo
    itismoAuthor
    New Member
    September 18, 2019

     

    That worked! You are definitely great and saved me 

     

    How long did it take you to figure those queries? Do you often need to edit/create datasets and you are already an SQL expert?

     

    I would really love to thank you personally, I will be in prague later this month :)

     

    Terms & ConditionsAccessibility statement