How to Obtain Powershell to Read a SQL Server Error Log

advertisements

I need your help with PowerShell today. I have a SQL Server instance (on a server named VMDEV-APP11) configured as a Central Management Server (CMS) in which I have registered all my Dev/Test/Prod SQL instances and I want to use PowerShell to read the SQL Server error logs from all my servers. I have a query which retrieves all my SQL Server instances but when I pipe the output of this into a call to ReadErrorLog, I get an error.

This is the code that retrieves my list of the SQL Server instances which are registered on my CMS (note that I exclude my SQL Server 2000 instances):

Set-Location D:\MSSQL11.MSSQLSERVER\CMS
# Define functions to query SQL Server and write data to a SQL table
. ./invoke-sqlcmd2.ps1
. ./write-datatable.ps1

Invoke-sqlcmd2 -ServerInstance "VMDEV-APP11" -Database dba -Query "select s.server_name from msdb.dbo.sysmanagement_shared_registered_servers s, msdb.dbo.sysmanagement_shared_server_groups g where s.server_group_id = g.server_group_id and g.name not like '2000%'" 

server_name
-----------
INF-SRV14
VMDEV-APP15
NEX-SRV48
...

And this is what I thought would work and the error I actually get:

Invoke-sqlcmd2 -ServerInstance "VMDEV-APP11" -Database dba -Query "select s.server_name from msdb.dbo.sysmanagement_shared_registered_servers s, msdb.dbo.sysmanagement_shared_server_groups g where s.server_group_id = g.server_group_id and g.name not like '2000%'" | foreach-object { $_.server_name.ReadErrorLog() }

Error:

Method invocation failed because [System.String] does not contain a method named 'ReadErrorLog'.
At D:\MSSQL11.MSSQLSERVER\CMS\db_errorlog.ps1:38 char:284
+ ... reach-object { $_.server_name.ReadErrorLog() }
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

I suspect I need to convert the string returned by my query to another type of object (a server name perhaps?) in order to get the ReadErrorLog() call to work but I don't know how to do that.

Any suggestions?

Any help would be greatly appreciated.

Ken


You're trying to invoke the ReadErrorLog() method on a DataRow column value, which is a string.

What you need to do is something like this:

Import-Module sqlps -DisableNameChecking

Invoke-sqlcmd2 -ServerInstance "VMDEV-APP11" -Database dba -Query "select s.server_name from msdb.dbo.sysmanagement_shared_registered_servers s, msdb.dbo.sysmanagement_shared_server_groups g where s.server_group_id = g.server_group_id and g.name not like '2000%'" | `
foreach-object {
    $server = $_.server_name
    $logs = (get-item SQLSERVER:\sql\$server\default).ReadErrorLog()
    # $logs is a DataTable so you can iterate the rows however you wish
}

This assuming all your servers are default instances, otherwise you may need to fiddle around a bit more to target specific instances.