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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply