Azure Virtual Machines Asset Import

[Jim]

Hi, I do have another thread from a while ago but I can't seem to find it

 

Using Azure asset import, when importing virtual machines I am unable to get the ip addresses of the servers to import into Hornbill automatically, using the resource explorer I can run a kusto query that will return the ip addresses, but I am unable to get it to work in the config file, I think it doesn't like the joins and projects to enable the joins, Has anyone else been able to get virtual machines ip addresses to import to Hornbill? 

[Jim]

This is the query in its simplest form

 

Resources    
    | where type =~ 'microsoft.compute/virtualmachines'
    | project vmId = tolower(tostring(id)), vmName = name, properties
    | join kind=leftouter   (Resources
        | where type =~ 'microsoft.network/networkinterfaces'
        | mv-expand ipconfig=properties.ipConfigurations
        | project vmId = tolower(tostring(properties.virtualMachine.id)), privateIp = ipconfig.properties.privateIPAddress, publicIpId = tostring(ipconfig.properties.publicIPAddress.id)
        | join kind=leftouter (Resources
            | where type =~ 'microsoft.network/publicipaddresses'
            | project publicIpId = id, publicIp = properties.ipAddress
        ) on publicIpId
        | project-away publicIpId, publicIpId1
        | summarize privateIps = make_list(privateIp), publicIps = make_list(publicIp) by vmId
    ) on vmId
    | project-away vmId1
    | sort by vmName asc

 

[Jim]

hmm, I've just noticed there were a few updates I've missed, looks like this should now work with the provided query in the example, going to test