I've managed to create 2 queries that retrieve the data I need

--Query 1: Return machines with 1 IPAddress

select MachineId, MAX(IPAddress) as IPAddress from @IPInfo

group by MachineID

having COUNT(MachineID) = 1

--Query 2: Return machines that have #IPAddresses, and only show valid ip address

select MachineId, ipaddress from @IPInfo

where MachineId in (SELECT MachineId FROM @IPInfo

GROUP BY MachineId

having COUNT(MachineID) > 1) and IPAddress not like '192.168.%' and IPAddress not like '172.20.%' and IPAddress not like '10.%'

(I still need to fine tune the IPAddress filter a bit more, but for the sample data it's working)

when I try to combine this into 1 big query, it does not return any rows !!!

select machineid, ipaddress from @IPInfo

inner join (select q1t.MachineId, MAX(q1t.IPAddress) as IPAddress from @IPInfo as q1t

group by q1t.MachineID

having COUNT(q1t.MachineID) = 1) As Q1 on Q1.MachineId = machineID

inner join (select MachineId, ipaddress from @IPInfo

where MachineId in (SELECT MachineId FROM @IPInfo

GROUP BY MachineId

having COUNT(MachineID) > 1) and IPAddress not like '192.168.%' and IPAddress not like '172.20.%' and IPAddress not like '10.%') As Q2 on Q2.MachineId = machineID and Q2.IPAddress = IPAddress

Other option I'm looking at is creating 2 Temp tables and then use Union query, need to figure that out still