how to display all vpn users logged in last month

[Ralph1973]

Hello, for one of our customers I am looking for a way to see which forti softtokens have been used in the last few months.

This can help to save tokens, if they are not used, the account can be disabled.

The customer has a FortiAnalyzer and I want to generate an overview of vpn users that have logged in during the last 2 or 3 months. Better would be to see which fortitokens have only be used months ago, so these can be given out to different users.

Does anyone has a usable idea?

Thank you and regards,

Ralph

Arnhem, Netherlands

[JohnAgora]

Hello Ralph, I don't have a lab with VPN clients using FortiTokens, anyhow I'm sure you can do some search to get what you want.

Can you share some logs so we can help you improve the search?

[neonbit · Accepted solution]

There's a pre-made report called 'VPN Report' that will show all the SSL VPN users that have connected. Remember to change the time period to be 'last 3 months' before you run it.

[Ralph1973]

Hello, thanks, I have found the correct config. I hadded to alter the layout of the report to display all entries instead of the top 10 or top 20. I also noticed that the Fortitoken that is used, is not logged by the Fortigate or FAZ.

Kind regards,

Ralph

[FortiStudent]

Why will FaZ report show multiple authenticated users at the same time? For example:

" 402 ","1147240089167 QHYHAP1","ssl-tunnel","2020-01-16 06:28:54","1 ","00:03:16" " 404 ","QJLLAP1 QJPWAP1","ssl-tunnel","2020-01-15 02:08:50","1 ","08:00:15" " 409 ","QLXWAP1 QXHSAP1","ssl-tunnel","2020-01-14 06:34:40","1 ","01:48:34"

Is this a FaZ error? My DataSet looks something like this and there's nothing like this with other locations where I pulled report.

select coalesce(xauthuser_agg, user_agg, ipstr(`remip`)) as f_user, t_type as tunneltype, from_dtime(min(s_time)) as start_time, sum(total_num) as total_num, sum(duration) as duration from (select string_agg(distinct xauthuser_agg, ' ') as xauthuser_agg, string_agg(distinct user_agg, ' ') as user_agg, t_type, devid, vd, remip, tunnelid, min(s_time) as s_time, max(e_time) as e_time, (case when min(s_time)=max(e_time) then max(max_duration) else max(max_duration)-min(min_duration) end) as duration, (case when min(s_time)=max(e_time) then max(max_traffic_in)+max(max_traffic_out) else  max(max_traffic_in)-min(min_traffic_in)+max(max_traffic_out)-min(min_traffic_out) end) as bandwidth, (case when min(s_time)=max(e_time) then max(max_traffic_in) else max(max_traffic_in)-min(min_traffic_in) end) as traffic_in, (case when min(s_time)=max(e_time) then max(max_traffic_out) else max(max_traffic_out)-min(min_traffic_out) end) as traffic_out, sum(tunnelup) as total_num from ###(select devid, vd, remip, nullifna(`xauthuser`) as xauthuser_agg, nullifna(`user`) as user_agg, (case when tunneltype like 'ipsec%' then 'ipsec' else tunneltype end) as t_type, tunnelid, tunnelip,  min(coalesce(dtime, 0)) as s_time, max(coalesce(dtime, 0)) as e_time, max(coalesce(duration,0)) as max_duration, min(coalesce(duration,0)) as min_duration, min(coalesce(sentbyte, 0)) as min_traffic_out, min(coalesce(rcvdbyte, 0)) as min_traffic_in, max(coalesce(sentbyte, 0)) as max_traffic_out, max(coalesce(rcvdbyte, 0)) as max_traffic_in, sum((case when action='tunnel-up' then 1 else 0 end)) as tunnelup from $log where $filter and subtype='vpn' and (tunneltype like 'ipsec%' or tunneltype like 'ssl%') and action in ('tunnel-up', 'tunnel-stats', 'tunnel-down') and tunnelid is not null and tunnelid!=0 group by xauthuser_agg, user_agg, devid, vd, remip, t_type, tunnelid, tunnelip)### t group by t_type, devid, vd, remip, tunnelid having max(tunnelup) > 0) tt where bandwidth>0 group by f_user, tunneltype order by total_num desc