How to get a portion of the string in NRQL

Hello Team,

I want to get a portion of the string in NRQL.

I have attribute in my logs like below. the attribute name is message.
[ERW0001] some error messages…

The ERW0001 is a error code.
I want group by error code to count errors.

My NRSQL like below.
select count(*) form Log Since 1 hour ago where level = ‘ERROR’ FACET SUBSTR(message,1,7) extrapolate

but the SUBSTR() function not support NRQL.
In the NRQL ,how to get a portion of the string?

thanks.

You might try ‘WITH METRIC_FORMAT’

1 Like

Thank you!
I have used the ‘WITH METRIC_FORMAT’ like below. but I did not get any result .
I think have any mistakes in this NRQL. Can you give me any advice or sample , Thanks .

FROM Log SELECT errorCode since 1 day ago where level = 'ERROR' and Role = 'all' WITH METRIC_FORMAT '[{errorCode}] {errorMessage}'

Typically you will want to count the thing and facet the things. So count your errorCode and facet the two formatters you added. I don’t know if it will work for this, but you can try it.

Thank you very much!
I will try it.

Hi @haojie-min Once you have had a chance to try it please let the community know the results. :slight_smile:

I’d maybe change your formatter names so that they don’t conflict with attribute names.