Пишем логи 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

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

35 комментариев к “Пишем логи IIS в MSSQL

  1. Can I simply state what an alleviation to find a person who really knows what theyre speaking about on the web. You definitely know how to bring a problem to light as well as make it crucial. More people need to read this and also understand this side of the tale. I cant believe youre not much more preferred since you most definitely have the gift.

  2. An outstanding share! I’ve just forwarded this onto a colleague who had been conducting a little homework on this. And he actually ordered me breakfast due to the fact that I stumbled upon it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanx for spending the time to talk about this issue here on your blog.

  3. Hi there, just became alert to your blog through
    Google, and found that it is truly informative.
    I am gonna watch out for brussels. I’ll be grateful if you
    continue this in future. A lot of people will be benefited from your writing.
    Cheers!

  4. I was really pleased to discover this web-site. I wished to many thanks for your time for this remarkable read!! I certainly delighting in every little bit of it and also I have you bookmarked to check out new things you blog post.

  5. I do not know if it’s just me or if perhaps everyone else experiencing
    problems with your site. It appears as if some of the text on your posts are
    running off the screen. Can someone else please comment and let me know if this is happening to them as well?

    This could be a problem with my internet browser because I’ve had this
    happen previously. Many thanks

  6. I’m really inspired with your writing skills and
    also with the structure in your weblog. Is that this a paid theme or did you
    modify it your self? Either way stay up
    the excellent quality writing, it is uncommon to peer a great
    weblog like this one today..

  7. Attractive section of content. I just stumbled upon your
    web site and in accession capital to assert that
    I acquire in fact enjoyed account your blog posts. Any way I’ll be subscribing to your feeds and even I achievement you access consistently quickly.

  8. An impressive share, I just given this onto an associate who was doing a little analysis on this. And he actually got me breakfast due to the fact that I found it for him. smile. So let me rephrase that: Thnx for the treat! However yeah Thnkx for spending the time to review this, I really feel highly regarding it and also enjoy learning more on this topic. When possible, as you end up being know-how, would you mind upgrading your blog site with even more details? It is extremely handy for me. Huge thumb up for this post!

  9. After research a few of the blog posts on your site now, as well as I truly like your way of blog writing. I bookmarked it to my book marking internet site listing and also will certainly be checking back soon. Pls have a look at my internet site too and let me recognize what you assume.

  10. You could certainly see your enthusiasm within the paintings you write. The world hopes for even more passionate writers like you who are not afraid to mention how they believe. All the time go after your heart.

  11. I’m really inspired together with your writing abilities and also with the layout to your weblog. Is this a paid subject matter or did you customize it yourself? Either way keep up the excellent high quality writing, it is uncommon to see a nice weblog like this one nowadays..

  12. There are certainly a great deal of information like that to take into consideration. That is a great point to bring up. I provide the ideas over as basic inspiration however clearly there are concerns like the one you raise where the most crucial point will certainly be working in truthful good faith. I don?t know if best practices have emerged around points like that, however I am sure that your work is plainly determined as a fair game. Both kids and also ladies feel the impact of just a moment?s enjoyment, for the remainder of their lives.

  13. Youre so awesome! I don’t mean Ive read anything like this prior to. So great to discover somebody with some initial ideas on this subject. realy thanks for beginning this up. this internet site is something that is needed on the internet, a person with a little creativity. beneficial work for bringing something new to the net!

  14. Oh my benefits! an incredible short article man. Thanks However I am experiencing problem with ur rss. Don?t understand why Incapable to sign up for it. Is there anybody getting similar rss trouble? Anyone who recognizes kindly respond. Thnkx

  15. An outstanding share! I have just forwarded this onto a friend who had been conducting a little research on this.
    And he actually bought me breakfast due to the fact
    that I stumbled upon it for him… lol. So let me reword this….
    Thanks for the meal!! But yeah, thanks for spending time to discuss this issue
    here on your website.

  16. I love your blog.. very nice colors & theme. Did you design this website yourself or did you hire someone to do it for you? Plz reply as I’m looking to design my own blog and would like to find out where u got this from. thanks a lot|

Оставить ответ

Ваш адрес email не будет опубликован. Обязательные поля помечены *