How do replace carriage returns (newlines) in one of my variables in an alert action to make it JSON compliant?

I'm sending JSON in one of my alert actions to a REST API.

I've formatted the JSON and I'm also using variables (like ${N=Alerting;M=Notes} ).

Unfortunately, the Alert Notes variable value has some unsafe JSON characters in it (namely, carriage returns).  My REST API is giving me these errors from time to time:

There was an error when trying to parse the webhook body to JSON (could be due to un-escaped control characters like newlines):
Illegal unquoted character ((CTRL-CHAR, code 9)): has to be escaped using backslash to be included in string value at [Source: (ByteArrayInputStream); line: 3, column: 422] (through reference chain: com.codebarrel.automation.api.webhook.IncomingWebhook["Description"])
I'm stumped on how I can replace the newline character embedded in the ${N=Alerting;M=Notes} value?  Is there a built in replace function?
Thank you~!
  • I think I might have solved it:

    ${SQL: SELECT REPLACE(REPLACE(REPLACE(Notes, CHAR(13), '\r'), CHAR(10), '\r'),CHAR(9),'\t') FROM [AlertStatusView] asv INNER JOIN [AlertActive] aa ON asv.AlertObjectID = aa.AlertObjectID WHERE aa.AlertActiveID = ${N=Alerting;M=AlertActiveID}}

    Here's my full JSON I'm passing to Jira Automation:

    {
    	"Summary": "${NodeName} - ${N=Alerting;M=AlertName} (Status= ${Status})",
    	"Description": "|*When*|${N=Alerting;M=AlertTriggerTime;F=DateTime} |\r|*Node Status*|${Status} |\r|*Node URL*|${NodeDetailsURL} |\r|*Object Details*|${N=SwisEntity;M=DetailsUrl} |\r|*Alert Details*|${N=Alerting;M=AlertDetailsUrl} |\r|*Server FQDN*|${N=SwisEntity;M=DNS} |\r|*Alert Notes*|${SQL: SELECT REPLACE(REPLACE(REPLACE(Notes, CHAR(13), '\r'), CHAR(10), '\r'),CHAR(9),'\t') FROM [AlertStatusView] asv INNER JOIN [AlertActive] aa ON asv.AlertObjectID = aa.AlertObjectID WHERE aa.AlertActiveID = ${N=Alerting;M=AlertActiveID}} |",
    	"Support Team": "TEAM NAME GOES HERE",
    	"Priority": "Normal P3",
    	"Alert ID": "${N=Alerting;M=AlertActiveID}"
    }

    I don't know SWQL well enough to confirm that I'm supposed to join AlertObjectID to AlertActive, but, for now it seems to be working.

  • Depending on your version of SQL Server, you can format directly as JSON.