Search - Merge Fields to Dedup

[gc12345]

Hi 

I wish to dedup and consolidate customer details across two cities.

E.g.  I have 2 records of the same customer across two cities and I want to consolidate them into 1 row

NewCustomerID	City1_CustomerID	City2_CustomerID	City	isActiveCustomer
12345	00001		A	Y
12345		00002	B	N

Result: 

* Merge City1_CustomerID and City2_CustomerID into one row

* City Field populates the City where the customer is active

* Field for ExistsInOtherCities is "Y" when there are 2 or more records.

NewCustomerID	City1_CustomerID	City2_CustomerID	City	isActiveCustomer	ExistsInOtherCities
12345	00001	00002	A	Y	Y

I am used to SQL where I can make temp tables in each in then join it back but unsure how to do it in SPLUNK

[ITWhisperer]

What do your events look like? For example, do events from different cities use different field names, and what does it look like if there are more than 2 cities? Can only one city be active for the customer? Is the new customer id a different field to the city customer id?

[gc12345]

- They use the same field names (The Data Admin provided me a lookup to use rather than streaming events)

- It cannot be more than 2 cities 

- Both cities can be active but choose the first record

- The new Customer ID can sometimes be the same as the city customer id 

[ITWhisperer · Accepted solution]

| eval activeCity=if(isActiveCustomer=="Y",City,null)
| stats list(City_CustomerID) as City_CustomerID first(activeCity) as activeCity dc(City) as City_count by NewCustomerID
| eval City1_CustomerID=mvindex(City_CustomerID,0)
| eval City2_CustomerID=mvindex(City_CustomerID,1)
| eval City=activeCity
| eval isActiveCustomer=if(isnotnull(City),"Y","N")
| eval ExistsInOtherCities=if(City_count>1,"Y","N")