June 13, 2013 at 8:45 pm
Comments posted to this topic are about the item Check SQL Error Logs using Powershell
July 26, 2013 at 10:53 am
Hi,
I am receiving the following error when I run the script...
7/26/2013 12:51:31 PM - Checking Agent Error Logs on BOSSQL03...
Unable to read SQL Agent error log from server @{serverName=BOSSQL03}
July 26, 2013 at 12:50 pm
Using SSMS, does this work:?
EXEC master..xp_readerrorlog
August 26, 2014 at 1:46 pm
Thanks for sharing. That's a relatively quick and dirty way to get the error log entries from multiple systems. I don't yet have PS3 installed, so I modified your code to use sqlps and the invoke-sqlcmd cmdlet and it works as desired. Hope you don't mind me sharing that. If so, I can remove the post.
<#
_checkErrorLogs.ps1
Description:
Author: Jim Breffni.
This script displays the errorlog entries for all selected servers for the last n days.
The script can take a few minutes to run if the error logs are large and you are looking back over several days.
S.Kusen 2014-08-26: Modified to use SQLPS and invoke-sqlcmd for powershell v2 environment.
Requirements:
Module invokesqlquery needs to be installed.
1. Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com/
#>
cls
import-module SQLPS
$today = (get-date).toString()
$all = @()
$lookback = ((get-date).adddays(-1)).ToString() # look back n days from current time
# Load up the list of servers to check
# Use this code if you can get your list of servers from a sql database
$servers = invoke-sqlcmd -Query @'
select distinct serverName from YOUR_TABLE
order by serverName
'@ -serverinstance YOUR_SERVER -database YOUR_DATABASE
# remove the comment from the code below if you want to supply the list of servers as an array
#$servers = 'sql01', 'sql02' | select-object @{Name = 'serverName'; Expression = {$_}}
foreach ($server in $servers) {
"$((get-date).toString()) - Checking SQL Error Logs on $($server.servername)..."
try {
$all += invoke-sqlcmd -query "EXEC master..xp_readerrorlog 0, 1, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
where-object {$_.Text -match 'error|fail|warn|kill|dead|cannot|could|stop|terminate|bypass|roll|truncate|upgrade|victim|recover'} | `
where-object {$_.Text -notmatch 'setting database option recovery to'}
}
catch {"Unable to read SQL error log from server $server"}
} # foreach ($server in $servers) {
$all | out-gridview -title ("$($all.Count) errors in SQL Server Error Logs. From $lookback to $today")
$all = @()
foreach ($server in $servers) {
"$((get-date).toString()) - Checking Agent Error Logs on $($server.servername)..."
try {
$all += invoke-sqlcmd -query "EXEC master..xp_readerrorlog 0, 2, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
where-object {[datetime]$_.LogDate -ge $lookback}
}
catch {"Unable to read SQL Agent error log from server $server"}
} # foreach ($server in $servers) {
$all | out-gridview -title ("$($all.Count) errors in SQL Agent Error Logs. From $lookback to $today")
August 26, 2014 at 1:58 pm
nice, thank you!
September 1, 2014 at 7:20 am
09/01/2014 13:17:19 PM - Checking Agent Error Logs on SOME SERVERNAME...
Unable to read SQL Agent error log from server @{serverName=SOME SERVERNAME}
I'm experiencing the same problem with both methods: Dynamically or static
Any suggestions
September 1, 2014 at 7:31 am
Without seeing your actual script the error could be caused by a permissions issue...
September 2, 2014 at 12:19 am
I'm sysadmin when running this and this is the script
<#
_checkErrorLogs.ps1
Description:
Author: Jim Breffni.
This script displays the errorlog entries for all selected servers for the last n days.
The script can take a few minutes to run if the error logs are large and you are looking back over several days.
Requirements:
Module invokesqlquery needs to be installed.
1. Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com/
#>
cls
import-module invokesqlquery
$today = (get-date).toString()
$all = @()
$lookback = ((get-date).adddays(-1)).ToString() # look back n days from current time
# Load up the list of servers to check
# Use this code if you can get your list of servers from a sql database
$servers = invoke-sqlquery -query @'
select distinct MYCOLUMN from dbo.MYTABLE
order by MYCOLUMN
'@ -server MY SERVER -database MYDB
# remove the comment from the code below if you want to supply the list of servers as an array
#$servers = 'sql01', 'sql02' | select-object @{Name = 'serverName'; Expression = {$_}}
foreach ($server in $servers) {
"$((get-date).toString()) - Checking SQL Error Logs on $($server.servername)..."
try {
$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 1, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
where-object {$_.Text -match 'error|fail|warn|kill|dead|cannot|could|stop|terminate|bypass|roll|truncate|upgrade|victim|recover'} | `
where-object {$_.Text -notmatch 'setting database option recovery to'}
}
catch {"Unable to read SQL error log from server $server"}
} # foreach ($server in $servers) {
$all | out-gridview -title ("$($all.Count) errors in SQL Server Error Logs. From $lookback to $today")
$all = @()
foreach ($server in $servers) {
"$((get-date).toString()) - Checking Agent Error Logs on $($server.servername)..."
try {
$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 2, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `
where-object {[datetime]$_.LogDate -ge $lookback}
}
catch {"Unable to read SQL Agent error log from server $server"}
} # foreach ($server in $servers) {
$all | out-gridview -title ("$($all.Count) errors in SQL Agent Error Logs. From $lookback to $today")
This is the result:
02-09-2014 08:14:52 - Checking SQL Error Logs on ...
Unable to read SQL error log from server @{instancename=SERVERNAME1\INSTANCE1 }
02-09-2014 08:15:07 - Checking SQL Error Logs on ...
Unable to read SQL error log from server @{instancename=SERVERNAME1\INSTANCE2 }
02-09-2014 08:15:07 - Checking SQL Error Logs on ...
Unable to read SQL error log from server @{instancename=SERVERNAME2\INSTANCE3 }
02-09-2014 08:15:07 - Checking Agent Error Logs on ...
Unable to read SQL Agent error log from server @{instancename=SERVERNAME1\INSTANCE1 }
02-09-2014 08:15:07 - Checking Agent Error Logs on ...
Unable to read SQL Agent error log from server @{instancename=SERVERNAME1\INSTANCE2 }
02-09-2014 08:15:07 - Checking Agent Error Logs on ...
Unable to read SQL Agent error log from server @{instancename=SERVERNAME2\INSTANCE3 }
I ran the script from ISE both as administrator and my account (which is sysadmin)
What permissions do I need other than that?
Thanks for your support
September 2, 2014 at 12:24 am
and EXEC master..xp_readerrorlog runs fine on all instances 2008r2, 2012sp1, 2014
I can see from the trace and the output that I'm able to retrieve the serverlist from the table so that part should be in order
May 7, 2015 at 7:04 am
Thanks for the script.
November 18, 2015 at 9:44 am
Jim Breffni or anyone, anyway to add logic to include/exclude events per server. Example if we have a misbehaving server that has bogus errors how can we exclude the events just for this server?
Love the script use it daily....
November 18, 2015 at 12:36 pm
Learn_something_new_everyday (11/18/2015)
Jim Breffni or anyone, anyway to add logic to include/exclude events per server. Example if we have a misbehaving server that has bogus errors how can we exclude the events just for this server?Love the script use it daily....
Not a very clean way to do it, but you could filter on the line itself (without refactoring all of the code to accept exceptions) by changing the "out-gridview" lines to something like this:
$allfiltered = $all | where-object {$_.ProcessInfo -notlike "*Backup*"}
$allfiltered | out-gridview -title ("$($all.Count) errors in SQL Server Error Logs. From $lookback to $today")
$allfiltered = $all | where-object {$_.ProcessInfo -notlike "*Backup*"}
$allfiltered | out-gridview -title ("$($all.Count) errors in SQL Agent Error Logs. From $lookback to $today")
The goal of doing the filtering that way would be to filter the $all variable that contains all of the errors.
Hope that helps get you in the right direction.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy