Combine multiple rows in one with a common key

[lyds]

Hello,

I have a log that records data bit by bit. I want to combine them to have only one row of data.

Exemple:

I've tried mvcombine but when there are multiple values for a field it doesn't work as I would like. I'vealso tried stats values but I struggle to expand both pet and male/female fields as they don't have the same number of different values.

Do you know how to do it?

Thanks for the help

[niketn]

@lyds, try the following run anywhere search based on sample data and desired output as per your question. Seems like First Name, Last Name and City will be unique per Key and there will be multiple values for Pet and respective pet's Gender.

The command from | makeresults till | table Key FirstName LastName Pet Gender City generates cooked up data as per your sample events. You can pipe in your existing search in its place.

The remaining query brings the Pet and Gender fields together and then uses stats to correlate event fields based on Key. Finally the Pet and Gender fields as split as per desired results using mvexpand, makemv (split() eval function may also be used) and mvindex() function.

| makeresults 
| eval data="Key=1,FirstName=Frank;Key=1,LastName=Jones;Key=1,Pet=Dog,Gender=Male;Key=1,Pet=Cat,Gender=Male;Key=1,Pet=Rabbit,Gender=Female;Key=1,City=Paris;Key=2,FirstName=Jane;Key=2,LastName=Lock;Key=2,Pet=Mouse,Gender=Female;Key=2,Pet=Cat,Gender=Male;Key=2,Pet=Rat,Gender=Female;Key=2,City=Paris"
| makemv data delim=";"
| mvexpand data
| rename data as _raw
| KV
| table Key FirstName LastName Pet Gender City
| eval PetDetails=Pet.",".Gender
| stats values(FirstName) as FirstName values(LastName) as LastName values(City) as City values(PetDetails) as PetDetails by Key
| mvexpand PetDetails
| makemv PetDetails delim=","
| eval Gender=mvindex(PetDetails,1), Pet=mvindex(PetDetails,0)
| table Key FirstName LastName Pet Gender City

Please try out and confirm!

[lyds]

I've tried it, it displays data the way I want, thank you a lot!

However, now I want to retrive the last Pet (the whole row) for each Key but I can't make it work.
I've tried | stats latest(Pet) by Key but I get no values. I've got this command working on other dashboards using a simple base search but not for this one. I've tried to concatenate a timestamp with Pet and Gender but it is still not working. | eval PetDetails=Pet.",".Gender.",".timestamp

[niketn]

@lyds as per your Sample data and initial requirement there could have been multiple pets (and hence multiple gender for pets) per key. So I had created PetDetails to bring the two fields together. Using values(PetDetails), unique Pet/Gender details are grouped together in Single field which is multi -valued.

mvexpand is used to example the multi-valued pet details fields and retain the remaining unique values (like Key, First Name, Last Name etc) in all expanded pet details rows.

Makemv afterwards is to split the Pet Details into Pet name and gender fields respectively.

If you want only the First/Last pet use the following approach
PS: list() statistical function will get all the values of pets for each Key in exact same order they appeared in the events (gives first 100 result only but show duplicates and retains the order on values as per the order of events)

 | makeresults 
 | eval data="Key=1,FirstName=Frank;Key=1,LastName=Jones;Key=1,Pet=Dog,Gender=Male;Key=1,Pet=Cat,Gender=Male;Key=1,Pet=Rabbit,Gender=Female;Key=1,City=Paris;Key=2,FirstName=Jane;Key=2,LastName=Lock;Key=2,Pet=Mouse,Gender=Female;Key=2,Pet=Cat,Gender=Male;Key=2,Pet=Rat,Gender=Female;Key=2,City=Paris"
 | makemv data delim=";"
 | mvexpand data
 | rename data as _raw
 | KV
 | table Key FirstName LastName Pet Gender City
 | eval PetDetails=Pet.",".Gender
 | stats values(FirstName) as FirstName values(LastName) as LastName values(City) as City list(PetDetails) as PetDetails by Key
 | eval FirstPetDetails=mvindex(PetDetails,0)
 | eval LastPetDetails=mvindex(PetDetails,mvcount(PetDetails)-1)
 | eval FirstPetDetails =split(FirstPetDetails,","),LastPetDetails=split(LastPetDetails,",")
 | eval FirstPetGender=mvindex(FirstPetDetails,1), FirstPet=mvindex(FirstPetDetails,0), LastPetGender=mvindex(LastPetDetails,1), LastPet=mvindex(LastPetDetails,0)
 | fields - *Details