1 Reply Latest reply on Jan 2, 2010 10:04 AM by lodo

    Kiwi Syslog -> MySQL Query

      I have been trying to convert this MS SQL query to a MySQL query and it's been driving me up the wall so any help would be really appreciated!!

      The following query only display's the total count but nothing is listed under the "outside" column, when I remove the "count" section the outside column display's the public IP address in the MsgText column.

      ---------------
      select substr(MsgText,POSITION('outside' IN MsgText)+8, POSITION('(' IN MsgText)-73) as outside, count(*) As count
      from Syslogd;
      ---------------

      The following query lists everything I need for the public IP but I can't do the following:
      -sum all public IP's
      -add a date next to the output
      -list denied port (the snippet below would be UDP/5000)

      ---------------
      select substr(MsgText,POSITION('outside' IN MsgText)+8, POSITION('(' IN MsgText)-73) as 'outside'
      from Syslogd
      where MsgText like '%PIX-2-106100%' and msgdate >= '2009-12-20' and msgtext not like '%169.254%'
      and msgtext not like '%10.1.%'
      ---------------

      Any help would be appreciated......Or any MySQL query examples would be a huge help, I'm sure plenty of peeps out there are parsing their data in MySQL:-)


      Here is a snippet of the raw data in the MsgText column:
      Dec 13 2009 07:01:12: %PIX-2-106100: access-list out denied udp outside/169.254.1.136(44890) -> outside/169.254.1.255(5000) hit-cnt 19 300-second interval [0x6a852657, 0x0]

      -lo

        • Re: Kiwi Syslog -> MySQL Query

          Thanks for the help:-\

          The following query is working for me...

          select ReceivedAt, substring(MsgText,POSITION('access' in MsgText)+35, POSITION('/'in MsgText)-17)as outside, count(*) as Hit_Count from SystemEvents
          where MsgText like'%denied%'
          and MsgText like '%OUTSIDEIPGOESHERE%'
          and ReceivedAt > '2009-01-01'
          group by substring(MsgText,POSITION('access' in MsgText)+35, POSITION('/'in MsgText)-17)
          Order By Hit_Count DESC