Dear Professional,

I am pretty much confused in capturing each event of login in to Historical
databse and in one point of time we are expecting billions of records.
Before dig into that I need expert advice from you.

There are so many events that needs to be capture in historical database for
instance.

When User Login
Login Data
Login Time
User ID
Platform ID
Version ID
Operating System

When Failed Password
User ID
Wronge Password
Date Time

When Wrong Secret Answer
User ID
Secret Question
Wrong Secret Answer
Date Time


There are so many events that needs to be capture in the Historical
Database, I am thinking I have to make historical database more robust so
that in the future if I need data for reporting purpose I can easily get it
from Historical DB, here is my thought in terms of Table designing.


Event (table)

Event ID (1)
Event (User Log IN)


User History (table)

Event ID
User ID
Platform ID
Login Date
Login Time
Logout Time
Operating System

I want to create generic table so that I can use it for different purpose /
events, now the problem is if I create User History table and in that I have
to capture more information than the user change secret question. How I have
to make table structure more generic so that I can use it for any events

Thanks

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote:

if you are using sql server 2005 sp2 then you can use ddl auditing. In SQL
2005, a trigger can be created in order to perform any action upon a DDL
statement.

first, create a table to store event log in database

CREATE TABLE admin.logs (
LogID int IDENTITY(1,1),
EventTime DATETIME,
EventType VARCHAR(100),
LoginName VARCHAR(100),
HostName VARCHAR(100),
AppName VARCHAR(255),
Event_Data XML)

create trigger ServerWideLoginLogs
on all server
with execute as self
for LOGON
as begin
DECLARE @event XML
SET @event = eventdata()
INSERT INTO Admin_Log.admin.logs
(EventTime,EventType,LoginName,HostName,AppName,Ev ent_Data)
VALUES(CAST(CAST(@event.query('/EVENT_INSTANCE/PostTime/text()') AS
VARCHAR(64)) AS DATETIME),

CAST(@event.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),

CAST(@event.query('/EVENT_INSTANCE/ClientHost/text()') AS
VARCHAR(100)),

APP_NAME(),

@event)

end

GO

The Event_Data field is populated by the EVENTDATA() function from our
INSERT statement in our trigger. In this case, the eventdata() function
returns XML data related to the connection, including the date and time of
the login, the server name, the user ID, and the machine system ID.

i am sure it will help you .

ones u have created table now create a trigger to capture events in database
"Noor" wrote: