Help with dataset needed

[Mattias]

Hi there,

i need help configuring a new dataset in FortiAnalyzer 5.2.1. It is a very simple query but I don't get it running due to a lack of SQL knowledge

I need a dataset for dial-up vpn (sslvpn and ipsec vpn) which only should show username and date.

I tried with the query:

select from_dtime (dtime) as date, `xauthuser` as Benutzer, tunneltype from $log where (tunneltype= 'ssl-tunnel' or tunneltype='ipsec') and action='tunnel-up' order by date desc

Problem is that for sslvpn the user attribute is "user" and for ipsec vpn it is "xauthuser". So I need a query which gets the attribute "user" for sslvpn and attribute "xauthuser" for ipsec BUT the usernames from both sslvpn and ipsec needs to be put in the column "Benutzer".

Could you please help me? Thanks and Cheers,

Mattias

[AtiT · Accepted solution]

Hello,

How about this one?:

SELECT from_dtime (dtime) AS date, (CASE (`tunneltype`)   WHEN 'ssl-tunnel' THEN `user`   ELSE `xauthuser` END) AS Benutzer, `tunneltype` FROM $log WHERE $filter AND `subtype`='vpn' AND `action`='tunnel-up' AND `tunneltype` IN ('ssl-tunnel', 'ipsec') ORDER BY date DESC

[Mattias]

Works like a charm!

Lots of thanks @AtiT!