Trigger powershell based on event log

问题:

I have a command line argument script written in PowerShell which accepts server name from the task scheduler. However my requirement is to execute the script when the SQL server is restarted, hence I have attached the PowerShell script to the event 17069. But I am unable to dynamically pass the event source which would be the server name in this case.

Below is argument section of the task scheduler job

-Command "& 'D:\SQLJobs\PS\readErrorLogFile.ps1' '$(Source)'"

$(Source) does not pick up the event source from event view while firing the powerShell. Can you please let me know how to pick up the event source.

As suggested in the comments I have updated my task scheduler job with below XML branch

<ValueQueries>
<Value name="server">Source</Value>
</ValueQueries>

The Above script has been inserted in <EventTrigger> Branch.

Below is PowerShell CMD line input script.

 param (
    [string]$server
 )

Kindly let me know what needs to be mentioned in the argument section of the task scheduler job as $(server) displays null in the script.

回答1:

I think what you're looking for is described here:

• https://blogs.technet.microsoft.com/wincat/2011/08/25/trigger-a-powershell-script-from-a-windows-event/

After you've created your task from the event, you need to right click it in Task Scheduler and do Export.. to save it as XML. Then modify the XML to include value queries for the values you want sent to your script (within the <EventTrigger> section).

For example to get the event source you add:

<ValueQueries>
  <Value name="eventSource">Event/System/Provider/@Name</Value>
</ValueQueries>

Now delete the task and recreate it by importing your modified XML file.

You can now configure the Action of the task to run PowerShell.exe and your script and then reference whatever names you used for your valuequeries above as parameter inputs to your script. For example:

powershell.exe .\TriggerScript.ps1 -eventSource $(eventSource)

Of course then your script just needs to support these inputs via a param() block at the top, e.g:

param($eventSource)

回答2:

An alternative solution might be to create a job in the SQL Server Agent and set the schedule to be run at server start up. That way you don't need to read from the event logs at all.