Audit Login/Logout
-
Does anyone has a script that will keep track of when users login in or logout as well provide the date, I know you can use Profiler but I don't think you can get a date just duration.
-
Have you checked out c2 auditing?
-
Yes, I have the option turn on.
-
What information are you missing from those logs?
-
There is no log, a log is suppose to be created in the \msql\data directory but there isn't one . I have the C2 option turn on(set to 1) and the server has been rebooted.
-
I see. I've never met that problem so I'll let the other dbas take this one. Good luck
. -
I'm sorry about that, I was able to find the report. The report list:
Database spid loginame program_name and hostname, all the information I want but not for the whole day. If I open the report it will only list the information in the last 30 minutes, how can I can the information for the whole day (say from 9:00am - 6:00pm).
-
Hi,
this is what i use (db management is my db for managerial things).
The procedure sp_start_audit_logins is startet by a job at 00:00 each day:
CREATE PROCEDURE sp_start_audit_logins AS
declare @dumpfile nvarchar(128) -- Full Path
declare @name varchar(100) -- Filename
declare @Backupdir sysname -- path to directory
declare @P1 int
declare @ende datetime
-- Variablen füllen
select @Backupdir = 't:\backups\'
select @name=@@SERVERNAME+'_LoginAudit_'+DATENAME(dw,getdate())+'_'+REPLACE(STR(DATEPART(dd,getdate()),2)+'.'+STR(DATEPART(mm,getdate()),2)+'.'+STR(DATEPART(yy,getdate()),4)+'_'+STR(DATEPART(hh,getdate()),2)+'.'+STR(DATEPART(mi,getdate()),2)+'_Uhr',' ','0')
select @dumpfile = @Backupdir +@name
select @ende = CONVERT(DATETIME, CONVERT(VARCHAR(12),GETDATE()) + '23:59:00')
exec management.dbo.UP_AutoImport_TraceFiles @Backupdir
set @P1=0
exec sp_trace_create @P1 output, 2, @dumpfile, NULL, @ende
select @P1
--You will need this output to be able to stop trace otherwise shutdown SQL.
--@x is a bit value, for some reason you must submit parameter 4 this way to get to work properly.
declare @x bit
set @x = 1
--Login
exec sp_trace_setevent @P1,14,3, @x
exec sp_trace_setevent @P1,14,10, @x
exec sp_trace_setevent @P1,14,11, @x
exec sp_trace_setevent @P1,14,12, @x
exec sp_trace_setevent @P1,14,14, @x
exec sp_trace_setevent @P1,14,34, @x
exec sp_trace_setevent @P1,14,35, @x
--Logout
exec sp_trace_setevent @P1,15,3, @x
exec sp_trace_setevent @P1,15,6, @x
exec sp_trace_setevent @P1,15,8, @x
exec sp_trace_setevent @P1,15,9, @x
exec sp_trace_setevent @P1,15,10, @x
exec sp_trace_setevent @P1,15,11, @x
exec sp_trace_setevent @P1,15,12, @x
exec sp_trace_setevent @P1,15,13, @x
exec sp_trace_setevent @P1,15,14, @x
exec sp_trace_setevent @P1,15,16, @x
exec sp_trace_setevent @P1,15,17, @x
exec sp_trace_setevent @P1,15,18, @x
exec sp_trace_setevent @P1,15,34, @x
exec sp_trace_setevent @P1,15,35, @x
exec sp_trace_setevent @P1,15,40, @x
--LoginFailed
exec sp_trace_setevent @P1,20,3, @x
exec sp_trace_setevent @P1,20,10, @x
exec sp_trace_setevent @P1,20,11, @x
exec sp_trace_setevent @P1,20,12, @x
exec sp_trace_setevent @P1,20,14, @x
exec sp_trace_setevent @P1,20,34, @x
exec sp_trace_setevent @P1,20,35, @x
--Start trace
exec sp_trace_setstatus @P1, 1
/*
--Parameter 1 is the trace id to stop and remove from queue
declare @P1 int
set @P1=0
exec sp_trace_setstatus @P1,0
exec sp_trace_setstatus @P1,2
*/
--Look at SQL BOL for the relative information of each process.
GO
create procedure UP_AutoImport_TraceFiles
@TraceFilePath varchar(255)
-- Create By: Regan Galbraith
-- Create On: 2004-12-28
-- Purpose:
-- This stored procedure was written to facilitate the automatic import of profiler trace files.
--
-- Example:
-- exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'
--
-- Possible future additions:
-- 1> simple enhancement to specify DB to store data, and table.
-- 2> accepting a parameter instead of apply the default .trc. Use the .trc as default
-- 3> implementing default value's for dir's, so that it can run without parm's ... good or bad?
--
-- Change Control: version 1 - creation and adding of comment
--
--
as
begin
set nocount on
set quoted_identifier off
create table #TraceFileList(TraceFileName varchar(128))
create table #DirExists
(FileExists int,
FileIsDir int,
ParentDirExists int)
Create table #filedetails(
alternatename char(20),
size char(20),
creation_date char(20),
creation_time char(20),
last_written_date char(20),
last_written_time char(20),
last_accessed_date char(20),
last_accessed_time char(20),
attributes char(20)
)
declare @TraceFileName varchar(255)
declare @dirSQL varchar(255)
declare @ErrorDir varchar(6)
declare @cmd varchar(999)
insert into #DirExists exec master..xp_fileexist @TraceFilePath
if not exists(select 'Dir Exists' from #DirExists where fileExists = 0 and FileIsDir = 1 and ParentDirExists = 1)
begin
set @ErrorDir = 'Data'
goto DirError
end
set @dirSQL = 'dir '+@TraceFilePath+'*.trc /b'
--print @dirSQL
delete from #TraceFileList
insert into #TraceFileList
exec master..xp_cmdshell @dirSQL
--select * from #TraceFileList
declare TraceFileList cursor for select TraceFileName from #TraceFileList
open TraceFileList
fetch next from TraceFileList into @TraceFileName
while @@fetch_status <> -1
begin
-- this assumes a naming standard where your back names are databasename_ and then more detail. If you have
-- a naming standard for backups of simply databasename.bak, then change this to charindex ('.' , for example.
select @TraceFileName = @TraceFilePath+@TraceFileName
select 'Files Processed : ' +@TraceFileName
if @TraceFileName is not null
begin
INSERT #filedetails EXEC master.dbo.xp_getfiledetails @TraceFileName
if (select top 1 size from #filedetails) > 0 BEGIN
INSERT INTO [management].[dbo].[profiler_log]
([EventClass], [DatabaseID], [TextData], [SPID], [Duration], [EndTime],
[Reads], [Writes], [CPU], [TransactionID], [NTUserName], [NTDomainName],
[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime],
[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID],
[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode],
[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName],
[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions])
SELECT [EventClass],[DatabaseID],Cast([TextData] as varchar(6000)) as TextData,
[SPID],[Duration],[EndTime],[Reads],[Writes],[CPU], [TransactionID], [NTUserName], [NTDomainName],
[HostName], [ClientProcessID], [ApplicationName], [LoginName], [StartTime],
[Permissions], [Severity], [EventSubClass], [ObjectID], [Success], [IndexID],
[IntegerData], [ServerName], [ObjectType], [NestLevel], [State], [Error], [Mode],
[Handle], [ObjectName], [DatabaseName], [FileName], [OwnerName], [RoleName],
[TargetUserName], [DBUserName], [TargetLoginName], [ColumnPermissions]
FROM ::fn_trace_gettable(@TraceFileName, default)
SELECT @cmd = 'move ' + @TraceFileName + ' ' + @TraceFilePath + 'Alte_Traces'
EXEC master.dbo.xp_cmdshell @cmd
END
-- where max(DATALENGTH(textdata)) <= 6000
end
delete from #filedetails
fetch next from TraceFileList into @TraceFileName
end
close TraceFileList
deallocate TraceFileList
goto finish
DirError:
Print 'An error with the '+@errordir+' Directory given - please check that it exists. Thanks'
drop table #TraceFileList
drop table #DirExists
drop table #filedetails
RETURN -1
Finish:
drop table #TraceFileList
drop table #DirExists
drop table #filedetails
RETURN 0
end
-- exec UP_AutoImport_TraceFiles 'c:\Auto_Prof\'
/****END SP Script****/
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[profiler_log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[profiler_log]
GO
CREATE TABLE [dbo].[profiler_log] (
[RowNumber] [int] IDENTITY (1, 1) NOT NULL ,
[DateEntered] [datetime] NOT NULL ,
[EventClass] [int] NULL ,
[DatabaseID] [int] NULL ,
[TextData] [varchar] (6000) COLLATE Latin1_General_CI_AS NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[EndTime] [datetime] NULL ,
[Reads] [bigint] NULL ,
[Writes] [bigint] NULL ,
[CPU] [int] NULL ,
[TransactionID] [bigint] NULL ,
[NTUserName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[NTDomainName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[HostName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[LoginName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[StartTime] [datetime] NULL ,
[Permissions] [int] NULL ,
[Severity] [int] NULL ,
[EventSubClass] [int] NULL ,
[ObjectID] [int] NULL ,
[Success] [int] NULL ,
[IndexID] [int] NULL ,
[IntegerData] [int] NULL ,
[ServerName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ObjectType] [int] NULL ,
[NestLevel] [int] NULL ,
[State] [int] NULL ,
[Error] [int] NULL ,
[Mode] [int] NULL ,
[Handle] [int] NULL ,
[ObjectName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[DatabaseName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[FileName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[OwnerName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[RoleName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[TargetUserName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[DBUserName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[TargetLoginName] [varchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[ColumnPermissions] [int] NULL
) ON [PRIMARY]
GO
------------------------------------------------------------
regards
karl
Best regards
karl
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply