Logic app, convert run query json results to usuable / readable output (html/excelish?)to use in Sentinel comments for enrichment

Question

i have created a logic app to use in Sentinel for enrichment and automation. In this case its an automation app for identity based incidents, namely 'unfamiliar signins' The app is actually working perfectly, however. in the app at one point i run a kql query with the 'run query and list results' action. The query works, i can see the output in json. Now, with i use the raw output value, and use that in my Sentinel commment i do see the results, only its not in the best format as you can imagine.

The query looks for sign in events based on the user-agent seen in the incident, filters on use-agent and looks for events where the user agent has been seen in the past, does a summary based on application,useragent, and shows the results, this is perfect for incident inrichment. The results can vary, sometimes there are no results, because the user agent has not been seen in the past days/weeks, other times there are 4 rows with results, so its pretty dynamic.

I am looking for a way to make the output results of the query, which is in JSON, more usable, more readable, maybe in a html format and use that in the incident, or something else more readable.

the query i am using:

SigninLogs
| where UserPrincipalName == "<the upn variable>" // Replace with the specific user's UPN
| where UserAgent == "<the user-agent variable>" // Replace with the specific User-Agent string you're interested in
| where TimeGenerated between(ago(31d)..ago(1d)) // Filters to records from the last 30 days, excluding today
| summarize Count = count() by UserAgent, UserPrincipalName, AppDisplayName
| where Count > 0
| order by Count desc // Optional: Orders the results by count in descending order
| project Count, AppDisplayName, UserAgent, UserPrincipalName

I have found several posts online where this is being explained more or less, but or i am having trouble understanding or the instructions are incorrect ;)

Is there someone that can give me some good advise on this ?

regards! your friend

Tried to play with array variables and such, but having trouble reflecting the internet instructions to my situation.

Answer 1

only its not in the best format as you can imagine.I am looking for a way to make the output results of the query, which is in JSON, more usable, more readable, maybe in a html format and use that in the incident, or something else more readable.

Yes, In Logic apps there are many ways to make it into a readable format and below is my design:

Output:

Showing Readable data using CSV Table Connector:

Showing Readable data using HTML table Connector:

Not only this there are many connectors in Logic apps to design tables .