Escaping backslash in SQL_INSTANCE name

General
#1

Hi, I am just getting started with SQLWATCH and experimenting with Teams notifications. I’ve ran in to an issue with named instances, where by the {SQL_INSTANCE} contains a backslash and this is not a valid JSON character, so it bombs out with “bad payload”. I can fix this by manually entering in a double backslash when testing, but I’m stuck on how to incorporate this automatically.

Do you know how I would escape this please? I’m not sure if it would be in the sqlwatch_config_check_action_template] table or the [sqlwatch_config_action]

Many thanks.

#2

Sounds like a bug.
The variable substitution is in usp_sqlwatch_internal_process_actions Line 215: https://github.com/marcingminski/sqlwatch/blob/main/SqlWatch.Monitor/Project.SqlWatch.Database/dbo/Procedures/usp_sqlwatch_internal_process_actions.sql

I think that’s the only place where you’d have to change it.

#3

Thanks Marcin, such a quick response. I’ll take a look at that procedure and see if I can workaround it.

One other question if you don’t mind.
Is it possible to have different actions for 1 check but based on whether it’s a warning or critical?

Say for example I wanted to send warnings to MS teams but for critical I want to send an email to trigger out of hours?

Thank you.

#4

Good question! I can’t say from the top of my head, I think not but maybe there’s a way (I haven’t looked into actions for long time). One solution perhaps would be to creat two checks, one with critical only and one with warning and have different actions. Checks must have a critical threshold so the one for warning only would have a critical threshold set high enough so it never triggers an action. Bit of a back but it may suit you.

#5

Yeah thats how I thought it may need to be implemented, but I’ll see if it’s really a requirement.

Many thanks, appreciate the assistance.

1 Like
#6

Hi Marcin, updated that procedure to replace the backslash with double backslash and it worked perfectly for the teams notification. Thanks for pointing me in the right direction.

I was wondering, what’s the best way to ask more generic questions please? I appreciate these Topics want to “stay on topic”, and not be littered with other questions, however I have a few things I can’t find the answers for, and it would be great if I could ask you please.
Thanks.

1 Like
#7

Glad it worked.

Just create a new topic and ask away. I’ll pick it up :slight_smile:

#8

I think this backslash is also causing an issue in the Log Analytics workspace.
I’ve taken the json template and that saved ok but when viewing data, after I’ve picked the instance and Check Status it throws the errors:
“Request is invalid and cannot be processed: Syntax error: SYN0001: I could not parse that, sorry. [line:position=0:0]…”

I can only assume its the backslash in the {SqlInstance}, maybe something with this line: where sql_instance_s == "{SqlInstance}\

Not sure how easy it is to troubleshoot this one!!

UPDATE: Confirmed it is the backslash in the SqlInstance that is the problem. Just hooked up a default instance and the data is displayed on the workbook.

Any ideas how to tweak it to support named instances please?

#9

I am going to take a guess here. Log Analytics is JSON driven and backslash in JSON is escaped by a backslash so try two backslashes