Fortinet Forum
The Forums are a place to find answers on a range of Fortinet products from peers and product experts.
RobertNi
New Contributor

report total amount of users on vpn

hi, I want to create a report to send me each morning about the day before (N Day) amount of VPN users.

currently, it gives me each user on its own and the details but I want to simplify the results and just know how many users connected yesterday? I am new with the analyzer as you can see, so a step by step, if someone can help me, would be great!

9 REPLIES 9
Phil_Stevens
New Contributor

I'm in the same boat. Could do with a report of the maximum concurrent number of users connected to the SSL VPN per day. If anyone has any ideas that would be great

JG67130

I'm also interested in this. I managed to create a custom Dataset with the following query:

SELECT COUNT(DISTINCT(UPPER(`user`))) FROM $log WHERE $filter AND `subtype`='vpn' AND `tunneltype`='ssl-web' AND `action`='tunnel-down'

 

When I test this dataset, I get the right answer (great!). But when I try to add the result in a report, I always get the following message in the report: "No matching log data for this report". I have another Dataset which gives me the list of all VPN-SSL connections that occured during the specified timeperiod which is working great, so I can easily work this list in Excel to get the answer by suppressing duplicate entries, but this is very strange. If anyone can help me with this, I'm interested !

 

kbar44
New Contributor

Hi I have the same problem. I need report that shows number of unique users connected to ssl vpn per day for some period of time. Example for 7 days should be like this: date       count 2020-03-31 250 2020-03-30 234 2020-03-29 111 2020-03-28 99 2020-03-27 244 2020-03-26 200 2020-03-25 189 I create dataset like this: select to_timestamp(itime)::date as date, count(distinct `user`) from $log where $filter and (tunneltype = 'ssl-tunnel' or tunneltype = 'ssl-web') and `user` != 'N/A' group by date When I do test dateset it return data like this: date     count 2020-03-27     256 2020-03-28     13 2020-03-29     27 2020-03-30     251 2020-03-31     241 2020-04-01     253 2020-04-02     219 I create 3 types of charts for this data set bar, line andt table. When I try to add the result in a report, I get the following message in the report: "No matching log data for this report" for line and table type. Bar type of chart is ok. I don't know why.

best regards

 

abelio
Valued Contributor

Hi,

try modifying your chart using:

chart type: table

data bindings with table type 'ranked'

 

I did and obtained the results you're looking for

regards


__ Abel

kbar44
New Contributor

Thank you it works fine. Do you know why the line chart can't display any results?

 

best regerds

 

rpoon
New Contributor

When I test the script, it gives me the following error:

 

ERROR: invalid input value for enum fgt_enum_type: "vpn"

LINE 4: AND "type"='vpn'

                            ^ Does someone has an idea what's wrong?

 

What I want to obtain is the chart showing the total number of VPN connections at certain point of time (8:00, 8:30, 9:00, etc).  Not sure if this is the correct script to run.

 

Thanks

Richard P.

rpoon
New Contributor

I fixed the problem by changing the Log type to Event (didn't realize the default as Application Control).

 

Still need help on creating chart showing the total number of VPN connections at certain point of time (8:00, 8:30, 9:00, etc).

 

Thanks

dieter
New Contributor

I'm not very proficient in SQL queries: what would the query have to look like to show vpn users per hour ?

Diego_Pereira

Hi I'm making this way:

 

SELECT $flex_datetime(timestamp) AS hodex,        count(DISTINCT `user`) FROM (                                    SELECT   $flex_timestamp AS timestamp,`user`                  FROM     $log                  WHERE $filter                  AND (tunneltype = 'ssl-tunnel'                  OR tunneltype = 'ssl-web')                  AND `user` != 'N/A'                  GROUP BY timestamp,`user`)### t GROUP BY hodex order by hodex

But, in my page of dataset where will make test with custom filter with more of two days the result is total sum in the each day. Instead where make with one or two days my result is amount users per hour.

 

My FortiAnalyzer is version 6.2.3 and not test in previous version.

 

CCNA | HCIA | NSE4