Announcement

Collapse
No announcement yet.

SQL query help

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL query help

    Hi peeps,

    Not really the best place for it I'm sure. I need to make a SQL query on one of my devices to output to a table (I can do that bit) but have no experience in SQL queries (at all).

    Basically I want to log the time a device comes online on the network and the time it goes offline.

    Online looks like this in the logs:

    itime=2015-01-16 15:07:10 vd=root logver=52 ap=FAP01 date=2015-01-16 ssid=JTGUEST devid=FWF60CXXXX logdesc=wireless client activity msg=Client%2030%3A85%3Aa9%3A55%3A64%3A0d%20authent icated. vap=JTGUEST type=event channel=7 dtime=2015-01-16 15:07:09 devname=JTFORTIGATE stamac=30:85:a9:55:64:0d reason=Reserved 0 itime_t=1421420830 user=N/A srcip=0.0.0.0 group=N/A offset_idx=140214188345645 radioband=802.11n level=notice logid=43524 subtype=wireless sn=FAP11CXXXX time=15:07:09 action=client-authentication security=wpa2

    Offline looks like this:

    itime=2015-01-16 14:10:48 vd=root logver=52 ap=FAP01 date=2015-01-16 ssid=JTGUEST devid=FWF60CXXXX logdesc=wireless client activity msg=Client%2030%3A85%3Aa9%3A55%3A64%3A0d%20leaves% 20WTP. vap=JTGUEST type=event channel=7 dtime=2015-01-16 14:10:46 devname=JTFORTIGATE stamac=30:85:a9:55:64:0d reason=Disassociated because sending STA is leaving (or has left) BSS itime_t=1421417448 user=N/A srcip=192.168.4.20 group=N/A offset_idx=140214188345645 radioband=802.11n level=notice logid=43524 subtype=wireless sn=FAP11CXXXX time=14:10:46 action=client-leave-wtp security=wpa2

    I have some examples of how the existing queries look but nothing that will do a similar task to this.

    For example:

    Top users by bandwidth:
    Code:
    select coalesce(nullifna(`user`), nullifna(`unauthuser`), ipstr(`srcip`)) as user_src, sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0)) as bandwidth, sum(coalesce(rcvdbyte, 0)) as traffic_in, sum(coalesce(sentbyte, 0)) as traffic_out, count(*) as sessions from $log where $filter and logid_to_int(logid) not in (4, 7, 14) group by user_src having sum(coalesce(sentbyte, 0)+coalesce(rcvdbyte, 0))>0 order by bandwidth desc
    If not I'll have a lengthy chat I'm sure with the manufacturers, I'd rather avoid that though.
    TheCorsa's friendly predator

    I like my women like I like my laptop. Thin, virus free and on my lap



  • #2
    Are the logs currently written to anything linux based? This seems like it would be a lot easier to write in bash using awk etc..

    - - - Updated - - -

    But without seeing all of it and how it writes and where etc, I'm not much help tbh
    |> Spec2 R33 Skyline GTS-T <|

    Comment


    • #3
      SQL query help

      It's a proprietary device, Linux underneath but not really accessible - just certain CLI commands. I can give you access if need be - it's my own device in my house (a FortiAnalyzer)
      TheCorsa's friendly predator

      I like my women like I like my laptop. Thin, virus free and on my lap


      Comment


      • #4
        Have you tried turning it off and on again

        Comment


        • #5
          Many times
          TheCorsa's friendly predator

          I like my women like I like my laptop. Thin, virus free and on my lap


          Comment

          Working...
          X