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
