Skip to main content
Mattias
Mattias
New Member
March 16, 2015
Solved

Help with dataset needed

  • March 16, 2015
  • 2 replies
  • 3144 views

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

    Best answer by AtiT

    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

    2 replies

    AtiT
    AtiTAnswer
    New Member
    March 16, 2015

    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
    MattiasAuthor
    New Member
    March 16, 2015

    Works like a charm!

     

    Lots of thanks @AtiT!

    Terms & ConditionsAccessibility statement