SQL string question (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

rrnml -> SQL string question (9/27/2005 12:07:21)

I have a string called MsgText. I am trying to extract the IP address from this string. The problem is, the string is not always in the same place so if I tried to extract starting at a certain character and ending at a certain character, it wouldn't always return the IP since it appears in different places. So I thought maybe there would be a way to extract it by the characters that are before and after the IP. There will always be an src=XX.X.XX.XX: So it's always preceded by and = sign and followed by a : colon. I will give you the string. Can you tell me if this can be done? If not is there a better or easier way?

Thanks

The bold part is what I'm trying to extract.

id=firewall sn=0006B10CE7F8 time="2005-09-26 16:19:51" fw=65.123.147.178 pri=6 c=262144 m=98 msg="Connection Opened" n=1423607 src=10.1.72.67:2794:X0 dst=63.240.76.72:80:X1 proto=tcp/http



dpf -> RE: SQL string question (9/27/2005 12:09:59)

quote:

So it's always preceded by and = sign and followed by a : colon.
but does it come in a string like the one you have posted which is filledwith other = signs? if so, wouldnt src= be your key. do a google search for "Regular Expressions" which is a method that may help you



rrnml -> RE: SQL string question (9/27/2005 12:17:09)

You're right src= would be the first key to look for.



dpf -> RE: SQL string question (9/27/2005 12:25:12)

also, what programming language are you using? javascript? asp? php? i think there might be string functions that would let you do that.... using src= as a starting point.



rrnml -> RE: SQL string question (9/27/2005 12:26:04)

I'm using ASP and Frontpage as the editor.



dpf -> RE: SQL string question (9/27/2005 12:32:26)

see if this helps:


[image]local://upfiles/9414/1B21596F0FCC46D1BBBED49E727DCA1D.jpg[/image]



ou812 -> RE: SQL string question (9/27/2005 12:41:49)

As Dan was saying, use Instr(string,searchstring) to get the position of "src=" then use Mid(string,start,length) to get the IP address. So, something like:

ippos=instr(MsgText,"src=") + 4
adding 4 to compesate for the actual text src=

dstpos=instr(MsgText,"dst=")
length = dstpos - ippos

IPtemp=mid(MsgText,ippos,length)
this will then grab the whole string up to the dst=. we then need to find out where the : is and cut there

colonpos=instr(IPtemp,":")

IP=mid(IPtemp,0,colonpos)

Not sure if this works or not, since I didn't try it, but it gives you an idea of how to attack it.

Here are the functions with some examples too: http://www.learnasp.com/learn/strings.asp



rrnml -> RE: SQL string question (9/27/2005 12:43:36)

Awesome guys. I will try that. Thanks. And I'LL BE BACK!



dpf -> RE: SQL string question (9/27/2005 12:46:41)

good job brian - i started it and wasnt sure how to finish...lol..hey, I' m learning slowly



rrnml -> RE: SQL string question (9/27/2005 12:56:53)

Can you explain this part to me?

dstpos=instr(MsgText,"dst=")
length = dstpos - ippos



dpf -> RE: SQL string question (9/27/2005 13:09:33)

quote:

id=firewall sn=0006B10CE7F8 time="2005-09-26 16:19:51" fw=65.123.147.178 pri=6 c=262144 m=98 msg="Connection Opened" n=1423607 src=10.1.72.67:2794:X0 dst=63.240.76.72:80:X1 proto=tcp/http
see from your original post... brian assumed that dst= comes after the ip and would represent the end of the src= string. is that a safe assumtion?
if not, is the colon : the ending point?



ou812 -> RE: SQL string question (9/27/2005 13:11:01)

The thought was to find the position of the "dst=" so that I could grab this whole part "10.1.72.67:2794:X0 ", in your example. So, this finds where the position where "dst=" starts. I then take the "dst=" position minus the "src=" position to give me the length of the string, from the beging of "10.1.72.67:2794:X0 " to the end of it. In theory, of course. Does that help? Is it not working right?




rrnml -> RE: SQL string question (9/27/2005 13:14:25)

Ok I see. That's almost what I need. However, what I want returned is 10.1.72.67
I don't need anything after those last two digits.



ou812 -> RE: SQL string question (9/27/2005 13:18:51)

Yes, and that is where these lines come in to play

colonpos=instr(IPtemp,":")
IP=mid(IPtemp,0,colonpos)

This takes your newly created string, IPtemp, and finds the colon's position, and then you cut at the first colon.



rrnml -> RE: SQL string question (9/27/2005 13:20:13)

Oh I see. Duh. Sorry. I'll try to implement and let you know how it goes!



dpf -> RE: SQL string question (9/27/2005 13:22:32)

i havent had the time (or need) yet to get serious about string functions but as brian has shown you, they are amazingly powerful!!!!



rrnml -> RE: SQL string question (9/27/2005 16:39:46)

Will this work for MSSQL as well or is there another format?



ou812 -> RE: SQL string question (9/27/2005 16:51:29)

I'm not sure I understand. All of what we were doing is ASP, and nothing to do with SQL. Did you pull MsgText from a database, is why you are asking. Sorry, just not understanding.



rrnml -> RE: SQL string question (9/27/2005 17:03:04)

It's actually getting pulled off the server from the syslog. So I know I said I was using asp, but I'm always in asp so I kind of have a one track mind when it comes to that. So it's actually being done in MSSQL. Sorry :(



ou812 -> RE: SQL string question (9/27/2005 21:14:37)

Sorry, I'm still confused. I believe you are reading from a syslog and then reading/parsing to get your answer using ASP. I'm not understanding what you mean will this work with MSSQL(MicroSoft SQL Server, a DB) though? I'm not sure where MSSQL fits in the picture for you, or what you are asking about how it should?.



rrnml -> RE: SQL string question (9/28/2005 10:54:47)

I'm sorry. What I mean is it's being done on the SQL server through a stored procedure. My apologies. I'm not too good with the terminology.



ou812 -> RE: SQL string question (9/28/2005 23:20:53)

Hmmm, I don't see why it shouldn't work. I'ld be interested if you find out otherwise.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125