question

JaneDickson-1754 avatar image
0 Votes"
JaneDickson-1754 asked JaneDickson-1754 commented

Line break in string in Stream Analytics job

Hello.

I have a stream analytics job that generates alarm messages on incoming data and sends them to the Message table where they get picked up by Twilio Text Message service.
Here's my Stream Analytics query:

case when max(lab_temp_alarm) = 1 or max(humidity_alarm) = 1
then concat('ALERT: Temperature or Humidity value over threshold',
'; Time: ', max(timestamp_cst), ' CST', <line break>,
'; Asset Name: ', asset_name, <line break>,
'; Node Name: ', node_name, <line break>,
'; Temperature: ', round(avg([temperature (F)]), 2), 'F', <line break>,
'; Humidity: ', round(avg(humidity), 2), '%')
else 'No alarms'
end as TextBody

I need to put line breaks between "Time", "Asset Name" and all other readings. In SQL I would use CHAR(13) but it's not supported in Stream Analytics.
I tried to put "\n", "\r\n", "x0a" and all other stuff but they're not converted into new line in the text messages that I receive during testing, they just remain what they are.
If there are line breaks in a message body in SQL so is there any alternative to CHAR(13) in Stream Analytics?

Thanks!

azure-stream-analytics
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @JaneDickson-1754 ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

Hi Himanshu,

Thank you so much for a quick response and proposed solution!
I am out of office traveling right now and will be able to try it when I'm back.
I'll update you once I have tried the solution.

Thanks again!

Best,
Jane.

0 Votes 0 ·

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered JaneDickson-1754 commented

Hello @JaneDickson-1754 ,

Thanks for the ask and using Microsoft Q&A platform .
When i see your query , I see that you are concatenating the fields to make a string and that was the Twillio API are going to consume . Since its passed as a string its will take abd char(13) and not as a new line character . The solution which I am sharing is a overkill ( as per me ) but hopefully its gives you an idea and you can improve on the same .

I my case the I have the records in EH and I created a query something like this .

select concat('''device 1:',deviceId ,''' + char(13) +','''device 2:',deviceId,'''') as message
into SQLOP from EHInput

OP : Something like ( this is what is going to go in SQL table .

'device 1:2' + char(13) +'device 2:2'


IN SQL run the below query and you should have the required output .

DECLARE @message VARCHAR(50)
DECLARE @formattedmessage VARCHAR(256)

DECLARE db_cursor CURSOR FOR
SELECT message from ASATest

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @message

WHILE @@FETCH_STATUS = 0
BEGIN
SET @formattedmessage = (@message)
SELECT (@formattedmessage)
FETCH NEXT FROM db_cursor INTO @message
END

CLOSE db_cursor
DEALLOCATE db_cursor


139624-image.png




Please do let me know how it goes .
Thanks
Himanshu


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators



image.png (7.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Himanshu,

I was finally able to review your suggested solution, and I believe it's not exactly what I was asking.

The cursor, as far as I understand, is a built-in function that allows for the iteration of a table (or result set) by row.
I'm not simply trying to display the string with the line break - a string with a line break character in it needs to be passed from Stream Analytics to SQL, where Twilio app picks it up momentarily and sends that TextBody as a text message.

I my case the I have the records in EH and I created a query something like this .

select concat('''device 1:',deviceId ,''' + char(13) +','''device 2:',deviceId,'''') as message
into SQLOP from EHInput

Your example above shows concatenating the string with char(13) as a line break character, but Stream Analytics would not accept char(13) function:

144238-capture1.jpg

Is there a character like that in Stream Analytics at all, or what workarounds are there?

Thank you very much for your help!

Best,
Jane.


0 Votes 0 ·
capture1.jpg (77.7 KiB)