Why PowerShell? Since anything can execute the xp_readerrorlog from a remote box then why not much more simply have the
management server periodically execute the xp from its local SQL Agent?
How is a management server going to connect to the different servers? Unless you set up a series of linked servers or use SQL command, you simply cannot "periodically execute the xp". PowerShell would be my choice for this.
I'd say a variant of this PowerShell might best be served installed local to each server being monitored and run under that box's Windows Task Schedule. This variable script can push it's results to the
management server. Then if a given monitored server is experiencing a significant problem at least the local script might be able to catch something and push to the manager.
That may allow for customization per server, but wouldn't a stored proc in your DBA database executed from a SQL job be a bit simpler to maintain? There would be no need to log into a server to set this up.
management server should also have a process to look into the log table to validate all remote instances are checking-in as expected. And add a PowerBI dashboard to trigger an email for any concerns - not use the PW script to send any emails.
Sounds like a duplication of monitoring software? I think I would want to know if a server is not responding on a far more frequent schedule than this.
This is a good article, and it is very useful. Here are some random thoughts...
Your exclude list may get very long. Should this be an Include list? If the list was on each server, you could customize it for each server. There may be servers where you do not care about some messages, and others you may. Like failed logins. I really do not care about failed logins on dev servers, but on prod boxes I do. Something like the "differential backup" I would want to see on the servers where we do not take a diff backup.
The use of PowerShell vs. something else is interesting. I've done this same thing for years with a stored proc and a SQL job. To each his own!
Is there any mechanism to notify different groups per server? DBA's get all servers, BI folks get notified about the BI servers, and so forth? Leveraging the operators on each server may be an improvement.
I attached the proc I have been using forever to do this same thing. It could use some attention, I'm sure.