SQL Kullanıcılarını...
 
Bildirimler
Hepsini Temizle

SQL Kullanıcılarının bağlı olduğu ip adresi  

  RSS
Ahmet YURUK
(@AhmetYURUK)
Üye

Merhaba ;

SQL Server 2008 R2 kullanıyorum. Şöyle bir sorgulama yapabilir miyim acaba ?

+ O anda sistemde aktif olan / bağlı olan kullanıcıların listesi ve ip adresleri (LAN ve WAN dahil)

+ XX kullanıcısının geçmişe dönük ne zaman login oldu ve ip adresi nedir gibi bir raporlama mümkün müdür ?

Teşekkürler. 

Alıntı
Gönderildi : 30/12/2014 16:20
oldmember
(@yavuzfilizlibay)
Üye

Merhaba 

 Aşağıdaki kodu çalıştırın, bir stored procedure oluşturacak. Daha sonra bu procedure ü 

EXEC ab_who3 -- yazarak çalıştırın, 

Raporlama yapması için, bu procedure den dönen bilgileri bir tabloya insert etmeniz gerekir. Daha sonra o tablodan istenen verileri çekebilirsiniz. 

 

 

USE master;

GO

IF EXISTS

(

SELECT 1 

FROM sys.procedures

WHERE name = 'ab_who3'

)

DROP PROCEDURE dbo.ab_who3;

GO

CREATE PROCEDURE dbo.ab_who3

-- only show logins with this name

-- 'active' also works like sp_who2/ab_who2

@Loginame VARCHAR(255) = NULL, 

-- ShowBlockersOnly will reduce resultset to

-- those that are blocking or being blocked

@ShowBlockersOnly BIT = 0,

-- optional search conditions

@SearchSPID INT = NULL,

@SearchDBName NVARCHAR(255) = '%',

@SearchHostName NVARCHAR(255) = '%',

@SearchCommand NVARCHAR(255) = '%',

@SearchIP VARCHAR(16)   = '%',

@SearchProgramName NVARCHAR(255) = '%',

-- unlikely you want to see yourself,

-- but stranger things can happen

@IncludeSelf BIT = 0,

-- show system SPIDs?

@IncludeSystemSPIDs BIT = 0,

-- augment exec_sql info with DBCC INPUTBUFFER

@IncludeSQL BIT = 0,

-- displays only the first 255 characters of SQL

@Brief BIT = 0,

-- optional sorting... allowed values:

-- 'Elapsed_Time', 'CPU_Time', 

-- 'Logical_Reads', 'Reads', 'Writes'

@OrderBy VARCHAR(32) = NULL

AS

BEGIN

SET NOCOUNT ON;

SET ANSI_WARNINGS OFF;

DECLARE

@spid INT,

@sql NVARCHAR(MAX),

@briefsize SMALLINT;

-- used in conjunction with @Brief and @IncludeSQL

SET @briefsize = 255;

-- override @IncludeSQL otherwise nothing to search

IF @SearchCommand != '%'

SET @IncludeSQL = 1;

SELECT 

[Spid] = s.[session_id],

[Status] = MAX(UPPER(COALESCE

(

r.[status],

tt.[task_state],

s.[status],

''

))),

[Command] = MAX(COALESCE

(

r.[command],

r.[wait_type],

wt.[wait_type],

r.[last_wait_type], 

''

)),

[Blocked_By] = MAX(CONVERT(VARCHAR(12), COALESCE

(

RTRIM(NULLIF(r.[blocking_session_id], 0)),

'  .'

))),

[Database_Name] = MAX(DB_NAME(COALESCE

(

tl.[database_id],

r.[database_id],

t.[database_id], 

''

))),

[Logical_Reads] = MAX(COALESCE

(

NULLIF(r.[logical_reads], 0),

s.[logical_reads],

0

)),

[Reads] = MAX(COALESCE

(

NULLIF(r.[reads], 0),

NULLIF(s.[reads], 0),

c.[num_reads],

0

)),

[Writes] = MAX(COALESCE

(

NULLIF(r.[writes], 0),

NULLIF(s.[writes], 0),

c.[num_writes],

0

)),

[CPU_Time] = MAX(COALESCE

(

NULLIF(tt.[CPU_Time], 0),

NULLIF(r.[cpu_time], 0), 

NULLIF(s.[cpu_time], 0),

s.[total_scheduled_time], 

0

)),

[Elapsed_Time] = MAX(COALESCE

(

r.[total_elapsed_time],

s.[total_elapsed_time]

)),

[Row_Count] = MAX(s.[row_count]),

[Memory_In_Pages] = MAX(COALESCE

(

NULLIF(r.[granted_query_memory], 0),

s.[memory_usage],

0

)),

[Tran_Count] = MAX(COALESCE

(

t.[trancount], 

0

)),

[Lock_Count] = MAX(COALESCE

(

tl.[lockcount], 

0

)),

[Login_Name] = s.[login_name],

[Host_Name] = MAX(COALESCE

(

s.[host_name],

'  .'

)),

[IP_Address] = MAX(COALESCE

(

c.[client_net_address], 

'  .'

)),

[Program_Name] = MAX(COALESCE

(

s.[program_name], 

''

)),

[Login_Time] = MAX(COALESCE

(

s.[login_time],

c.[connect_time]

)),

[Last_Request] = MAX(COALESCE

(

r.[start_time],

s.[last_request_start_time]

)),

[Handle] = MAX(COALESCE

(

r.[sql_handle],

c.[most_recent_sql_handle]

)),

[Exec_SQL] = CONVERT(NVARCHAR(MAX), N'')

INTO

#Spids

FROM

sys.dm_exec_sessions s

LEFT OUTER JOIN

sys.dm_exec_connections c

ON c.[session_id] = s.[session_id]

LEFT OUTER JOIN 

sys.dm_exec_requests r

ON s.[session_id] = r.[session_id]

LEFT OUTER JOIN

(

SELECT

[session_id],

[database_id] = MAX([database_id]),

[trancount] = COUNT(*)

FROM

sys.dm_tran_session_transactions t

INNER JOIN

sys.dm_tran_database_transactions dt

ON

t.[transaction_id] = dt.[transaction_id]

GROUP BY

[session_id]

) t

ON s.[session_id] = t.[session_id]

LEFT OUTER JOIN

(

SELECT 

[request_session_id],

[database_id] = MAX([resource_database_id]),

[lockcount] = COUNT(*)

FROM

sys.dm_tran_locks WITH (NOLOCK)

GROUP BY

[request_session_id]

) tl

ON

s.[session_id] = tl.[request_session_id]

LEFT OUTER JOIN

sys.dm_os_waiting_tasks wt

ON 

s.[session_id] = wt.[session_id]

LEFT OUTER JOIN

(

SELECT

ot.[session_id],

ot.[task_state],

[CPU_Time] = MAX(oth.[usermode_time])

FROM

sys.dm_os_tasks ot

INNER JOIN

sys.dm_os_workers ow

ON

ot.[worker_address] = ow.[worker_address]

INNER JOIN

sys.dm_os_threads oth

ON

ow.[thread_address] = oth.[thread_address]

GROUP BY

ot.[session_id],

ot.[task_state]

) tt

ON

s.[session_id] = tt.[session_id]

WHERE

s.[login_name] = COALESCE

(

NULLIF(@Loginame, 'active'),

s.[login_name]

)

GROUP BY

s.[session_id],

s.[login_name];

-- delete rows we're not interested in

IF LOWER(@Loginame) = 'active'

BEGIN

DELETE #spids 

WHERE UPPER([Status]) = 'SLEEPING'

OR UPPER([Command]) = 'AWAITING COMMAND';

END

IF @ShowBlockersOnly = 1

BEGIN

DELETE s1

FROM #spids s1

WHERE s1.[Blocked_By] = '  .'

AND NOT EXISTS

(

SELECT 1

FROM

#spids

WHERE

[Blocked_By] = RTRIM(s1.[Spid])

);

END

IF @SearchSPID IS NOT NULL

BEGIN

DELETE #spids

WHERE [Spid] != @SearchSPID;

END

IF @SearchDBName != '%'

BEGIN

DELETE #spids

WHERE [Database_Name] IS NULL

OR [Database_Name] NOT LIKE @SearchDBName;

END

IF @SearchHostName != '%' 

OR @SearchProgramName != '%'

OR @SearchIP != '%'

BEGIN

DELETE #spids

WHERE [Host_Name] NOT LIKE @SearchHostName

OR [Program_Name] NOT LIKE @SearchProgramName

OR [IP_Address] NOT LIKE @SearchIP;

END

IF @IncludeSelf = 0

BEGIN

DELETE #spids

WHERE [Spid] = @@SPID;

END

