Some thoughts of your typical data shepherd / data plumber / data dance teacher sort of person.
Method 1 – TSQL
SELECT ConnectionProperty('net_transport') AS 'net_transport'
, ConnectionProperty('protocol_type') AS 'protocol_type'
, ConnectionProperty('auth_scheme') AS 'auth_scheme'
, ConnectionProperty('local_net_address') AS 'local_net_address'
, ConnectionProperty('local_tcp_port') AS 'local_tcp_port'
, ConnectionProperty('client_net_address') AS 'client_net_address'
, ConnectionProperty('physical_net_transport') AS 'physical_net_transport'
Local_net_address was the field that I required.
This post was inspired by - https://www.sqlservercentral.com/forums/reply/1519373
Further information on ConnectionProperty can be found here.
Method 2 – DBA Tools
Test-DbaConnection <InstanceName> see -> https://docs.dbatools.io/#Test-DbaConnection
The output includes a property IPAddress which is what I wanted. If you have not looked at DBATools then you should, it is an amazing, open source project, and a genuine time saver.
Are there any other methods which I have not thought of please let me know.