mailing list archives
RE: SQL Queries of Windows Event Logs
From: "Painter, Marcus J." <MARCUS.J.PAINTER () saic com>
Date: Wed, 8 Jun 2005 15:53:11 -0400
I am also working on importing the event logs into a DB.
Two problems I have come across:
1. Importing does not like the "Time" format
2. The Event "Description" is not archived with the log... I need this
in my records. I know that if you import a .evt log back into the
Event Viewer, it will allow you to view the description, however it does
not record it when you save it as a .txt or .csv file.
From: Joe Quigley [mailto:jquigley () iir-central com]
Sent: Tuesday, June 07, 2005 10:48 AM
To: security-basics () securityfocus com
Cc: Harlan Carvey
Subject: RE: SQL Queries of Windows Event Logs
A couple of people have asked for more information.
1. Auditing is turned on, via GPO, for logon events (success and
failure) and account management.
2. I am interested in events that could indicate inappropriate use of
the network. Some examples would be security events 517, 624, 632, 636,
642, 644, 660, 675, 676, 680, and 681. I would like to try and filter
some of the "background noise" out of the reports, if possible. An
example of the "noise" would be a single 675 error from one machine.
That event will occur when a Kerberos ticket expires, but should not
happen repetitively pre machine. If it happens more then once, I'd like
to see it in my daily report.
3. Database Structure. I import each type of log (security, app, system,
DS, DNS) into its own table. Here is the SQL script used to create the
CREATE TABLE [dbo].[Security] (
[Log] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date] [datetime] NULL ,
[Time] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
[EventID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS
[Type] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
[Category] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
[UserID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS
[Computer] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
I used the database structure and import routine from this document if
anyone is curious.
(Thanks to the author, Gregory Lalla)
I apologize for the initial lack of information. If I missed anything
else that may be of use, please let me know.
From: Harlan Carvey [mailto:keydet89 () yahoo com]
Sent: Tuesday, June 07, 2005 7:48 AM
To: Joe Quigley
Subject: re: SQL Queries of Windows Event Logs
Since SQL is not my strong point, I'd like to ask
the list if anyone has seen (or would be willing to
share) a recommended set of reports/queries for
tracking potential security issues (failed logins,
AD object changes, etc).
there are a couple of things at work here...
First, are you sure that the logs are configured to
support the queries you've mentioned above? For
example, are you logging failed login attempts (sorry,
had to ask)?
Second, what are the events you're interested in? Are
there any others besides the ones you've listed above?
Finally, before anyone can give you the SQL queries
you're asking for, you're going to have to make the
structure of your database public. Are the fields
dumped into a single table?
Just stuff to consider, that's all...
Harlan Carvey, CISSP
"Windows Forensics and Incident Recovery"