Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
Есть задачка средствами SQL мониторить и логировать вход, выход пользователей.
вопрос чем и какая нагрузка будет на базу?
29 июл 13, 14:07    [14632023]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
Dmitriyv
Есть задачка средствами SQL мониторить и логировать вход, выход пользователей.
вопрос чем и какая нагрузка будет на базу?
c2 audit mode
29 июл 13, 14:08    [14632036]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Гость333
Member

Откуда:
Сообщений: 3683
alexeyvg
c2 audit mode

Кроме входа-выхода, будет логироваться ещё миллион разных событий. Нагрузка на сервер вырастает существенно. При исчерпании места для логов сервер будет остановлен. В общем, не очень хороший вариант.

Если версия позволяет, то лучше использовать SQL Server Audit. Если не позволяет, то Event Notifications. Если и этого не позволяет, то настроить трассировку событий login и logout. Вроде так.
29 июл 13, 14:41    [14632269]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
вот кое что нашел, буду пробовать.
Может есть способ попроще?
не силен в запросах :(

Еще нужно вытащить в читаемом виде список юзеров, и групп с правами доступа. опробовал несколько скриптов, получил не очень наглядные данные.
29 июл 13, 16:08    [14632946]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Dmitriyv
вот кое что нашел, буду пробовать.
Может есть способ попроще?
не силен в запросах :(

Еще нужно вытащить в читаемом виде список юзеров, и групп с правами доступа. опробовал несколько скриптов, получил не очень наглядные данные.

Вам нужны логины или пользователи базы?
29 июл 13, 16:17    [14633003]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888

Здесь обсуждалось

И вообще в поиске по форуму будет достаточно информации для решения этой задачи.
29 июл 13, 16:19    [14633020]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Dmitriyv
Может есть способ попроще?

Версия MSSQL у вас какая?

Dmitriyv
Еще нужно вытащить в читаемом виде список юзеров, и групп с правами доступа. опробовал несколько скриптов, получил не очень наглядные данные.

Приведите пример того, что вы подразумеваете под наглядными данными.
29 июл 13, 16:19    [14633022]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Dmitriyv
Есть задачка средствами SQL мониторить и логировать вход, выход пользователей.
вопрос чем и какая нагрузка будет на базу?


Есть такая штука - триггер на логин. Можно настроить триггер, который на вход пользователя может писать в табличку информацию о юзере.

Пример триггера:


+

CREATE TRIGGER [tr1]
ON ALL SERVER
FOR LOGON 
AS
BEGIN

IF ORIGINAL_LOGIN() not in 
(
'user1',
'user2'
)
and ORIGINAL_LOGIN() not like 'domain_name\'
 and App_Name() not in 
(
'App_name'
)
    BEGIN
        
declare @eventdata xml
set @eventdata = EventData()
rollback
begin tran
insert master.dbo.Audit_Login (Date,Destunation,[Login],event_type,spid,[host_name],[app_name])
values (
         @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
         @eventdata.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),
         @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
         @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
         @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
         @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname'),     
             App_Name()
       )
	
	commit
        
    END
END;


GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [tr1] ON ALL SERVER



Соответсвенно этот триггер смотрит на параметры пользователя и если имя пользователя - 'domain_name\' или 'user' или 'user2' или имя приложения под которым он заходит - 'App_name' То этогог пользователя не пускает на сервер, а инфа о нем пишется в таблицу Audit_Login.
Конечно параметры фильтра можно менять и можно делать что бы триггер не запрещал вход и просто логировал.
29 июл 13, 16:48    [14633200]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
Гость333, MS SQL 2008

наглядней примерно так

Имя Пользователя\группа | роль | база | время создания | последний вход

примерно так, хотелось бы получить в идеале :)
29 июл 13, 16:50    [14633220]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
andrew shalaev, мне нужно что бы просто был лог. а можно настроить выгрузку лог файла например раз в день?
29 июл 13, 16:53    [14633242]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Dmitriyv,

вообще этот триггер работает постоянно, и просто надо время от времени просматривать таблицу. Как сделать аудит сторонними приложениями что бы писала только вход\выход я не знаю.
29 июл 13, 17:03    [14633292]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
andrew shalaev, ок понял, спасибо.
29 июл 13, 17:05    [14633301]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
andrew shalaev, попробовал прогнать скрипт на тестовой базе получил ошибку
Could not find stored procedure 'dbo.Audit_Login'.
29 июл 13, 17:13    [14633353]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Dmitriyv,

вам мед еще и ложкой? это было для примеру + табличку бы создать..
29 июл 13, 17:18    [14633383]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Dmitriyv
MS SQL 2008

Нужен результат запроса "select @@version"
29 июл 13, 17:24    [14633432]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31985
Гость333
alexeyvg
c2 audit mode

Кроме входа-выхода, будет логироваться ещё миллион разных событий. Нагрузка на сервер вырастает существенно. При исчерпании места для логов сервер будет остановлен. В общем, не очень хороший вариант.
Ну зато это самый надёжный аудит, как бы стандарт для самых маньяков по секюрити :-)

А так да, есть и другие способы. ИМХО самый простой - триггер на логон, но тогда без logout
29 июл 13, 17:32    [14633483]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
Crimean, я ток учусь :( сильно не пинайте
30 июл 13, 07:55    [14635306]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
Гость333, Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86) Sep 22 2011 00:28:06 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
30 июл 13, 07:56    [14635307]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Dmitriyv,

Ну раз Enterprise, то можно воспользоваться возможностями SQL Server Audit: http://msdn.microsoft.com/ru-ru/library/cc280386(v=sql.100).aspx, http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
Имхо это проще всего. (Сам я, правда, на практике не пользовался — мне как DBD это не нужно. :)
30 июл 13, 08:56    [14635446]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
Гость333, ок, буду пробовать. спасибо.
30 июл 13, 09:13    [14635499]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
andrew shalaev
Member

Откуда: Moscow
Сообщений: 610
Dmitriyv,
andrew shalaev, попробовал прогнать скрипт на тестовой базе получил ошибку
Could not find stored procedure 'dbo.Audit_Login'.


dbo.Audit_Login - это как раз та таблица в которую записываются логи по пользователям

скрипт на ее создание :

+

USE [master]
GO
/****** Object:  Table [dbo].[Audit_Login]    Script Date: 07/30/2013 12:39:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Audit_Login](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Date] [datetime] NULL,
	[Destunation] [sysname] COLLATE Cyrillic_General_CI_AS NOT NULL,
	[Login] [sysname] COLLATE Cyrillic_General_CI_AS NOT NULL,
	[event_type] [sysname] COLLATE Cyrillic_General_CI_AS NOT NULL,
	[spid] [int] NULL,
	[host_name] [sysname] COLLATE Cyrillic_General_CI_AS NULL,
	[app_name] [sysname] COLLATE Cyrillic_General_CI_AS NULL
) ON [PRIMARY]



скрипт на создание триггера, который не будет никого трогать а просто будет писать инфу о пользователях(едиственное что не проверял его, но должен работать)
+

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [tr1]
ON ALL SERVER
FOR LOGON 
AS

    BEGIN
        
declare @eventdata xml
set @eventdata = EventData()

begin tran
insert master.dbo.Audit_Login (Date,Destunation,[Login],event_type,spid,[host_name],[app_name])
values (
         @eventdata.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime'),
         @eventdata.value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname'),
         @eventdata.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),
         @eventdata.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname'),
         @eventdata.value('(/EVENT_INSTANCE/SPID)[1]', 'int'),
         @eventdata.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname'),     
             App_Name()
       )
	
	commit
        
   
END;




а вот этим запросом можно просматривать логи:
+

select *
from master.dbo.Audit_Login (nolock)

30 июл 13, 12:44    [14636710]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
andrew shalaev, спасибо, буду пробовать.
31 июл 13, 07:57    [14640910]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Dmitriyv
Member

Откуда:
Сообщений: 68
andrew shalaev, все отлично работает, нагрузка минимальная.
Спасибо большое!
12 авг 13, 10:43    [14695126]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Empirical
Member

Откуда:
Сообщений: 99
andrew shalaev
скрипт на ее создание :


спасибо за скрипт! только у меня он что-то много логирует:

idDateDestunationLoginevent_typespidhost_nameapp_name
12013-08-12 08:03:24.810V2NT_CONTEXT\v2LOGON51<local machine>Microsoft SQL Server Management Studio
22013-08-12 08:03:26.003V2NT_CONTEXT\v2LOGON52<local machine>Microsoft SQL Server Management Studio
32013-08-12 08:03:26.337V2NT_CONTEXT\v2LOGON51<local machine>Microsoft SQL Server Management Studio
42013-08-12 08:03:28.570V2NT_CONTEXT\v2LOGON52<local machine>Microsoft SQL Server Management Studio
52013-08-12 08:03:28.930V2NT_CONTEXT\v2LOGON52<local machine>Microsoft SQL Server Management Studio
72013-08-12 08:03:39.390V2NT_CONTEXT\v2LOGON53<local machine>Microsoft SQL Server Management Studio - Query
82013-08-12 08:03:39.510V2NT_CONTEXT\v2LOGON53<local machine>Microsoft SQL Server Management Studio - Query
92013-08-12 08:03:40.900V2NT_CONTEXT\v2LOGON53<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
102013-08-12 08:03:43.153V2NT_CONTEXT\v2LOGON53<local machine>Microsoft SQL Server Management Studio - Query
62013-08-12 08:03:36.693V2NT_CONTEXT\v2LOGON52<local machine>Microsoft SQL Server Management Studio - Query
112013-08-12 08:03:43.230V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Query
122013-08-12 08:03:43.950V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
132013-08-12 08:03:44.267V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
142013-08-12 08:03:44.450V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
152013-08-12 08:03:44.457V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
162013-08-12 08:03:44.480V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
172013-08-12 08:03:44.483V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
182013-08-12 08:03:44.487V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
192013-08-12 08:03:44.490V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
202013-08-12 08:03:44.497V2NT_CONTEXT\v2LOGON54<local machine>Microsoft SQL Server Management Studio - Transact-SQL IntelliSense


Как бы только собственно логины оставить?
12 авг 13, 11:10    [14695271]     Ответить | Цитировать Сообщить модератору
 Re: Мониторинг и логирование  [new]
Glory
Member

Откуда:
Сообщений: 104751
Empirical
Как бы только собственно логины оставить?

А event_type = LOGON это что по-вашему ?
12 авг 13, 11:13    [14695287]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить