Security Basics 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. Any help?? Thanks, Marcus -----Original Message----- 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 Hello, 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 tables. 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 NULL , [EventID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Type] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Category] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UserID] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Computer] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [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. http://www.sans.org/rr/whitepapers/logging/902.php (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. Regards, Joe -----Original Message----- 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 Joe,
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 ------------------------------------------ Harlan Carvey, CISSP "Windows Forensics and Incident Recovery" http://www.windows-ir.com http://windowsir.blogspot.com ------------------------------------------
Current thread:
- SQL Queries of Windows Event Logs Joe Quigley (Jun 06)
- Re: SQL Queries of Windows Event Logs Adam Jones (Jun 08)
- <Possible follow-ups>
- RE: SQL Queries of Windows Event Logs Joe Quigley (Jun 08)
- RE: SQL Queries of Windows Event Logs Painter, Marcus J. (Jun 08)