How to centralize your SQL Server Event Logs.

Question

Post reply

How to centralize your SQL Server Event Logs.

Viewing 15 posts - 16 through 30 (of 72 total)

You must be logged in to reply to this topic. Login to reply

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 1

GNUZEN (11/8/2010)

Great post!

I usually did the same way before SQL Server 2008.

Now I've implemented central monitor solution using PowerShell and SQL Server central management feature.

For security reason I would try not using VBS. Using native SQL Server central management also enables you to manage and configure in one place.

my two cents.

with SQL 2008 can you import event logs from non-sql servers into a database?

peleg SSCrazy Points: 2611 More actions · Answer 2

vbscript is not secure enough, but powershell has it's dangerous too.

i think that doing it in PowerShell is better, if you start from Zero, beacuse it's being added more abilities all the time (while vbscript is old and not updated).

to dump data using powershell to a db, you can use a dll which will do the work for you :

Bulk import text files using .net 2.0 SqlBulkCopy class in C#

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

alen teplitsky

SSC-Dedicated

Points: 30014

November 8, 2010 at 9:07 am

#1247711

so you have to dump the log to a text file first and then use SSIS or bcp to import it?

Matt Hayes Newbie Points: 3 More actions · Answer 4

This example seems to work a treat on two of my SQL Servers (one a 2005 box and the other a 2008 box), however I cant seem to get any events forwarding on another 2008 box.

I've enabled Event Forwarding in SQL Agent on the server and have verified the hostname and that all events are enabled but this doesnt seem to have resolved this.

Are there any common pitfulls I may have overlooked?

Thanks

Matt

Geoff A SSChampion Points: 11511 More actions · Answer 5

Matt Hayes (11/8/2010)

This example seems to work a treat on two of my SQL Servers (one a 2005 box and the other a 2008 box), however I cant seem to get any events forwarding on another 2008 box.

I've enabled Event Forwarding in SQL Agent on the server and have verified the hostname and that all events are enabled but this doesnt seem to have resolved this.

Are there any common pitfulls I may have overlooked?

Thanks

Matt

Hi Matt,

more than likely, MSDTC is not configured.

On the server that IS set to forward events and it is not working, look at the application event log to confirm.

Locker916 SSC Journeyman Points: 87 More actions · Answer 6

Thanks very much for the article. I'm having an issue and I'm hoping someone can offer some assistance with troubleshooting.

I followed the steps in the article and have created the database (DBA), table (EventLogStaging), the script (E:\Administration\Scripts\EventLog2DB.vbs) and the SQL Agent Job (Changed the @command as indicated with the proper path).

The job is running successfully.

The test command (raiserror ('working great',16,1) with log) inserts a record into the Application event log.

Problem, no data is being copied to the EventLogStaging table when the Agent Job/Script are run.

I am running on SQL 2005/Window Server 2003 and I'm using Mixed authentication. Also tried changing the Agent Job owner to the same owner as my other, working jobs.

Any troubleshooting assistance would be most appreciated.

Thanks!

Geoff A SSChampion Points: 11511 More actions · Answer 7

Locker916 (11/8/2010)

Thanks very much for the article. I'm having an issue and I'm hoping someone can offer some assistance with troubleshooting.

I followed the steps in the article and have created the database (DBA), table (EventLogStaging), the script (E:\Administration\Scripts\EventLog2DB.vbs) and the SQL Agent Job (Changed the @command as indicated with the proper path).

The job is running successfully.

The test command (raiserror ('working great',16,1) with log) inserts a record into the Application event log.

Problem, no data is being copied to the EventLogStaging table when the Agent Job/Script are run.

I am running on SQL 2005/Window Server 2003 and I'm using Mixed authentication. Also tried changing the Agent Job owner to the same owner as my other, working jobs.

Any troubleshooting assistance would be most appreciated.

Thanks!

I would try this to troubleshoot your issue;

1. disable the job created to run the vbs script.

2. open a command prompt and enter ;

cscript "E:\Administration\Scripts\EventLog2DB.vbs"

3. the DOS box should change to say this;

Microsoft (R) Windows Script Host Version 5.7

4. run the raiserror command again from SSMS and watch the command propmt window.

if the DOS box (command prompt) window stop working with an error, tell me what it is.

if the DOS box remains with the same text as above, let me know that.

Locker916 SSC Journeyman Points: 87 More actions · Answer 8

E:Administration\Scripts\EventLog2DB.vbs(4, 1) Microsoft OLE DB Provider for SQL Server: [DBNETLIB][ConnectionOption <Connect<>>.]Specified SQL server not found.

Looks like a problem identifying the sql server. Here is the fourth line of my EventLog2DB.vbs which seems to be the connection string:

