Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
Allan Stark
Member

Откуда:
Сообщений: 38
В одном програмном продукте IP адрес хранится в MS SQL в таблице в поле типа uniqueidentifier
Тип выбран для возможности хранения IPv4 и IPv6 адресов.

Задача. Создать функцию преобразования из строки вида "192.168.8.1" в ее эквивалент типа uniqueidentifier: "C0A80801-FFFF-0000-0000-000000000000"

Обратное преобразование (uniqueidentifier в IPv4) сделать не проблема. Сам застопорился на разборе строки IPv4 - данные поступают в БД именно как "192.168.8.1", а не "192.168.008.001".
11 апр 14, 13:16    [15867741]     Ответить | Цитировать Сообщить модератору
 Re: Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
Glory
Member

Откуда:
Сообщений: 104751
PARSENAME()
11 апр 14, 13:17    [15867742]     Ответить | Цитировать Сообщить модератору
 Re: Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
aleks2
Guest
Допилите по фкусу.
ALTER function dbo.IP2Int
(@ip varchar(15))
returns bigint
as
begin
  return cast(PARSENAME(@ip , 1) as tinyint)
	+cast(PARSENAME(@ip , 2) as tinyint)*cast(256 as bigint)
	+cast(PARSENAME(@ip , 3) as tinyint)*cast(65536 as bigint)
	+cast(PARSENAME(@ip , 4) as tinyint)*cast(16777216 as bigint)
end


И поразмыслите на досуге о правильном хранении IP-адресов в системе.
11 апр 14, 13:48    [15867983]     Ответить | Цитировать Сообщить модератору
 Re: Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
Allan Stark
Member

Откуда:
Сообщений: 38
aleks2
Допилите по фкусу.
ALTER function dbo.IP2Int
(@ip varchar(15))
returns bigint
as
begin
  return cast(PARSENAME(@ip , 1) as tinyint)
	+cast(PARSENAME(@ip , 2) as tinyint)*cast(256 as bigint)
	+cast(PARSENAME(@ip , 3) as tinyint)*cast(65536 as bigint)
	+cast(PARSENAME(@ip , 4) as tinyint)*cast(16777216 as bigint)
end


И поразмыслите на досуге о правильном хранении IP-адресов в системе.


Большое спасибо за наводку.
И поразмыслите на досуге о правильном хранении IP-адресов в системе.
Это не ко мне, это к господам, сотворившим сей продукт, БД ихняя:
http://technet.microsoft.com/en-us/forefront/ee807302
11 апр 14, 14:52    [15868440]     Ответить | Цитировать Сообщить модератору
 Re: Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
aleks2
Guest
Allan Stark

Большое спасибо за наводку.
И поразмыслите на досуге о правильном хранении IP-адресов в системе.
Это не ко мне, это к господам, сотворившим сей продукт, БД ихняя:
http://technet.microsoft.com/en-us/forefront/ee807302


Вот не надо с больной головы на здоровую.
При журналировании ISA 2006 на SQL Server поля IP address имеют тип bigint.

Сомневаюсь, что у Forefront TMG поля IP address имеют тип uniqueidentifyer.
11 апр 14, 16:23    [15869221]     Ответить | Цитировать Сообщить модератору
 Re: Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
Glory
Member

Откуда:
Сообщений: 104751
aleks2
Сомневаюсь, что у Forefront TMG поля IP address имеют тип uniqueidentifyer.

Вряд ли IPv6 поместится в bigint
11 апр 14, 16:27    [15869250]     Ответить | Цитировать Сообщить модератору
 Re: Функция преобразования строки с IPv4 адресом в тип uniqueidentifier  [new]
Allan Stark
Member

Откуда:
Сообщений: 38
aleks2
Allan Stark
Большое спасибо за наводку.
И поразмыслите на досуге о правильном хранении IP-адресов в системе.
Это не ко мне, это к господам, сотворившим сей продукт, БД ихняя:
http://technet.microsoft.com/en-us/forefront/ee807302


Вот не надо с больной головы на здоровую.
При журналировании ISA 2006 на SQL Server поля IP address имеют тип bigint.

Сомневаюсь, что у Forefront TMG поля IP address имеют тип uniqueidentifyer.


Я просто оставлю это здесь... Два сценария, входят в поставку TMG 2010, для развертывания на внешнем SQL (базово TMG ставится с EXPRESS версией с аналогичной структурой таблиц).
И таки да: http://habrahabr.ru/post/188090/

+
IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_batch_insert' AND type = 'P')
exec sp_executesql N'CREATE PROCEDURE sp_batch_insert @tempTableName nvarchar(100), @tableName nvarchar(100) AS
EXECUTE (''INSERT into ['' + @tableName + ''] SELECT * FROM ['' + @tempTableName + '']'')
EXECUTE (''truncate table ['' + @tempTableName + '']'')'
GO

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_batch_discard' AND type = 'P')
exec sp_executesql N'CREATE PROCEDURE sp_batch_discard @tempTableName nvarchar(100) AS
EXECUTE (''truncate table ['' + @tempTableName + '']'')'
GO

CREATE TABLE FirewallLog (
[servername] nvarchar(128),
[logTime] datetime,
[protocol] varchar(32),
[SourceIP] uniqueidentifier,
[SourcePort] int,
[DestinationIP] uniqueidentifier,
[DestinationPort] int,
[OriginalClientIP] uniqueidentifier,
[SourceNetwork] nvarchar(128),
[DestinationNetwork] nvarchar(128),
[Action] smallint,
[resultcode] int,
[rule] nvarchar(128),
[ApplicationProtocol] nvarchar(128),
[Bidirectional] smallint,
[bytessent] bigint,
[bytessentDelta] bigint,
[bytesrecvd] bigint,
[bytesrecvdDelta] bigint,
[connectiontime] int,
[connectiontimeDelta] int,
[DestinationName] varchar(255),
[ClientUserName] varchar(514),
[ClientAgent] varchar(255),
[sessionid] int,
[connectionid] int,
[Interface] varchar(25),
[IPHeader] varchar(255),
[Payload] varchar(255),
[GmtLogTime] datetime,
[ipsScanResult] smallint,
[ipsSignature] nvarchar(128),
[NATAddress] uniqueidentifier,
[FwcClientFqdn] varchar(255),
[FwcAppPath] varchar(260),
[FwcAppSHA1Hash] varchar(41),
[FwcAppTrusState] smallint,
[FwcAppInternalName] varchar(64),
[FwcAppProductName] varchar(64),
[FwcAppProductVersion] varchar(20),
[FwcAppFileVersion] varchar(20),
[FwcAppOrgFileName] varchar(64),
[InternalServiceInfo] int,
[ipsApplicationProtocol] nvarchar(128),
[FwcVersion] varchar(32)
)

CREATE CLUSTERED INDEX [IX_FirewallLog_DateTime] ON [FirewallLog]([logTime]) ON [PRIMARY]

GO

+

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_batch_insert' AND type = 'P')
exec sp_executesql N'CREATE PROCEDURE sp_batch_insert @tempTableName nvarchar(100), @tableName nvarchar(100) AS
EXECUTE (''INSERT into ['' + @tableName + ''] SELECT * FROM ['' + @tempTableName + '']'')
EXECUTE (''truncate table ['' + @tempTableName + '']'')'
GO

IF NOT EXISTS (SELECT name FROM sysobjects WHERE name = 'sp_batch_discard' AND type = 'P')
exec sp_executesql N'CREATE PROCEDURE sp_batch_discard @tempTableName nvarchar(100) AS
EXECUTE (''truncate table ['' + @tempTableName + '']'')'
GO

CREATE TABLE WebProxyLog (
[ClientIP] uniqueidentifier,
[ClientUserName] nvarchar(514),
[ClientAgent] varchar(128),
[ClientAuthenticate] smallint,
[logTime] datetime,
[service] smallint,
[servername] nvarchar(32),
[referredserver] varchar(255),
[DestHost] varchar(255),
[DestHostIP] uniqueidentifier,
[DestHostPort] int,
[processingtime] int,
[bytesrecvd] bigint,
[bytessent] bigint,
[protocol] varchar(13),
[transport] varchar(8),
[operation] varchar(24),
[uri] varchar(2048),
[mimetype] varchar(32),
[objectsource] smallint,
[resultcode] int,
[CacheInfo] int,
[rule] nvarchar(128),
[FilterInfo] nvarchar(256),
[SrcNetwork] nvarchar(128),
[DstNetwork] nvarchar(128),
[ErrorInfo] int,
[Action] varchar(32),
[GmtLogTime] datetime,
[AuthenticationServer] varchar(255),
[ipsScanResult] smallint,
[ipsSignature] nvarchar(128),
[ThreatName] varchar(255),
[MalwareInspectionAction] smallint,
[MalwareInspectionResult] smallint,
[UrlCategory] int,
[MalwareInspectionContentDeliveryMethod] smallint,
[UagArrayId] varchar(20),
[UagVersion] int,
[UagModuleId] varchar(20),
[UagId] int,
[UagSeverity] varchar(20),
[UagType] varchar(20),
[UagEventName] varchar(60),
[UagSessionId] varchar(50),
[UagTrunkName] varchar(128),
[UagServiceName] varchar(20),
[UagErrorCode] int,
[MalwareInspectionDuration] int,
[MalwareInspectionThreatLevel] smallint,
[InternalServiceInfo] int,
[ipsApplicationProtocol] nvarchar(128),
[NATAddress] uniqueidentifier,
[UrlCategorizationReason] smallint,
[SessionType] smallint,
[UrlDestHost] varchar(255),
[SrcPort] int,
[SoftBlockAction] nvarchar(128)
)

CREATE CLUSTERED INDEX [IX_WebProxyLog_DateTime] ON [WebProxyLog]([logTime]) ON [PRIMARY]

GO
11 апр 14, 16:58    [15869427]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить