The way I hopefully figured this out (am going to try it out in a couple of days), was first doing it manually, getting an alert out to a teams channel (configured as a destination) , using the default template. When it actually wrote on teams, I got a link to a job (which apparently isn’t visible on the workflows page). Looking at the yaml of the job, it seems whenever a schedule is added to an alert, it creates a workflow with a “sql_task”.
Now, we have already been writing to teams channel, for failed jobs using email notifications and using the mail id of the channel (yes, there is an email link for a teams channel).
So, I would approach this as:
1. Create a saved query (through api, you can use terraform too).
2. Create an alert for the query. This only creates an alert with no schedule.
3. Create a workflow/job through api with a sql task pointing to an alertid and using email_notifications for the job (email notification can be used for both teams and to normal mailing groups).
I hope this should work, but I have to try it out and confirm in a few days.
This should however have to be simpler.