objConn.Open "Provider=SQLOLEDB.1;Data Source=.;Initial Catalog=DBA;Integrated Security=SSPI"

Thanks again!

Geoff A (11/8/2010)

I would try this to troubleshoot your issue;

1. disable the job created to run the vbs script.

2. open a command prompt and enter ;

cscript "E:\Administration\Scripts\EventLog2DB.vbs"

3. the DOS box should change to say this;

Microsoft (R) Windows Script Host Version 5.7

Copyright (C) Microsoft Corporation. All rights reserved.

4. run the raiserror command again from SSMS and watch the command propmt window.

if the DOS box (command prompt) window stop working with an error, tell me what it is.

if the DOS box remains with the same text as above, let me know that.

Geoff A SSChampion Points: 11511 More actions · Answer 9

Geoff A

SSChampion

Points: 11511

November 8, 2010 at 11:03 am

#1247784

is your SQL Server running on the default instance or a named instance?

Locker916 SSC Journeyman Points: 87 More actions · Answer 10

Locker916

SSC Journeyman

Points: 87

November 8, 2010 at 11:05 am

#1247786

Fixed it.

I changed Datasource=. to Datasource=localhost in the adodb connection string and now the records are being written to the table when I run the script directly from the command line.

I'm assuming it will now work from the Agent which I"ll test now. Thanks very much for the assistance.

Marco Default port Points: 1408 More actions · Answer 11

I also found this. I haven't tried it yet, but reading the eventlogs through SSIS is also a very nice method.

http://www.softpedia.com/get/Internet/Servers/Server-Tools/SSIS-Event-Log-Source.shtml

This package provides an SSIS 2005 Data Source component for loading Windows 2003 / XP event logs (.evt) into SQL Server 2005 for analysis.

Regards,

Marco

Geoff A SSChampion Points: 11511 More actions · Answer 12

mchofman (11/8/2010)

I also found this. I haven't tried it yet, but reading the eventlogs through SSIS is also a very nice method.

http://www.softpedia.com/get/Internet/Servers/Server-Tools/SSIS-Event-Log-Source.shtml

This package provides an SSIS 2005 Data Source component for loading Windows 2003 / XP event logs (.evt) into SQL Server 2005 for analysis.

Regards,

Marco

Thanks for the link Marco.

the solution here will work on windows 2000 through Windows 2008 R2 and probably beyond....

it also will work with SQL 2000 through SQL 2008 R2 and probably beyond. 😉

jumarcil Grasshopper Points: 14 More actions · Answer 13

Hi Guys;

I am getting the same error as Newbie: "invalid query" The DB is created; so is the sql agent job; running in mixed mode.

I am using Script engine 5.6; I have notice that you use 5.7. would it make a difference ?

Any help appreciated since it really is a nice little app; I manage quite a lot of SQL servers throughout the company and it would really simplify my work.

Regards

Jules

Geoff A SSChampion Points: 11511 More actions · Answer 14

jumarcil (11/9/2010)

Hi Guys;

I am getting the same error as Newbie: "invalid query" The DB is created; so is the sql agent job; running in mixed mode.

I am using Script engine 5.6; I have notice that you use 5.7. would it make a difference ?

Any help appreciated since it really is a nice little app; I manage quite a lot of SQL servers throughout the company and it would really simplify my work.

Regards

Jules

the script engine will make no difference.

did you see what newbie did to resolve his issue. he created an alias in SQL Server Configuration Manager because he was running a named instance on a non-standard port. Do you happening to be running your SQL Server instance on a named instance or a port other than 1433?

jumarcil Grasshopper Points: 14 More actions · Answer 15

Geoff A (11/10/2010)

jumarcil (11/9/2010)

Hi Guys;

I am getting the same error as Newbie: "invalid query" The DB is created; so is the sql agent job; running in mixed mode.

I am using Script engine 5.6; I have notice that you use 5.7. would it make a difference ?

Any help appreciated since it really is a nice little app; I manage quite a lot of SQL servers throughout the company and it would really simplify my work.

Regards

Jules

the script engine will make no difference.

did you see what newbie did to resolve his issue. he created an alias in SQL Server Configuration Manager because he was running a named instance on a non-standard port. Do you happening to be running your SQL Server instance on a named instance or a port other than 1433?

I will try to create an alias and will verify If my default instance is running on default port 1433 as I have many instance on that box.

Regards

JUles

UPDATE :

I found why it isn't working on my server. If I used the "like" operator in the query it generates an error.

Ex: TargetInstance.SourceName like 'MSSQL%' will generate a script error but TargetInstance.SourceName = 'MSSQL' works fine. I will just create a couple of "or" clause for the amount of instances that I have on the box and It will work fine.

Thanks

Jules