Have you tried:
$instanceNameList = invoke-Sqlcmd
-query
"SELECT [server_name] as Name
FROM [msdb].[dbo]
.[sysmanagement_shared_registered_servers_internal] SSRSI
join [msdb].[dbo].[sysmanagement_shared_server_groups_internal] SSSGI
on SSRSI.server_group_id = SSSGI.server_group_id
where SSSGI.server_group_id = '15' or SSSGI.server_group_id = '14'
or SSSGI.server_group_id = '13' or SSSGI.server_group_id = '12'"
-serverinstance "consoleserver"
$results = @()
foreach($instanceName in $instanceNameList)
{
try
{
$results += Invoke-Sqlcmd -Query
"use msdb;
go;
SELECT @@servername, items.subject, items.last_mod_date,l.description
FROM dbo.sysmail_faileditems as items
INNER JOIN dbo.sysmail_event_log AS l
ON items.mailitem_id = l.mailitem_id
WHERE items.last_mod_date > DATEADD(hh, -1,getdate());"
-ServerInstance $instanceName.Name
}
catch
{
Write-Error $_
}
}
$results | Where-Object {$_} | Export-Csv c:\MailFailure.csv -NoTypeInformat
Edit: Code formatting.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!