|
| |
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
 |
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
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
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
_____________________________
Dan
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
RE: SQL string question - 9/27/2005 12:17:09
You're right src= would be the first key to look for.
| |
|
|
|
dpf
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
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.
_____________________________
Dan
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
RE: SQL string question - 9/27/2005 12:26:04
I'm using ASP and Frontpage as the editor.
| |
|
|
|
ou812
Posts: 1603
Joined: 1/5/2002
From: San Diego
Status: offline
|
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
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
RE: SQL string question - 9/27/2005 12:43:36
Awesome guys. I will try that. Thanks. And I'LL BE BACK!
| |
|
|
|
dpf
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
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
_____________________________
Dan
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
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
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
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?
_____________________________
Dan
| |
|
|
|
ou812
Posts: 1603
Joined: 1/5/2002
From: San Diego
Status: offline
|
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?
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
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
Posts: 1603
Joined: 1/5/2002
From: San Diego
Status: offline
|
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.
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
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
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
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!!!!
_____________________________
Dan
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
RE: SQL string question - 9/27/2005 16:39:46
Will this work for MSSQL as well or is there another format?
| |
|
|
|
ou812
Posts: 1603
Joined: 1/5/2002
From: San Diego
Status: offline
|
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.
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
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
Posts: 1603
Joined: 1/5/2002
From: San Diego
Status: offline
|
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?.
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
|
|
rrnml
Posts: 197
Joined: 9/20/2004
Status: offline
|
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
Posts: 1603
Joined: 1/5/2002
From: San Diego
Status: offline
|
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.
_____________________________
-brian
EnterpriseDB: Enterprise-class relational database management system
PostgreSQL: The world's most advanced open source database
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
