Need help in to extract values to new row in Kusto

user_10964894 0
Viewed 54

I kinda need help extracting a value from a string and dynamically add new row

Below is the string that I have in the column DBInfo.

[{"DBName":"master","TriggerName":"ramp_sqlpreventivetrigger_sqlserver_v01","TriggerStateDB":"Enabled"},{"DBName":"master","TriggerName":"No Trigger","TriggerStateDB":"No Trigger"}]

As you can see, we have two sets of data in above string. master database is coming twice so i need rows instead of one row.

Below is what I have so far. I am using substring and trim for now to remove "]" and "[" from the string so that I can use parse_json function. I am not having any luck so far.

SQLTriggerViolations 
| where TriggerStatus != "Enabled"
| where ServerName == 'ServerName'
| project ServerName, DBInfo
| extend DBInfoModified = substring(DBInfo, 1, strlen(DBInfo))
| extend DBInfoFinal = trim("]", DBInfoModified)
| extend DBName = parse_json(DBInfoFinal).TriggerName
kql azure-data-explorer
1 Answers

you can use mv-expand: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/mvexpandoperator

for example:

datatable(DBInfo:string)
[
    '[{"DBName":"master","TriggerName":"ramp_sqlpreventivetrigger_sqlserver_v01","TriggerStateDB":"Enabled"},{"DBName":"master","TriggerName":"No Trigger","TriggerStateDB":"No Trigger"}]'
]
| mv-expand parse_json(DBInfo)
DBInfo
{
"DBName": "master",
"TriggerName": "ramp_sqlpreventivetrigger_sqlserver_v01",
"TriggerStateDB": "Enabled"
}
{
"DBName": "master",
"TriggerName": "No Trigger",
"TriggerStateDB": "No Trigger"
}
user_78618070
Related