Kusto query output formatting into email

[sandeepgeorge]

I am trying to hook up a flow which runs a kusto query and then sends an email to folks.

The kusto query has a 2 columns
1. manager alias

2. Data specific to manager

I managed to hook up the query + Compose step to extract 'Body' of the kusto query and compose and email with the 'Outputs'. The email I receive is a json output. for example
{"value":[{"L4Manager":"HENRYSA","Source":"Other","count_":1180},{"L4Manager":"ALBERT","Source":"Other","count_":14927}]

How do I parse this 'Output' so that 

1. I can send email to the L4Manager alias

2. Is there a way for me to compose the json into a table format similar to how we view results in Kusto explorer?

Thanks,

Sandeep

[abm]

Hi @sandeepgeorge 

Yes you can use the JSON Parse step to extract the values.

Thanks

[abm]

Hi @sandeepgeorge 

Here it is

The schema is as follows:

{

"type": "array",

"items": {

"type": "object",

"properties": {

"L4Manager": {

"type": "string"

},

"Source": {

"type": "string"

},

"count_": {

"type": "integer"

}

},

"required": [

"L4Manager",

"Source",

"count_"

]

}

}

Thanks

[ChristianAbata · Accepted solution]

hi @sandeepgeorge  here is what I do to create a table from your json response.

No value

replace(string(outputs('Json')),'"value":','')

No fisrt

substring(outputs('NoValue'),2,sub(length(outputs('NoValue')),2))

NoSecond

substring(outputs('NoFirst'),0,sub(length(outputs('NoFirst')),2))

Array

replace(outputs('NoLast'),'","','"},{"')

Hope this can help