show on ipadress value in a column

%3CLINGO-SUB%20id%3D%22lingo-sub-3063562%22%20slang%3D%22en-US%22%3Eshow%20on%20ipadress%20value%20in%20a%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3063562%22%20slang%3D%22en-US%22%3E%3CDIV%3E%3CDIV%3E%3CSPAN%3EDeviceInfo%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3Bwhere%26nbsp%3BTimestamp%26nbsp%3B%26gt%3B%26nbsp%3Bago(30d)%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3Bsummarize%26nbsp%3Barg_max(Timestamp%2C%26nbsp%3B*)%26nbsp%3Bby%26nbsp%3BDeviceId%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3Bwhere%26nbsp%3BOnboardingStatus%26nbsp%3B%3D%3D%26nbsp%3B'Can%26nbsp%3Bbe%26nbsp%3Bonboarded'%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3Bjoin%26nbsp%3Bkind%3Dinner%26nbsp%3B(DeviceNetworkInfo%26nbsp%3B%7C%26nbsp%3Bdistinct%26nbsp%3BDeviceId%2C%26nbsp%3BMacAddress%2C%26nbsp%3BIPAddresses)%26nbsp%3Bon%26nbsp%3BDeviceId%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%7C%26nbsp%3Bdistinct%26nbsp%3BTimestamp%2C%26nbsp%3BDeviceName%2C%26nbsp%3BDeviceId%2C%26nbsp%3BOSPlatform%2C%26nbsp%3BOSDistribution%2C%26nbsp%3BOSVersion%2C%26nbsp%3BReportId%2C%26nbsp%3BMacAddress%2C%26nbsp%3BIPAddresses%3C%2FSPAN%3E%3C%2FDIV%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CSPAN%3Ethe%20result%20of%20IPAddresses%20column%20is%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3CTABLE%20width%3D%22481%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22481%22%3E%5B%7B%22IPAddress%22%3A%2210.14.14.16%22%2C%22SubnetPrefix%22%3Anull%2C%22AddressType%22%3A%22Private%22%7D%5D%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CBR%20%2F%3Ei%20like%20to%20have%20only%20the%20value%20only%2C%20in%20this%20example%3A%2010.14.14.16%3CBR%20%2F%3EI%20have%20tryied%20with%26nbsp%3B%7C%20extend%20IP%20%3D%20tostring(todynamic(IPAddresses).IPAddress)%2C%20but%20somehow%20it%20does%20not%20show%20me%20the%20result.%20How%20can%20I%20do%20it%20correct%20%2C%20(i%20have%20not%20mush%20expirience%20in%20this%20part)%3CBR%20%2F%3E%3CBR%20%2F%3Ekind%20regards%3C%2FDIV%3E%3CDIV%3ESteven%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%3E%3CSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
DeviceInfo
| where Timestamp > ago(30d)
| summarize arg_max(Timestamp, *) by DeviceId
| where OnboardingStatus == 'Can be onboarded'
| join kind=inner (DeviceNetworkInfo | distinct DeviceId, MacAddress, IPAddresses) on DeviceId
| distinct Timestamp, DeviceName, DeviceId, OSPlatform, OSDistribution, OSVersion, ReportId, MacAddress, IPAddresses
 
the result of IPAddresses column is

[{"IPAddress":"10.14.14.16","SubnetPrefix":null,"AddressType":"Private"}]

i like to have only the value only, in this example: 10.14.14.16
I have tryied with | extend IP = tostring(todynamic(IPAddresses).IPAddress), but somehow it does not show me the result. How can I do it correct , (i have not mush expirience in this part)

kind regards
Steven


1 Reply

@quinzy 

DeviceInfo
| where Timestamp > ago(30d)
| summarize arg_max(Timestamp, *) by DeviceId
| where OnboardingStatus == 'Can be onboarded'
| join kind=inner (DeviceNetworkInfo | distinct DeviceId, MacAddress, IPAddresses) on DeviceId
| mvexpand parse_json(IPAddresses)
| extend IP=IPAddresses.IPAddress
| project DeviceName, OnboardingStatus, OSPlatform,OSDistribution,OSVersion, MacAddress, IP
| sort by DeviceName asc

ok, I have found a way , but is there a way to remove the double DeviceName's ? Distinc does not work on IP