Question
Fortianalyzer dataset - failed vpn logins per day
I am trying to get a report to show a count of the number of failed vpn logins per day. So far I have the below dataset which give failed logins per user, per day. I am looking to get a table with the count per day.
Querying the Event log:
select hourstamp, f_user, tunneltype, sum(total_num) as total_num from ###(select coalesce(nullifna(`xauthuser`), `user`) as f_user, $DAY_OF_MONTH as hourstamp, tunneltype, count(*) as total_num from $log where $filter and subtype='vpn' and (tunneltype='ipsec' or left(tunneltype, 3)='ssl') and action in ('ssl-login-fail', 'ipsec-login-fail') and coalesce(nullifna(`xauthuser`), nullifna(`user`)) is not null group by hourstamp, f_user, tunneltype)### t group by hourstamp, f_user, tunneltype order by total_num desc