Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using PowerShell to access event logs for SQL Server


By:   |   Read Comments (5)   |   Related Tips: More > PowerShell


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem

This tip will introduce a few PowerShell cmdlets related to accessing and handling Windows event logs.  The event logs capture various system events that occur for both Windows and specific applications like SQL Server.  If anything goes wrong with your SQL Server box then the event logs would be one of the first places to look to help troubleshoot the issue. In this tip I will explain how to access the event logs using Windows PowerShell cmdlets.

Solution

You are probably already aware of the different event logs you can use to monitor your SQL Servers and in this tip we look at how to access and analyze these logs using PowerShell cmdlets.

With PowerShell we can read, filter, update, change, etc... using PowerShell cmdlets to manage either local or remote computers.  Open PowerShell and then we can look at some of these commands.

Opening Windows Event Viewer from PowerShell

We can use the Show-EventLog cmdlet to launch the Windows Event Viewer console. Just open the PowerShell window and type "show-eventlog". When you hit enter the event viewer console will appear.

--launch event viewer through PowerShell cmdlet
Show-eventlog
--launch eventlog of a remote computer
Show-eventlog -computername SERVERNAME

Getting a List of Event Logs

The Get-EventLog cmdlet is used to display logs which are configured on your server. If you only use get-eventlog, it will ask you to enter the event log you want to display.

--To find all logs configured on your box
Get-EventLog -list

get eventlog

Using this command we can also get the event log of a remote computer.

--Get eventlog names for a remote computer named SERVERNAME.
Get-EventLog -computername SERVERNAME -list

Viewing a Specific Event Log

Suppose we want to see only the system log. We can add "-LogName" in Get-EventLog and pass the log name as a parameter, as shown below. You can see all event log entries are showing in PowerShell console in the below screenshot.  To stop the scrolling of events you can use CTRL-C.

--Get even log entries for system event log.
Get-EventLog -LogName "system"

get eventlog of system type log

Getting the Last 20 Log Entries

If you want to check the newest log entries then you can add the "-newest" parameter. The below cmdlet will display the last 20 events for the system log.

Get-EventLog -LogName "system" -newest 20

get latest eventlog of system type log

Filtering the Event Log

We can also filter the output of these cmdlets. We can use another cmdlet "where-object" to filter any logs. Suppose we want to check all SQL server related logs from the application event log. We can add both cmdlets with the use of a pipe "|" and filter it as shown below. The output of the first cmdlet (before the pipe) is saved in $ and we can use this to access the output. Here we are searching for "SQL" in the source column.

--this will search the application log for any source values that have SQL in the string 
Get-EventLog -LogName "application" | Where-Object {$_.source -like "*SQL*"} 

filter logs for SQl server

We can also search the logs between a given time frame too by using the same cmdlets.

Viewing Specific Event Log Columns

You can see in the above screenshot that the source column is showing only SQL entries. You can use another cmdlet "select-object" to select specific columns from the event logs. Suppose you want to display only these columns: index, entry type and message.  You can use the below cmdlets to get the result.

--Filter column.
Get-EventLog -LogName "application" | Select-Object Index,entrytype, message

--You can also use all three cmdlets together to filter more accurate result. Suppose you want to filter all SQL Server related logs with only three columns.
Get-EventLog -LogName "application" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message

Here is another example to further filter the log for any SQL errors after a certain date.

Get-EventLog -LogName "application" -Message "*error*" -After "April 01, 2013 4:52:31 PM" | Where-Object {$_.source -like "*SQL*"} | format-list TimeGenerated, Source, Message

Saving the Output to a CSV File

We can also save the output of our event logs to a local or remote disk.

---export top 20 newest entries from system type event log to c:\tools\top_20_events.csv.
Get-EventLog -LogName "system" -newest 20 | export-csv c:\tools\top_20_events.csv

--export all events related to SQL for three columns to c:\tools\SQL_Server_logs.csv.
Get-EventLog -LogName "system" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message | export-csv C:\tools\sql_server_logs.csv

Saving the Output to a Text File

In a similar way you can save the data as a text file.

----export top 20 newest entries from system type event log into c:\tools\top_20_events.txt.
Get-EventLog -LogName "system" -newest 20 |out-file c:\tools\top_20_events.txt

--export all events related to SQL for three columns to c:\tools\SQL_Server_logs.txt.
Get-EventLog -LogName "system" | Where-Object {$_.source -like "*SQL*"} | select-Object Index,entrytype, message | out-file C:\tools\sql_server_logs.txt

Clearing an Event Log

The Clear-EventLog cmdlet deletes all the logs from specified event log. Here I am showing how to delete all entries for a specified event log. Open Windows PowerShell command prompt in "Run as Administrator" mode. Check the list of event logs configured on your box. Here we can see all event logs configured on our machine in the below screenshot. Suppose we want to delete all entries from the "Windows PowerShell" event log. You can see there are 118 entries in that event log.  Then you can run the clear-eventlog to delete all these entries.

--Check all configured event logs.
Get-EventLog -list
--Clear the given event log entries.
Clear-eventlog -Logname "Windows PowerShell"
--Check event logs again.
Get-EventLog -list

check the eventlogs and clear the entries

We can see that all entries have been deleted for that log.

check the eventlogs to verify the entries has been deleted
Next Steps
  • Try to use PowerShell cmdlets in your day to day activities. PowerShell is becoming more popular to be used for SQL Server support.
  • Read more tips on PowerShell.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Manvendra Singh Manvendra Singh has over 5 years of experience with SQL Server and has focused on Database Mirroring, Replication, Log Shipping, etc.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, June 11, 2013 - 3:49:54 AM - Manvendra Back To Top

Hi Mary,

 

You can get the 1000 lines by running below code:

Get-EventLog -LogName "system" -newest 1000

 

@SQLDBA- If you are talking about pulling the data from logs to sql table via powershell  then yes we can get this done.

 

@Van- Yes most of the logs can be captured by event viewer. Consult with windows admins to configure in a way so tht u could get the required logs.


Monday, June 10, 2013 - 2:59:11 AM - mary jane Back To Top

 

Hi Manvendra,

 

Thank you for the blog you put together. 

 

I need some help in getting logs from a log file which is like 55GB and cannot be opened. I would like to get the last 1000lines of the log file. Please hep in doing so.


Friday, May 31, 2013 - 8:51:19 AM - SQLDBA Back To Top

Hi Manvendra,

Thanks for your tips on powershell. If you know how to pull data from PowerShell to SQL table can you let us know.

 


Wednesday, May 15, 2013 - 3:31:58 AM - van Back To Top

Hi Manvendra,

good day to you.

I think this could really help a lot with my recent projects.

1st : Im trying to get the logs of mssql using this event viewer which already answer to this article. Im not really familiar with mssql but I would like to know if ALL the logs of mssql are really capturing by windows event viewer? or aside from the event viewer there are still logs from the application mssql itself which doesnt log to eventviewer?



Wednesday, May 01, 2013 - 5:03:50 PM - Rajesh Back To Top

 

Thanks Manvendra. Keep posting your wounderful SQL Tipls.

like learn more on powrshell.

 

Thanks

Rajesh


Learn more about SQL Server tools