Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community
SOLVED

Unpacking JSON

Copper Contributor

Hay , I am trying to unpack the JSON string  IPAddresses  from the table 

DeviceNetworkInfo: 
 DeviceNetworkInfo | take 10
 | project IP = parse_json(IPAddresses)
 | extend ADR = IP.IPAddress
 
I get an empty ADR column  .....
I tried to use this video to resolve the issue:
8 Replies
best response confirmed by eladfe (Copper Contributor)
Solution
Here IP is still an array of IPAddress,SubnetPrefix,AddressType values so a simple reference to property IPAddress is not unique. You have to index to which element of IP you want, such as IP[0].IPAddress, IP[1].IPAddress, etc.
Great! Thank you !
I have another question: Can I still get Other columns of DeviceNetworkInfo after unpacking the Json ?
For example : Timestamp, Devicename, ADR (ADR isI P[0].IPAddress)
Yes, all you are doing with parsing the JSON is adding columns, the original columns are all still ther
This is my Query:
DeviceNetworkInfo| take 10
| project IP = parse_json(IPAddresses)
| extend ADR = IP[0].IPAddress, SUB = IP[0].SubnetPrefix

the result include only 3 columns : IP, ADR, SUB
Add the other columns you want to the project statement, comma separated
The only option is to project one of the three columns that I mentioned. Nothing else.
Sorry I don't understand waht you mean by "the only option is to project one of the three columns", could you explain in more detail?
I change the line : | project IP = parse_json(IPAddresses)
to : | extend IP = parse_json(IPAddresses)
and now I have all the columns .
Thank you for your help !
1 best response

Accepted Solutions
best response confirmed by eladfe (Copper Contributor)
Solution
Here IP is still an array of IPAddress,SubnetPrefix,AddressType values so a simple reference to property IPAddress is not unique. You have to index to which element of IP you want, such as IP[0].IPAddress, IP[1].IPAddress, etc.

View solution in original post