Multiply substring commands in one query

[-lodogg-]

So the following command is working great for me but I want to pull and parse data twice in one query with the sustring command. I have a good bit of syslog data being dumped into one field and I need to run multiple substring commands to print it correctly. Hopefully I will find help here because the MySQL forums where of no help at all:-\

select ReceivedAt, substring(Message,POSITION('access' in Message)+35, POSITION('/'in Message)-17)as Outside 
from SystemEvents 
where Message like'%denied%' 
and DATE(ReceivedAt) = DATE(NOW()) 
order by ReceivedAt DESC

Output Looks Like

ReceivedAt                  Outside 
2010-01-04 13:50:40        219.136.22.29(80) 
2010-01-04 12:22:40        66.136.42.29(80)

------------

I would like to pull data twice from the Message field with the following command but when I run the query I get the following error.
(Operand should contain 1 column(s)Error# 1241)

select ReceivedAt, substring(Message,POSITION('access' in Message)+35, POSITION('/'in Message)-17)as Outside, (Message,POSITION('access' in Message)+20, POSITION('/'in Message)-15)as Port 
from SystemEvents; 
where Message like'%denied%' 
and DATE(ReceivedAt) = DATE(NOW()) 
order by ReceivedAt DESC

So how do I pull data twice with 2 -3 substring commands from one field and print it correctly.

It should look something like this:

ReceivedAt                  Outside                Port 
2010-01-04 13:50:40         219.136.22.29(80)       443

Many Thanks

[-lodogg-]

So I have found the answer but came up with a 2nd question:-)

The following code works as expected.

select ReceivedAt, substring(Message,POSITION('from' in Message)+4, POSITION('/'in Message)-31)as External,
(select substring(Message,POSITION('to outside:' in Message)+25, POSITION('/'in Message)+4))as Port
from SystemEvents
where Message like'%denied%'
and Message like '%192.168.1.1%'
and Message like '%ACL%'
and ReceivedAt> '2010-01-04'
order by ReceivedAt DESC

ReceivedAt                External            Port
2010-01-05 22:36:11       4.79.142.206     23
2010-01-05 22:36:11       4.79.142.206     23
2010-01-05 22:36:11       4.79.142.206'    22

The second question is how can I add the above query to another one? The second query will be like the one above but with different 'substring' and 'and' statements (see below). My plan is to have both queries run in one query and print the data as expected.

select ReceivedAt, substring(Message,POSITION('access' in Message)+35, POSITION('/'in Message)-23)as External,
(select substring(Message,POSITION('DMZ/' in Message)+17,POSITION('/'in Message)-31))as Port
from SystemEvents
where Message like'%denied%'
and Message like '%192.168.1.1%'
and DATE(ReceivedAt) = DATE(NOW())
and Message not like '%ACL%'
and Message not like '%inside/%'
order by ReceivedAt DESC

thx
-lo

[-lodogg-]

Unfortunately I guess I have to answer my own question:\ I needed a "union" command between each "select" statement to make the query run as expected.

Example:

select ReceivedAt
,substring(Message,POSITION('from' in Message)+4, POSITION('/'in Message)-31) as External
,substring(Message,POSITION('to outside:' in Message)+25, POSITION('/'in Message)-+4) as Port 
from SystemEvents
where Message like'%denied%'
and Message like '%192.168.1.1%'
and Message like '%ACL%'
and DATE(ReceivedAt) = DATE(NOW())

union

select ReceivedAt
,substring(Message,POSITION('access' in Message)+35, POSITION('/'in Message)-23) as External
,substring(Message,POSITION('DMZ/' in Message)+17,POSITION('/'in Message)-31) as Port
from SystemEvents
where Message like'%denied%'
and Message like '%192.168.1.1%'
and DATE(ReceivedAt) = DATE(NOW())
and Message not like '%ACL%'
and Message not like '%inside/%'
order by ReceivedAt DESC

thx