2

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.

Screengrabfrom event viewer

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.

SQLDoctor
  • 343
  • 7
  • 16

2 Answers2

4

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

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)
Mark Wragg
  • 22,105
  • 7
  • 39
  • 68
  • Thanks for the update, Mark. I have updated the question with the current issue that I am facing. Please have a look at it. – SQLDoctor Dec 12 '18 at 15:40
  • You need to make your value queries entry `Event/System/Provider/@Name` (beware you need to delete/recreate the task after changing this) then modify your command to be `-Command "& 'D:\SQLJobs\PS\readErrorLogFile.ps1' -server $(eventSource)"` – Mark Wragg Dec 12 '18 at 15:42
  • Sure, Let me check this and get back to you. – SQLDoctor Dec 13 '18 at 01:53
1

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.

Leyshon
  • 111
  • 1
  • 2