San Francisco Bay Area Endpoint Management User Group

I was having a bugger of a time getting my locations to display correctly using the inv_aex_ac_tcpip table as a machine can have several IP address. I really needed to get one good IP address that was the latest one.

I use this address to match up to my location schema with a CASE statement. After a bunch of working with the MAX statement I came up with the following that is about 95% accurate

select distinct  case   
when ip.[IP Address] like '100.100.%' then 'San Jose'  when ip.[IP Address] like '111.100.%' then ' SJC Office'
when ip.[IP Address] is null then 'No IP'
else ip.[IP Address]
end as [Location],  
ac.name, ac.[client date]


FROM dbo.Inv_AeX_AC_Identification ac
 LEFT  JOIN (select  distinct iptask1._resourceguid, iptask1.[ip Address]
                   from Inv_Device_Discovery_Task iptask1
                   join (select  _resourceguid,max([Date Last Seen])as 'date'  from Inv_Device_Discovery_Task
                   where [IP Address]!='' group by  _resourceguid)task
                   on task._resourceguid = iptask1._resourceguid and task.date = iptask1.[date Last seen]
                   where   iptask1.[ip address]!='')ip                                                                            
ON ac.[_ResourceGuid] = ip.[_ResourceGuid]