Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

Query to parse out the IP address from string

Looking for a way in a SQL query to extract the IP address from a string and then rename it.
For example I have a date field called Date_Time and field called "message" and it contains data such as

TCP connection 72260 for Outside:123.123.123.19/4717 to Inside:0.0.0.0/650 duration 0:00:00 bytes 0 TCP Reset-I ()
TCP connection 7260 for Outside:123.123.129.145/4718 to Inside:10.10.10.10/60 duration 0:00:00 bytes 0 TCP Reset-I ()

I want to grab the IP address of 123.123.123.19 and return something like
Date_Time, Host
2008-10-17 10:48:48    NewHostName       <---- based on the IP of 123.123129.145
2008-10-17 10:50:17    AnotherHostName <---- based on the IP of 123.123123.19
0
edrz01
Asked:
edrz01
1 Solution
 
srafi78Commented:
You might have to use the replace function for each each IP and replace it with the string you want to put in its palce.
0
 
BrandonGalderisiCommented:
this will find the IP address in message along with date_time, but I'm not sure where you want to get the hostname from.

Just replace your table name with the derived table I have put sample data in for after the FROM
select date_time,substring(message, charindex('Outside:',message)+8,charindex('/',message,charindex('Outside:',message)+8)-(charindex('Outside:',message)+8))
 
from 
(select 'TCP connection 72260 for Outside:123.123.123.19/4717 to Inside:0.0.0.0/650 duration 0:00:00 bytes 0 TCP Reset-I ()' as Message
union select 'TCP connection 7260 for Outside:123.123.129.145/4718 to Inside:10.10.10.10/60 duration 0:00:00 bytes 0 TCP Reset-I ()'
) a

Open in new window

0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now