Skip to main content
Jambo
Jambo
New Member
February 7, 2022
Question

FortiAnalyzer- SSL VPN Bandwidth Report for Destination Address

  • February 7, 2022
  • 3 replies
  • 8053 views

Hi 

 

I have a requirement to determine what amount of bandwidth is being used by SSL VPN users for internal addresses versus external addresses, however I don't see a dataset on FortiAnalyzer 6.4.7 that will allow me to achieve this.

 

I'd be extremely grateful if someone could  offer any advice to someone who is new to FortiAnalyzer and SQL on how I might achieve this?

 

Thanks in advance

3 replies

Debbie_FTNT
Staff & Editor
Debbie_FTNT
Staff & Editor
February 7, 2022

Hey Jambo,

there is a custom SQL dataset that a colleague wrote some years ago:

 

Dataset:

(log type: Event)

SELECT a.`user`,a.login,b.logout,b.duration,b.transferred_bytes
FROM ###( SELECT `user`,min(from_dtime(dtime)) AS login,tunnelid
FROM $log-event
WHERE action='tunnel-up' and $filter
GROUP BY `user`,tunnelid)### a,
###( SELECT max(from_dtime(dtime)) AS logout,duration,sum(sentbyte+rcvdbyte)AS transferred_bytes,
tunnelid
FROM $log-event
WHERE action='tunnel-down' and $filter
GROUP BY duration,tunnelid
HAVING sum(sentbyte+rcvdbyte)>0 )### b
WHERE a.tunnelid=b.tunnelid
ORDER BY login,
`user`


If you add the dataset to a chart like in the attached screenshot, that should result in a chart which gives you username, login time, logout time, and total bandwidth for each tunnel.
You could apply filters on the report level for destination IPs to get results for internal vs external IPs.

Jambo
JamboAuthor
New Member
February 7, 2022

Thanks @Debbie_FTNT that is very helpful,

 

When you say "filters on the report level for destination IPs" do you mean add the filter on the actual report under the 'Settings' tab, rather than on the individual chart within the report?

 

Also, if I have a specific list of VPN users that I want to find this information for, would we need to modify the provided SQL dataset (and if so how would I do this)? Or would I again use filters ?

 

Much Appreciated

Jambo

 

Jambo
JamboAuthor
New Member
February 8, 2022

Hi 

 

When I test the dataset, I can see that it returns the kind of information that I require. However when I associate it with a graph, as instructed above, that graph shows no information when the the report is generated. I have tried this both with and without more specific filters at the report level. I'm unsure why that would be the case?

 

Would be grateful if anyone could offer any assistance

Thanks

 

 

Debbie_FTNT
Staff & Editor
Debbie_FTNT
Staff & Editor
February 8, 2022

Hey Jambo,

it's difficult to say why you wouldn't get output in the report when you do get information from the chart - perhaps one of the data bindings or the binding format is incorrect?

Regarding your earlier question on filtering - you can do per-report or per-chart filtering, you do NOT need to edit the dataset - the 'where $filter' variable is a placeholder for the report/chart filters.

Jambo
JamboAuthor
New Member
February 8, 2022

Thanks for getting back to me and clarifying about the filters.

Really cannot see why the report isn't showing me data. The data bindings and binding format are definitely as shown in the screenshot that you attached.

Jambo
JamboAuthor
New Member
February 18, 2022

Hi @Debbie_FTNT I figured out why my report chart showed no data. It is a bug which was replicated by TAC related to whether the report is being run against all devices or a specific VDOM. The report shows no data when a specific VDOM is selected but shows the correct data if the same report is run against all VDOMs. 

 

The diagnostic log was useful in this case

 

https://community.fortinet.com/t5/FortiAnalyzer/Troubleshooting-Tip-Empty-reports/ta-p/197557?externalID=FD53371

 

Thought I'd post in case it useful to anyone else on here.

Thanks again for your help

Jambo

Debbie_FTNT
Staff & Editor
Debbie_FTNT
Staff & Editor
February 21, 2022

Great to hear, thanks for sharing Jambo :)

Terms & ConditionsAccessibility statement