Add location to IPs found in lookup or add unknown if missing

[khaghsam]

So I have a string of IPs that are input and trying to figure out how to add the location on them which are stated in a csv.

the input string varies and could looks like for example like this for each host:

ip=1.1.1.1
ip=1.1.1.2|1.2.3.4
ip=1.1.1.5|1.4.4.6|1.2.4.6

meaning each could either have one ip or more, some of these ips are in the location csv some not

so my table from the begining have these values and other empty fields that will be filled later,
*hostname, ip, location, owner *

The ones with information atm are hostname, ip, trying to add location with below, then add the other info after this code as its dependad on it:

**
|inputlookup hostnames.csv
|table hostname ip
| eval ip = split(ip,"|")
| eval numIPs = mvcount(ip)
| eval iVal = mvrange(0,numIPs,1)
.
.
.
...missing...code...
.
.
.
| lookup location_info ip_prefix AS ip OUTPUT location
|table hostname ip location owner
| eval location = if(location="NONE" OR location="Unknown", "Unkown", location)
| streamstats count
| mvexpand location
| dedup count location
| mvcombine location
| fields - count
| lookup owners.csv location OUTPUT owner
| table hostname ip location owner**

when there is 1 ip in the string, this works, but if there is more I have no clue at all how to solve it. I've tried mvexpand, mvcombine, foreach, with no luck or I'm using them wrongly.

can someone share some insight in this?

[richgalloway]

This should work.

| inputlookup hostnames.csv
| fields hostname ip
| eval ip = split(ip,"|")
| mvexpand ip
...

[khaghsam]

| inputlookup hostnames.csv
| fields hostname ip
| eval ip = split(ip,"|")
| mvexpand ip
| lookup location_info ip_prefix AS ip OUTPUT location

Works but the combine part becomes an issue then.
So the output of above becomes:

hostname1 1.1.1.1 location1

hostname1 2.2.2.2 unknown
hostname1 3.3.3.3 location2

depending on how many local/virtual ips the host has.

[richgalloway]

What is the desired output?

[khaghsam]

Something like this:

https://drive.google.com/open?id=1D5zo70owEnr5teSrWnkfSEU1uZBv6hdf

Now the location field's duplicates need to be removed in case there are, same with owner. (so I guess only unique values)