Very new to KQL - so apologies is this if "obvious"!
I am creating alerts from Log analytics data to create emails on changes to Network Security Groups.
So far so good, I get a nicely formatted table in the alert email - except for the destination port from the LA entry.
I can get the value out of the Json with
extend destPorts = parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).destinationPortRanges
which generates
destPorts ["80","443","7547","7647"]
how do I now get that into the output as a simply text string - looking exactly like above
["80","443","7547","7647"]
The full query is this - illegal at the moment as it says
"Path expression destPorts source must be scalar of type 'dynamic'. Received a source of type string instead"
AzureActivity
| where _ResourceId has "-subnet-nsg" and Properties has "requestbody"
| extend Time = format_datetime(TimeGenerated, 'yyyy-MM-dd HH:mm:ss')
| extend Admin = tostring(parse_json(Properties).caller)
| extend resourceGroup_ = tostring(parse_json(Properties).resourceGroup)
| extend hierarchy_ = split(tostring(parse_json(Properties).hierarchy), "/")
| extend sourcePort = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).sourcePortRange)
| extend sourceAdd = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).sourceAddressPrefix)
| extend proto = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).protocol)
| extend priority_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).priority)
| extend dir = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).direction)
| extend destPort = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).destinationPortRange)
| extend destinationApplicationSecurityGroups_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).destinationApplicationSecurityGroups)))
| extend destAdd = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).destinationAddressPrefix)
| extend description_ = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).description)
| extend Outcome = tostring(parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).access)
| mv-expand subid=hierarchy_[0], lz=hierarchy_[1], sub=hierarchy_[2]
//| project-away hierarchy_
| extend nsgN2 = split(tostring(parse_json(Properties).entity),"/")
| extend nsgName = strcat(nsgN2[8],"/",nsgN2[10],"/",nsgN2[10])
| extend destPorts = parse_json(tostring(parse_json(tostring(parse_json(Properties).requestbody)).properties)).destinationPortRanges
| project Time, Admin, lz, sub, resourceGroup_, nsgName, sourceAdd, sourcePort, destAdd, destPort. destPorts, dir, priority_, description_, proto, Outcome
output today looks like: (without destPorts)
Insights
Top 10 result(s)
Time 2021-05-19 09:31:56
Admin xxx.xxx@xxx.com
lz xxxx
sub Landing-Zones
resourceGroup_ xxxx-EUTS-PRD-RG-01
nsgName xxx-euts-prd-connectivity-subnet-nsg/Allow-Outbound-xxxx/Allow-Outbound-xxx
sourceAdd xx.xxx.x.xx/28
sourcePort *
destAdd yy.yyy.yy.yyy
dir Outbound
priority_ 3000
proto TCP
Outcome Allow
this is only an issue when the dest port entry is multi-valued.
