SQL Server Alert - need to know source server name

• doodlingdba

  Hall of Fame

  Points: 3164

  More actions

  June 20, 2018 at 3:17 am

  #363205

  Hi there
  We use Policy Based Management (PBM) in our environment, centralised through a Central Management Server (CMS). This means all the policies are defined on the CMS server, rather than having policies defined on each server. Yesterday I configured a SQL Server Alert on my CMS server which is raised every time one of my PBM policies fails on a set of servers. I have configured the alert to e-mail me when it is raised - this works, however, because the alert is hosted on the CMS server, the e-mail just says it comes from the CMS server, rather than detailing the actual server name where the policy failed. Obviously if I configure the alert locally on the problem server, the e-mail header will include the problem server name. But doesn't do this when the alert is centralised. Any ideas on how I could get the server name into the e-mail? Perhaps just in the subject? I came across a blog which suggested you could add a WMI query to the [Additional notification message to send] box on the Options tab in the Alert properties, like so: $(ESCAPE_NONE(WMI(ServerName))) - I tried this but the e-mail subject then just says server not found. Not sure if this WMI syntax is correct? The alert itself is not a WMI alert, but a SQL Server event alert. Not sure if this is the problem...

  Any ideas would be very welcome!

  Thanks 🙂
  Doodles