RegEx or equivalent in CRServer XI 1

[epoh]

I am looking for a RegEx or the CR equivalent of RegEx to parse a PIX syslog. I looked in the help file and couldn't find anything that looked promising.

I am using SQL Server 2000 as my backend database.

 

[lbass]

I think you would have better luck explaining what you would like to do, and showing some samples of existing data and then the same data as you would like to see it returned. There are multiple functions that might be of assistance.

-LB

 

[epoh]

Sorry about that:

Data to parse

%PIX-3-710003: TCP access denied by ACL from 10.20.15.68(unresolved)/33348 to outside:.64.233.167.99(64-233-167-99.myip.com)/22
%PIX-3-106014: Deny inbound icmp src inside:192.168.1.20(server.mydomain.com) dst inside:192.168.100.100(unresolved) (type 8, code 0)
%PIX-3-313001: Denied ICMP type=8, code=0 from 61.218.193.226(61-218-193-226.HINET-IP.hinet.net) on interface outside

Parsed data should look like this (pipe means a column) - colons and parentheses removed:

%PIX-3-710003|TCP access denied by ACL from|10.20.15.68|unresolved|33348|to outside|64.233.167.99|64-233-167-99.myip.com|22
%PIX-3-106014|Deny inbound icmp src inside|192.168.1.20|server.mydomain.com|dst inside|192.168.100.100|unresolved|type 8, code 0
%PIX-3-313001|Denied ICMP type=8, code=0 from|61.218.193.226|61-218-193-226.HINET-IP.hinet.net|on interface outside

Depending on the PIX code the columns will be different, so I will need to do a conditional statement for each PIX Code I want to parse.


Thanks for your help!

 

[epoh]

Correction:
*(pipe means a column)

Pipe indicates a field

 

[lbass]

Try the following, replacing {@str} with your field name:

stringvar x := replace(replace(replace(replace(replace (replace(replace(replace({@str}, chr(13),"^"),"(","^"),":","^"),")","^"),"/","^"),"from ","from ^")," to","^ to"),"^^","^");
stringvar array y := split(x,"^");
join(y,"|");

Format the formula to "can grow". This should replicate your result sample as long as returns are based on chr(13). If not, try chr(10). Then you could reference each field by using:

stringvar x := replace(replace(replace(replace(replace (replace(replace(replace({@str}, chr(13),"^"),"(","^"),":","^"),")","^"),"/","^"),"from ","from ^")," to","^ to"),"^^","^");
stringvar array y := split(x,"^");
if ubound >= 2 then
y[2]

Replace the 2 in both places with the number of the desired element.

-LB

 

[epoh]

Wow! That is pretty fancy.

It worked, with a pipe separating each "field".

Can I make CR display each "|" as a separate field?

In other words can I split the string so that I can sort the data in the string as if they were separate fields, and put these "fields" on the report?

Thanks,

I hope that question makes sense.

 

[lbass]

You didn't read the last part of my post which explains how to do that. You would need a separate formula per field. You might also want to change the formula to eliminate spaces:

if ubound >= 2 then
trim(y[2])

-LB

 

[epoh]

I did plug it in and but it does not give me the expected result.

Instead it returns three fields.

 

[epoh]

****Instead it returns three "fields" on some and none on others.

 

[lbass]

Show me the formula you are using, please.

-LB

 

[epoh]

Right now I am just trying to make sense of this, but here it is:

This is for all the PIX Codes (for right now - until I put the conditional statments in for formatting of each code)

stringvar x := replace(replace(replace(replace(replace (replace(replace(replace({@SeparateString}, chr(13),"^"),"(","^"),":","^"),")","^"),"/","^"),"from ","from ^")," to","^ to"),"^^","^");
stringvar array y := split(x,"^");
if ubound(y) >= 2 then
y[2

Result for PIX-3-106014

nothing - just blank

Result for PIX-3-71003

|10.20.15.68|unresolved|33348|

 

[lbass]

What is the content of {@SeparateString}? This should be the table.field.

Also, I thought you were showing the contents of one instance of your field in your first post. Does each line starting with %PIX represent a separate row? I retested and the formulas still worked, so there is something you are not telling me.

-LB

 

[epoh]

@SeparateString

stringvar x := replace(replace(replace(replace(replace (replace(replace(replace({Table1.Field4}, chr(13),"^"),"(","^"),":","^"),")","^"),"/","^"),"from ","from ^")," to","^ to"),"^^","^");
stringvar array y := split(x,"^");
join(y,"|");

Yes, each row starts with %PIX (Sorry I should have been more clear)

 

[epoh]

My mistake I didn't realize that all of that code was supposed to go in one field. I tried that (putting both formulas together in the same Formula Field) and it worked like you said.