Using WMI Functions in an Alert

[JohnBates]

hi everyone,

I've created an Alert (in SQL server 2005) to notify me when a server-level change has been performed - for example anytime a database is created, dropped or altered, the alerts sends me an email. That works ok.

The alert Type is a WMI Event Alert and it executes this: select * from DDL_SERVER_LEVEL_EVENTS within the \\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER namespace.


But I want to refine it to include the SQL stmt within the email so I could know which user performed it, which database etc. In the Additional Notification Message to Send, I want to include the text from the SQL stmt or some more info as to who did what. I've tried placing N$(ESCAPE_SQUOTE(WMI(TextData)))) in the Options box - the Alert still works but it just inserts N$(ESCAPE_SQUOTE(WMI(TextData)))) into the body of the email.

I guess my question is: Is there a WMI function that can run in the Additional Notification Message box to include more specific info in my email?

- OR - can I add a WHERE clause to select * from DDL_SERVER_LEVEL_EVENTS ?

Thanks for any ideas.
John