IF @IncludeSystemSPIDs = 0

BEGIN

DELETE #spids

WHERE [Spid] <= 50;

END

CREATE TABLE #dbcc

(

a SYSNAME,

b SYSNAME,

[Input_Buffer] NVARCHAR(MAX),

[Spid] INT NULL

);

IF @IncludeSQL = 1

BEGIN

UPDATE #spids

SET [Exec_SQL] = 

(

SELECT [text] 

FROM sys.dm_exec_sql_text([handle])

);

DECLARE dbcc_cursor CURSOR 

LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR 

SELECT [Spid]

FROM #spids;

OPEN dbcc_cursor;

FETCH NEXT FROM dbcc_cursor INTO @spid;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'DBCC INPUTBUFFER('

+RTRIM(@spid)+') 

WITH NO_INFOMSGS;';

INSERT #dbcc(a,b,[Input_Buffer]) 

EXEC sp_executesql @sql;

UPDATE #dbcc

SET [Spid] = @Spid

WHERE [Spid] IS NULL;

FETCH NEXT FROM dbcc_cursor INTO @spid;

END

CLOSE dbcc_cursor;

DEALLOCATE dbcc_cursor;

IF @SearchCommand != '%'

BEGIN

DELETE #dbcc 

WHERE COALESCE([Input_Buffer], '') 

NOT LIKE @SearchCommand;

DELETE #spids

WHERE COALESCE([Exec_SQL], '')

NOT LIKE @SearchCommand;

END

IF @Brief = 1

BEGIN

UPDATE #dbcc 

SET [Input_Buffer] = 

COALESCE(LEFT([Input_Buffer], @briefsize), '');

UPDATE #spids 

SET [Exec_SQL] = 

COALESCE(LEFT([Exec_SQL], @briefsize), '');

END

END

SELECT

s.[Spid],

s.[Status],

s.[Command],

s.[Blocked_By],

s.[Database_Name],

s.[Logical_Reads],

s.[Reads],

s.[Writes],

s.[CPU_Time],

s.[Elapsed_Time],

s.[Row_Count],

s.[Memory_In_Pages],

s.[Tran_Count],

s.[Lock_Count],

s.[Login_Name],

s.[Host_Name],

s.[IP_Address],

s.[Program_Name],

s.[Login_Time],

s.[Last_Request],

s.[Exec_SQL], 

[Input_Buffer] = COALESCE(d.[Input_Buffer], '')

FROM

#spids s

LEFT OUTER JOIN

#dbcc d

ON

s.[Spid] = d.[Spid]

ORDER BY

CASE @OrderBy

WHEN 'Elapsed_Time' THEN s.[Elapsed_Time]

WHEN 'CPU_Time' THEN s.[CPU_Time] 

WHEN 'Logical_Reads' THEN s.[Logical_Reads] 

WHEN 'Reads' THEN s.[Reads] 

WHEN 'Writes' THEN s.[Writes] 

END DESC,

s.[Spid];

DROP TABLE #dbcc, #Spids;

END

GO 

CevapAlıntı
Gönderildi : 30/12/2014 17:40
oldmember
(@yavuzfilizlibay)
Üye

Genel bilgi olarak; Sql serverda bu sorgunun temelinde iki stored procedure vardır sp_who ve sp_who2 bu procedure u de inceleyebilirsiniz.

  

CevapAlıntı
Gönderildi : 30/12/2014 17:44
Ahmet YURUK
(@AhmetYURUK)
Üye

Yavuz bey bilgi için teşekkür ederim.. 

 

CevapAlıntı
Gönderildi : 30/12/2014 18:11
Çağlar ÖZENÇ
(@CaglarOZENC)
Üye

Server Properties içerisinde yer alan Security bölümündeki Login Auditing kısmındaki seçeneklere bağlı olarak SQL Server'ın son restart zamanından bu yana ( select sqlserver_start_time from sys.dm_os_sys_info ) olan kayıtları log dosyası içerisinde aşağıdaki query ile okuyabilirsin. Aynı zamanda yine login auditing bölümü içerisinde both failed & successful logins seçili olursa hem başarılı hem başarısız loginlerin bilgilerini alabilirsin. Ama Başarılı loginleri loglamak çok mantıklı bir çözüm olmaya bilir.  

EXEC sp_readerrorlog 0, 1, 'Login failed'  

CevapAlıntı
Gönderildi : 13/01/2015 16:15
Paylaş: