Пишем логи IIS в MSSQL

Цель: организовать хранилище логов IIS под управлением MSSQL.

Условия:

  • Для удобного поиска (SELECT’а) по времени значение date и time должны храниться в одном поле
  • Каждая запись должна иметь уникальный ключ
  • При переносе логов в MSSQL должен использоваться BULK insert.
  • Вставка логов производится за предыдущий день.

Сразу оговорюсь, что напрямую писать логи в MSSQL не используя сторонний софт не получится. Поэтому я написал небольшую консольную программу IIS2SQL, которая удаляет из файла лога закомментированные строки и форматирует поля date и time в одно поле для дальнейшей bulk-вставки. У Microsoft есть аналогичная программа preplog, но она только удаляет закомментированные строки.

Итак, давайте для начала включим логирование в формате W3C. Я включу логирование для всех своих сайтов. В настройках вы можете указать необходимые поля для логирования.

Теперь необходимо создать необходимую базу и таблицу. Если вы уже гуглили данный вопрос, то обратили внимание, что в сети есть куча руководств и структура таблиц в них может отличаться. Поэтому мы будем делать свою таблицу на основании логов которые нам отдает IIS. Открываем любым текстовым редактором (лучше блокнот не использовать,т.к. велика вероятность что он зависнет) файл логов и смотрим на 3 строку. В ней мы и найдем заголовки полей, которые нам необходимо будет создать.

В моем случае это #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status time-taken. Не забывайте, что одно из условий было объединение полей date и time, поэтому создадим соотвествующую табличку. Обратите внимание на то, что я в таблицу добавил автоинкрементное поле Id.

USE [v2_prod_logs]
GO

/****** Object:  Table [dbo].[m_IIS_Log]    Script Date: 10/01/2015 15:25:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[m_IIS_Log](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[DATETIME] [datetime] NULL,
	[s-sitename] [varchar](60) NULL,
	[s-ip] [varchar](16) NULL,
	[cs-method] [varchar](8) NULL,
	[cs-uri-stem] [varchar](255) NULL,
	[cs-uri-query] [varchar](2048) NULL,
	[s-port] [varchar](16) NULL,
	[cs-username] [varchar](16) NULL,
	[c-ip] [varchar](16) NULL,
	[cs(User-Agent)] [varchar](1024) NULL,
	[cs-host] [varchar](60) NULL,
	[sc-status] [int] NULL,
	[sc-substatus] [int] NULL,
	[sc-win32-status] [int] NULL,
	[time-taken] [int] NULL,
 CONSTRAINT [PK_m_IIS_Log] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Проблема в том, что если формат лога не соответствует вашей таблице, то BULK-insert из файла не получится. Поэтому вставку в таблицу мы будем делать во View. Создадим новый View для этой БД, исключив поле Id.

USE [v2_prod_logs]
GO

/****** Object:  View [dbo].[ViewIISLog]    Script Date: 10/01/2015 15:29:10 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create view [dbo].[ViewIISLog]
as Select [DATETIME],
	[s-sitename],
	[s-ip],
	[cs-method],
	[cs-uri-stem],
	[cs-uri-query],
	[s-port],
	[cs-username],
	[c-ip],
	[cs(User-Agent)],
	[cs-host],
	[sc-status],
	[sc-substatus],
	[sc-win32-status],
	[time-taken]
FROM [dbo].[m_IIS_Log];

GO

Теперь вернемся к программе, которую я написал. Скачайте IIS2SQL и распакуйте в любой удобный каталог на сервере MSSQL. Если один сервер для MSSQL и IIS, то можно сразу запустить программу и поместить в планировщик заданий. А если нет, то вам надо будет каким то образом логи передать на сервер MSSQL. Напрямую с сервера IIS BULK сделать не получится. На сервере IIS создайте пользователя и расшарьте каталог с логами. А на сервере MSSQL напишем скрипт и поместим его выполнение в планировщик. Чтобы сервер сильно не нагружать, лучше настроить выполнение ночью.

Параметры для запуска IIS2SQL:

"C:\Program Files\IIS2SQL\IIS2SQL.exe" "ROOT_PATH" FILE_TYPE ADD_DAYS SERVER LOGIN PASSWORD DATABASE TABLE

Вот сам скрипт:

@echo off
net use Y: /delete /y
net use Y: \\192.168.1.1\IIS_Logs my_share_password /USER:logiis
"C:\Program Files\IIS2SQL\IIS2SQL.exe" Y: log -1 localhost dbuser dbpassword v2_prod_logs ViewIISLog
net use Y: /delete /y

Вот в принципе и все! Удачи!

Внедряем email-аутентификацию DKIM в Postfix за 5 минут

Работал у нас почтовый сервер для рассылки корреспонденции клиентам. И все бы ничего, но стал люд жаловаться, что не доходят письма или  в спам попадают. И хотя spf был настроен, все говорило о том, что пора нам к нашему Postfix прикрутить DKIM. Как гласит цитата откуда-то с хабра:

DKIM-подпись — это цифровая подпись, которая добавляется к заголовкам письма сервером отправителя, по которой сервер получателя может удостовериться, что отправитель письма соответствует полю From в заголовках письма. Если сервер получателя проверяет эту подпись, то по результатам проверки сервер может принять решение о том, как поступать с письмом: принять, отправлять в папку «Спам», отправить на дополнительную проверку или вовсе отказаться от приёма. Подписи DKIM проверяют и используют сами все ведущие почтовые службы, включая Яндекс и Mail.ru.

Для начала установим необходимый софт командой

aptitude install opendkim opendkim-tools

После установки необходимо отредактировать файл /etc/opendkim.conf, добавив в него строки

Canonicalization relaxed/relaxed
SyslogSuccess yes
KeyTable file:/etc/opendkim/keytable
SigningTable file:/etc/opendkim/signingtable
SoftwareHeader yes
LogWhy yes
ExternalIgnoreList file:/etc/opendkim/trusted
InternalHosts file:/etc/opendkim/trusted

Теперь создадим рабочий каталог,а в нем новый приватный и открытый ключ. Открытый ключ мы потом пропишем на нашем DNS-сервере.

mkdir /etc/opendkim/
opendkim-genkey -s mail -d example.com

В каталоге /etc/opendkim у вас должно появиться 2 новых файла. mail.private — это наш приватный ключ, которым мы будем подписывать письма. А mail.txt содержит публичный ключ. Список имеющихся ключей укажем в файле /etc/opendkim/keytable в формате «название домен:селектор:/путь/до/ключа». В нашем случае содержимое файла будет таким:

mail._domainkey.example.com example.com:mail:/etc/opendkim/keys/example.com/mail.private

Теперь объясним OpenDKIM почту каких доменов какими ключами подписывать в файле /etc/opendkim/signingtable в формате «домен название-ключа».

example.com mail._domainkey.example.com

В файле /etc/opendkim/trusted необходимо прописать ваши доверенные узлы:

127.0.0.1
localhost
192.168.1.0/24

Зададим необходимые права, добавим пользователя postfix в группу opendkim. Это нужно чтобы postfix смог отправлять письма на подпись через сокет OpenDKIM. После чего перезапустим службу. Я рекомендую заглянуть в системный лог и убедиться, что нет ошибок при запуске службы.

chown opendkim /etc/opendkim -R
gpasswd -a postfix opendkim
service opendkim restart

Вторым этапом настроим postfix, чтобы он мог подписывать нашу почту. В main.cf необходимо добавить:

milter_default_action=accept
milter_protocol=2
smtpd_milters=unix:/var/run/opendkim/opendkim.sock
non_smtpd_milters=unix:/var/run/opendkim/opendkim.sock

Ну и опять рестарт/релоад

service postfix restart

Третьим и последним этапом мы настроим наш DNS сервер добавив в него TXT запись. Выведите содержимое файла mail.txt и используйте следующую пару ключ-значение для добавления новой записи в DNS.

Name: mail._domainkey.example.com.
Text: "v=DKIM1; k=rsa; p=MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQC5N3lnvvrYgPCRSoqn+awTpE+iGYcKBPpo8HHbcFfCIIV10Hwo4PhCoGZSaKVHOjDm4yefKXhQjM7iKzEPuBatE7O47hAx1CJpNuIdLxhILSbEmbMxJrJAG0HZVn8z6EAoOHZNaPHmK2h4UUrjOG8zA5BHfzJf7tGwI+K619fFUwIDAQAB"

Для применения изменений в DNS может пройти несколько часов. Проверить DKIM-подпись вы можете отправив пиьсмо через свой сервер на ящик [email protected] Ответное письмо вы получите в виде отчета. Например:

=========================================================
Summary of Results
==========================================================
SPF check: pass
DomainKeys check: neutral
DKIM check: pass
Sender-ID check: pass
SpamAssassin check: ham

Полезные ссылки: