FortiAnalyzer- SSL VPN Bandwidth Report for Destination Address

[Jambo]

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

[Debbie_FTNT]

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]

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]

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]

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]

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.

[Debbie_FTNT]

In that case, I have no idea what's going on with your FortiAnalzyer, sorry, Jambo. When I last used the dataset and chart (granted, some years ago) I had no issues.

I can only suggest a ticket with the FortiAnalyzer team, see if they can double-check your chart.

If they say something about not supporting custom charts/datasets - that's technically true (this is handled by Professional Services, not TAC), but refer them to the forum thread here and also mention that the dataset comes from the Fortinet Developer Network, perhaps that will get them to offer at least some assistance.

Can't promise anything though - I'm helping out here as someone who's played around with FAZ a bit, and had access to the dataset I posted above, not as someone who officially supports FAZ.

[Debbie_FTNT]

You could also export the chart and post it here, maybe someone can spare the time and import the chart to their FAZ (and has logs to match into the chart) and play around with it a bit.

[Jambo]

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?extern...

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

Thanks again for your help

Jambo

[Debbie_FTNT]

Great to hear, thanks for sharing Jambo :)